Configure postgresql recovery again to be slave

Question

I completed a replication setup using PostgreSQL. Everything was working fine, so I proceeded doing a test by bringing down the main database and promoting the slave to be new master (via touch trigger file).

Again,it worked as expected and the slave became a full read/write database. Now, I want to return everything to normal state which means starting up master database, copying all the transactions that were done on slave during the transition, and finally start again the replication on slave. How can I do this... there seems to be no docs about restoring everything to normal state. Should I just rename the recovery.done to recovery.conf?

Thanks a lot

Answer 1

Assuming you are using streaming replication, "no latency". If you can stop writes or stay on the new master B, some steps below can be reduced

If you stop writes, before you failover back to old master A, it could be a master without any issues, most of the times.

But as you have mentioned that you have writes on the new master B after failing over to it, you will have to

1. resynchronize the old master A as a standby, using pg_start_backup-rsync-pg_stop_backup (and of course recovery.conf)
2. promote A using the trigger file, etc
3. restart B as a standby of A
4. if it doesn't start (due to continuous writes), you'll have to rsync it again as listed above

I would love to hear if there are other options :)

Answer 2

which means starting up master database, copying all the transactions that were done on slave during the transition, and finally start again the replication on slave.

No, it doesn't. The old master cannot just be re-synced with the new master; you must make a new base backup of the new master, install it on the old master, set it up as a streaming replica, let it catch up, and then fail over again.

It's the same process you used to create the replica and then promote it to become the new master.

I think you can (maybe) avoid the need to take a new base backup only if you shut the master down while the replica was completely synced up, if the replica was also WAL archiving. In this case you might just be able to create a recovery.conf before starting the old master DB back up and get it to catch up with the new master before using the same promotion procedure you used before.

There is ongoing work in the PostgreSQL project to make this process easier by allowing you to roll a master server back to an earlier point in time for the purpose of switching it to operation as a streaming replica. So you should in future be able to demote a master to a replica server, something that is not really possible at the moment. That's going to be PostgreSQL 9.4 at the earliest, though.

Answer 3

I had to figure this out myself so I'll write an answer. This was executed with PostgreSQL version 12.x but I think at least newer versions should match the behavior.

Here's the setup: three PostgreSQL servers db1, db2, db3 where db1 was initially the master and db2 and db3 were async hot standby slaves. Both slaves were directly replicating from db1 initially.

To switch db2 as new master with minimal resync time (the full startup time for PostgreSQL to finally re-attach db1 to the cluster with about 800 GB database on fast storage system was around 5 seconds – after that read-only queries could already be executed), following was required:

• Stop db3, reconfigure it to sync from db2, restart db3. The cluster replication was now running in chain db1 -> db2 -> db3. This reconfiguration was nearly instant because after db3 was turned off, db1+db2 continued to create new WAL logs and when db3 was connected as a slave for db2, it only needed to replay the new logs.

• To promote db2 we first stopped db1 so that it had seen the same amount of transactions as db2. Then we promoted db2 using pg_ctl promote on db2 (pg_ctlcluster in case of Ubuntu/Debian). The db3 was continuing syncing from db2 so we still had two servers with the same data (db3 slightly delayed because of async streaming connection). If db1 is still executing as master while you promote db2 you cannot reattach it easily!

• Now it was possible to simply add standby.signal (I'm not sure if details matter but we created zero byte file with mode 600 and owner was postgres – I know that PostgreSQL is sometimes very picky about file ownership and file access bits) on the data directory of db1 and reconfigure its config to sync from db2.

• When db1 was now started, it was starting from the transaction it had completed before shutdown and it could directly apply the transactions from WAL log it could receive from db2 without starting from new backup again.

• The ending state was db2 as the master and db1 and db3 syncing directly from db2.

To switch back to original setup, you would need to redo all the above with db1 and db2 names swapped in every step.

However, if the db1 has seen even one extra transaction that db2 and db3 couldn't originally see, then you cannot add db1 to the cluster without full resync because the history it sees has been forked from the current state of db2+db3. If you have correctly configured pg_rewind before all this, it should be possible to rewind db1 backwards just enough to avoid forking and the resync is nearly instantanous again.