Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
This is a copy and paste of stackoverflow.com/q/21785670/398670 . –  Craig Ringer Feb 14 at 22:38
    
Have a look at repmgr: repmgr.org –  a_horse_with_no_name Feb 14 at 22:54

2 Answers 2

up vote 1 down vote accepted

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 :)

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.