Streaming Replication Failover - how to point second slave at new master?

Question

I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over.

For the cluster itself, I have three identical Postgres 9.2 servers set up like so:

• Server A - Master DB
• Server B - Slave DB with synchronous Streaming Replication
• Server C - Slave DB with asynchronous Streaming Replication (possible synchronous server)

When Server A fails, server B takes over (no problems here). However, I need to make sure that Server C is 're-pointed' to stream from Server B.

Currently, I'm achieving this by doing a full base backup from B to C - it works, but seems like a sledgehammer approach given that the two databases should be almost identical at the point when server A fails...

Is there a sensible way to get server C to start streaming from server B without doing a full base backup?

I'm not looking for a hack to get this to work - the lab system will eventually move into a production environment, so I need a solid solution...

Any advice or pointers would be most welcome!

Cheers.

Answer 1

In PostgreSQL 9.2, you should be able to just repoint server C to B by changing it's recovery.conf file, if you are also using log archiving (and have a restore_command defined in your recovery.conf). It should work automatically - but it does require the log archive to work (machine B will send critical information about timeline switching to the log archive, which machine C will then replay).

PostgreSQL 9.3 will be able to deal with this repointing of the slave over pure streaming replication.