I'm trying to work out failover and disaster recovery procedures for a cluster of three servers. Streaming replication is being used with a high wal_keep_segments, no log shipping is happening. I need to avoid the several hours it takes to rebuild a hot standby from scratch.
ServerA is the master. ServerB is a streaming hot standby and prefered failover server. ServerC is a streaming hot standby.
For a planned failover, maintenance on ServerA:
- Shutdown ServerB & ServerC
- Shutdown ServerA
- Copy pg_xlog from ServerA to ServerB and ServerC
- Reconfigure ServerB as master, start it up.
- Reconfigure ServerC as streaming hot standby of ServerB. Start it.
- After maintenance, reconfigure ServerA as streaming hot standby of ServerB. Start it.
For an unplanned failover, ServerA has exploded:
- Run 'SELECT pg_last_xlog_receive_location()' on ServerB and ServerC, determining which is most up to date.
- Shutdown ServerB and ServerC
- If ServerC is more up to date, copy pg_xlog from ServerC to ServerB.
- Reconfigure ServerB as master, start it up.
- Reconfigure ServerC as streaming hot standby of ServerB, start it up.
Does this look correct to people?
Am I going to end up in trouble copying files into pg_xlog like this on a busy system? With further reading, I suspect I need a recovery.conf on the new master with a restore_command pointing to an archive of the old master's WAL files (if available).
Is it overengineered? eg. will a master ensure everything is streamed to connected hot standbys before a graceful shutdown?