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 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.

share|improve this question

1 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.

share|improve this answer

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.