What to do when promoting a postgresql slave to master

postgres_slave_promote.md

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.

It's bad security practice to put that file in /tmp

And how to return the slave(promoted) to slave again?

never kill -9 a postgres process.

Do you guys know where can I find information about this?

Apparently, in 9.3, this should be resolved and only require touching the trigger file.

BTW for those of you who are looking for Bartek's blog post, here is it:
http://justbartek.ca/a-postgresql-92-streaming-replication-primer.html