I would like to know what is the way to switch the role of 2 servers when using warm standby?

I have 2 servers : S_1 and S_2, and at a time T, S_1 is the master server and S_2 is a warm standby with streaming replication.

At a later moment, I would like to switch S_2 become the master server and S_1 the warm standby.

What do I have to do to switch roles ?

  • a warm standby or a hot standby? In Postgres, "warm standby" is usually used to refer to a second server which is keeping up to date, but not accepting connections. – Josh Berkus Dec 3 '12 at 23:36
  • Yes, the second server is a warm standby, and I like to know what steps are needed to switch master and warm standby mode. – iXô Dec 4 '12 at 13:04

Keep in mind that currently you cannot demote a master to a standby. Doing so will corrupt your database!

The basic documentation for doing this is at: http://www.postgresql.org/docs/9.1/static/warm-standby-failover.html

However the basic thing is you can't switch these other than:

  1. Turn off primary

  2. Promote secondary

  3. Rebuild former primary as new secondary off the new primary.

  • 3
    These days pg_rewind can be used to turn the former master into a standby of the new master much more quickly than a complete rebuild. Previously rsync or similar would have been a quicker way to do this. – Colin 't Hart Dec 28 '16 at 12:10
  • 1
    If you are on a sufficiently high version of PostgreSQL. This specified 9.1 which would have been too old. – Chris Travers Dec 28 '16 at 19:08
  • @ChrisTravers which version is sufficient? – Dims Jan 26 at 9:24
  1. Stop the primary service

systemctl stop postgresql ( This command might be different on your OS )

  1. login to the secondary and promote it to primary ( -D is for the data directory )

pg_ctl promote -D /opt/pgsql/data

  1. Now at this point you need to change the settings on the old primary to become the new replica and start the service again

PostgreSQL server can be promoted to master using below command:

pg_ctl promote -D

PostgreSQL server can be demoted to slave (post promotion using above command) by deleting/moving 'recovery.done' file @ /data dir. And restating the the server.

  • 2
    Demoting a server to a slave isn't as simple as this in general; pg_rewind exists for this purpose. – Colin 't Hart Dec 28 '16 at 12:12

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.