Skip to content

Instantly share code, notes, and snippets.

@Geekfish
Forked from bartek/postgres_slave_promote.md
Created June 19, 2013 13:08
Show Gist options
  • Save Geekfish/5814188 to your computer and use it in GitHub Desktop.
Save Geekfish/5814188 to your computer and use it in GitHub Desktop.

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