Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Shutdown ServerB & ServerC
  2. Shutdown ServerA
  3. Copy pg_xlog from ServerA to ServerB and ServerC
  4. Reconfigure ServerB as master, start it up.
  5. Reconfigure ServerC as streaming hot standby of ServerB. Start it.
  6. After maintenance, reconfigure ServerA as streaming hot standby of ServerB. Start it.

For an unplanned failover, ServerA has exploded:

  1. Run 'SELECT pg_last_xlog_receive_location()' on ServerB and ServerC, determining which is most up to date.
  2. Shutdown ServerB and ServerC
  3. If ServerC is more up to date, copy pg_xlog from ServerC to ServerB.
  4. Reconfigure ServerB as master, start it up.
  5. 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?

share|improve this question
add comment

1 Answer

I don't think copying pg_xlog after you've lost the master is going to allow you to make ServerB or ServerC the new master. If you might need to remaster, you really need to add log-shipping archives as well (this is a current limitation in Postgres, at least through 9.2).

Also, if the master has been shut down cleanly, you don't need to copy anything.

share|improve this answer
    
What do you mean by remaster ? –  Daniel Serodio Sep 13 '12 at 19:41
    
Surely there is some way to make the standby the new master. Otherwise, what is the point of streaming replication? –  phord Nov 8 '12 at 20:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.