Skip to content

Instantly share code, notes, and snippets.

@avesus
Forked from bartek/postgres_slave_promote.md
Last active August 29, 2015 14:25
Show Gist options
  • Save avesus/c14067434878d2b0c947 to your computer and use it in GitHub Desktop.
Save avesus/c14067434878d2b0c947 to your computer and use it in GitHub Desktop.
What to do when promoting a postgresql slave to master

This example is based on having a cascading setup, where you have a single master, a single "primary" slave, and cascading slaves which are being replicated from the primary slave. For an example of this setup, check out http://bartek.im/blog/2012/12/04/postgresql-92-streaming-primer.html

On the existing master, if accessible, stop postgres.

$ sudo service postgresql stop

And better to be safe than sorry. We can't have two masters running. (I only use this in an automated script. If you're doing this manually, you'd know if it was shutoff)

$ kill -9 `sudo cat /var/lib/postgresql/9.2/main/postmaster.pid | head -n 1` &> /dev/null

On the slave machine, trigger the promotion using your defined trigger in recovery.conf

$ touch /tmp/pg_failover_trigger

Finally, PostgreSQL 9.2 has an odd quirk where you need to adjust timelines on any OTHER slaves after promoting the primary slave to master, otherwise you will encounter out-of-sync errors. Apparently, in 9.3, this should be resolved and only require touching the trigger file.

So, if you have any remaining slaves:

$ vim /var/lib/postgresql/9.2/main/recovery.conf

recovery_target_timeline = 'latest'

$ sudo service postgresql restart

That's it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment