8

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 ?

Improve this question
2
  • 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, 2012 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, 2012 at 13:04

3 Answers 3

Reset to default
7

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.

Improve this answer
4
  • 4
    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, 2016 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, 2016 at 19:08
  • @ChrisTravers which version is sufficient?
    – Dims
    Jan 26, 2021 at 9:24
  • @ChrisTravers Why we need to turn off primary? Can step 1 skipped?
    – Niraj Nandane
    Dec 30, 2021 at 6:35
3
  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
Improve this answer
4
  • What if we don't have pg_ctl? It's not installed on Ubuntu, at least it's not on the path.
    – Marc
    Nov 19, 2022 at 18:01
  • How can we find where the data directory is?
    – qbush
    Feb 1 at 21:58
  • Ubuntu has it but it's not in the default PATH. Try dpkg -S pg_ctl to locate it. It's probably in /usr/lib/postgresql/$MAJORVERSION/bin/pg_ctl. On Ubuntu, you're expected to use pg_ctlcluster instead.
    – Mikko Rantalainen
    Feb 23 at 12:28
  • Note that you MUST NOT start the old primary/master server without standby.signal file created in the data directory or it cannot be added as hot-standby replica for the new master because files change. As long as new master has seen every transaction that the old master had executed before it was successfully shutdown, it can be attached as a slave to the new master because new master will do timeline change during pg_ctl promote and keep all the data in WAL log files. Note that you must have wal_level = replica and big enough wal_keep_segments on the new master (/old slave).
    – Mikko Rantalainen
    Jun 12 at 8:57
-2

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.

Improve this answer
1
  • 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, 2016 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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