Turning slaves into masters

A slave can be a wonderful thing if you want to scale up reads or get a backup of your data. But a slave might not always have to remain a slave. At some point, you might need to turn a slave into a master. In classic cases, this happens when the original master crashes or the hardware has to be changed.

Tip

Be careful when promoting a slave. It cannot easily be demoted anymore. Once a slave has turned into a master, it can be a slave again only after performing a complete resync or after running pg_rewind, which will be available by default in PostgreSQL 9.5.

PostgreSQL offers some simple ways to do this. The first way, and most likely the most convenient way, to turn a slave into a master is by using pg_ctl:

iMac:slavehs$ pg_ctl -D /target_directory promote
server promoting
iMac:slavehs$ psql test
psql (9.2.4)
Type "help" for help.
test=# CREATE TABLE sample (id int4);
CREATE TABLE

The promote command will signal the postmaster and turn your slave into a master. Once this is complete, you can connect and create objects.

Tip

If you've got more than one slave, make sure that those slaves are manually repointed to the new master before the promotion.

In addition to the promote command, there is a second option to turn a slave into a master. Especially when you are trying to integrate PostgreSQL with high-availability software of your choice, it can be easier to create a simple file than to call an init script.

To use the file-based method, you can add the trigger_file command to your recovery.conf file:

trigger_file = '/some_path/start_me_up.txt'

In our case, PostgreSQL will wait for a file called /some_path/start_me_up.txt to come into existence. The content of this file is totally irrelevant; PostgreSQL simply checks whether the file is present, and if it is, PostgreSQL stops recovery and turns itself into a master.

Creating an empty file is a rather simple task:

iMac:slavehs$ touch /some_path/start_me_up.txt

The database system will react to the new file, start_me_up.txt:

FATAL: terminating walreceiver proced fire up:
LOG: trigger file found: /some_path/start_ss due to
administrator command
LOG: redo done at 0/50000E0
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

PostgreSQL will check for the file you have defined in recovery.conf every 5 seconds. For most cases, this is perfectly fine, and fast enough by far..