3

I have successfully setup continuous archiving and recovery of a master and slave node with PostgreSQL 8.4 via log shipping. I'm using keepalived to monitor both nodes and I can successfully trigger failover.

If I immediately bring the old master back online after the failover, such that log shipping immediately resumes, the master just sits there waiting for a WAL file that never arrives. It appears to be a timeline issue where the old master is looking for WAL files from what it knew the last timeline to be, but by design, the new master advanced the timeline.

I've seen some related discussions that seem to indicate that automatic failback isn't possible but they seem to involve later versions of postgres and streaming replication.

Is automatic failback possible, assuming the log shipping resumes as it should, or would one have to redo the process of making a base backup, copying it to the old master, then resuming log shipping?

EDIT: It occurred to me that I didn't mean automatic failback so much as I want the old master to rejoin the cluster as a standby.

Thanks

  • And of course the $64 question is why you can't upgrade to a newer version of Postgresql where a lot of this stuff is much easier? – Colin 't Hart Mar 8 '16 at 18:14
  • 1
    Yes yes, that is our plan. Just wanted to see if we could get something working with what we've got! – Justin Miller Mar 8 '16 at 21:02
0

In general, no, not possible to turn the original master into a standby of the new master without making a copy of the new master, replacing all the data files of the old master with said copy, and resuming log shipping.

This is because, in general, there will be lost transactions on the old master that didn't make it to the standby.

You could use rsync to make the copy go faster -- as the contents of the two databases will likely be very similar. The tool pg_rewind was invented for this purpose: it will "rewind" the old master to the point at which the failover happened.

See Heikki Linnakangas' excellent presentation about the whole topic at http://hlinnaka.iki.fi/presentations/NordicPGDay2015-pg_rewind.pdf

  • Thanks for verifying. Yeah, that was my feeling. There was a note in the docs about bringing an old master back online as a backup. Just wanted to make sure I was reading it right. – Justin Miller Mar 8 '16 at 21:03

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.