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.

If my standby (postgres) is behind by few seconds before failover. What is the easiest way to sync back my old primary after failover in PostgreSQL.

In Oracle we have the option of Reinstating a Failed Primary Database, if flashback is enabled.

Do we have any such option in postgres if I have all the WAL archives generated after failover in new primary server? Or do we need to go for complete rebuild of standby?

share|improve this question
3  
I don't think PG (up to 9.3) provides any help to fast-rebuild a primary. Using rsync to transfer the data dir may be less costly than a complete rebuild since it's incremental. However the benefit depends on how much activity occurred after the failover and how it's spread among tables. –  Daniel Vérité Nov 27 '13 at 14:12
add comment

1 Answer 1

In current version (Pg 9.0-9.3) there is no fast "fail-back" mechanism. You will have to follow official guidelines which state:

The former standby is now the primary, but the former primary is down and might stay down. To return to normal operation, a standby server must be recreated, either on the former primary system when it comes up, or on a third, possibly new, system.

So your second guess is correct: you need to go for complete rebuild of standby.

This arises from PostgreSQL internals, WAL format and timelines. Every time fail-over is done, it creates a new timeline. You can not safely use WAL files from new server (the one that was promoted) to replay on old server after it's revived.

From version 9.1 on, you have a tool for fast cloning of postgreSQL instances: pg_basebackup. You can also use filesystem snapshots (eg. LVM2 + XFS).

If you have a snapshot of primary postgres cluster from before the crash, and a series of WAL files from that time on, you can revive primary cluster from that snapshot and replay its WAL files. This is covered in the docs.

PS. Thanks for a good question - this is not so clear right out of the docs.

share|improve this answer
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.