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.