7

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?

  • 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
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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