3

Imagine I have 3 PostgreSQL 9.3 databases (A, B, and C) set up in a cascading, streaming replication scenario (i.e. am NOT manually shipping WAL files, am having Postgres ship them over TCP internally) like so:

A --> B
      |
      V
      C

where A is the replication master for B, and B is the replication master for C.

If B fails (goes offline but doesn't failover) and I need to make C use A as replication master as quickly as possible, is it safe for me to skip rebuilding C from A and just set up A as C's replication master? I would do so by turning off the Postgres service on C, modifying the master host in the replication.conf file on C to point to A, and then starting the Postgres service on C. I've done this in practice and C replicates off of A without any data loss in my tests, but are there some cases where doing this could result in data corruption on C?

Intuitively, this should be a safe operation. Since neither B nor C do a failover, they will remain on the same timeline as A, and since the new master for C is "upstream" of the previous master, C can never be ahead of the WAL position of the new master.

Followup question (to be answered iff the previously described operation is deemed "safe"): Imagine that I perform the above steps, B comes back online, I rebuild it from A and start binary streaming replication from A such that I now have the following scenario:

A --> B 
|
V
C

where A is the replication master for both B and C.

I want to make B become C's replication master again, and like before, I want to minimize C's downtime. Can I stop the Postgres service on C, ensure that the WAL playback position on B is further along than that of C (waiting for B to catch up with C if C was "ahead" of B in WAL playback), then switch the replication master host for C to point at B in the recovery.conf file and start the Postgres service?

Once again, I've done this in practice and it seems to work, but I want to be sure that there aren't any scenarios that result in data corruption on C. In this second case, assume that replication from A to B is verified to be working correctly when Postgres on C is turned off (if replication from A to B breaks while C is still replicating from A, there can be divergence between C's and B's databases, which will obviously cause problems). Is this a safe operation?

1

Yes, it's safe. B is an identical copy of A that must always be at or behind it in time, so it is safe to switch replication from B to A.

Personally, though, I'd use WAL shipping from A as my fallback. I'd leave streaming replication configured to use B. If it's unreachable, C will try the WAL archives, and it'll find the WAL archives from A and replay from them, periodically retrying B until it can connect again.

  • Ok, that's what I was thinking as well. Thanks! – KSletmoe Aug 14 '15 at 21:23

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.