How can I minimize data loss & data transfer when I failback with the requirement that I promote the old primary back to its primary status?

Question

The definition of failback according to IBM docs is "the process of returning production to its original location after a disaster or a scheduled maintenance period".

From my reading of postgresql community postings, bringing an old primary server back online after failover as a standby that follows the new primary may also be considered failover.

This may be because it is widely accepted that having identical DB servers is best practice. However, this option is not available to me.

I have 2 servers: Costanza and Kramer. Costanza is a high performance server. Kramer is not.

Here is the scenario I am trying to optimize, step by step:

1. Costanza is in an irrecoverable failure mode (typical)
2. Kramer is promoted to primary
3. Costanza is available again and PGDATA is intact
4. Synchronize Costanza & Kramer such that no data written to Kramer is lost
5. Promote Costanza back to primary status
6. Establish Kramer as a standby

I am focused on steps 4 & 5.

When running pg_rewind to replay WAL files, it appears that "modifications that have happened on the source server after the latest common checkpoint are ignored – these will be recovered anyway when the target server becomes a standby of the source server." -- see this SO question

I deduce from this that simply running pg_rewind will not synchronize Costanza & Kramer (step 4) because writes to Kramer may have occurred after the last common checkpoint. This is also what we are observing when drilling failbacks.

My solution for step 4 is to:

4a. Run pg_rewind to synchronize divergent timelines

4b. Establish Costanza as a standby of Kramer and allow it to catch up on the replication lag (assuming WALs post checkpoint exist)

Then

5. Shutdown Kramer and promote Costanza to primary (again causing timeline divergence)

6a. Run pg_rewind with Kramer as target

6b. Establish Kramer as a standby of Costanza and allow it to catch up on the replication lag (again, assuming WALs post checkpoint exist)

This is an infrequent scenario. But I do not know what failure modes I will encounter and whether or not PGDATA will be intact.

This is a very large database and I want to avoid moving data via base backup whenever possible.

This approach seems inefficient to me because I must run pg_rewind twice and I have to establish Costanza as a standby just to apply modifications that occurred to Kramer post common checkpoint.

I must minimize data loss and this solution appears to do this with minimal data transfer.

(An aside: Should I even care about the additional timeline creation? This seems unavoidable as well as it occurs on promotion of a DB to primary)

Is there any way to apply the modifications on Kramer post common checkpoint to Costanza without establishing Costanza as a standby of Kramer?

Does there there exist a shorter path to an equivalent outcome? Or is there a path that you may judge "easier" to follow in a DB failure scenario?

Answer 1

Before I answer your question, let me remark that you forgot a very important step in your failover procedure: between steps 1 and 2, you have to make dead sure that Constanza is down. This may seem trivial, but if you don't do that, it can happen that some clients are still connected to the database on Constanza, and you end up with a "split brain" scenario, which is arguably worse than an extended down time.

Apart from that, your steps are fairly accurate, but more complicated than necessary. After you turned Constanza into a standby and let it catch up with Kramer, you perform a clean shutdown on Kramer. That makes sure that all changes to Kramer are replicated to Constanza, and there is no need for pg_rewind. All you have to do is to make sure that primary_conninfo on Kramer points to Constanza and create standby.signal on Kramer.

If you are using replication slots, each failover and switchover also needs to take care of creating them after promotion, because replication slots are not yet (as of PostgreSQL v16) replicated.

Finally, I think that it is a bad setup to have Kramer be weaker than Constanza. If Kramer cannot handle the load, your high availability setup won't provide high availability.