These days, replicated postgres is simple to setup, easy to maintain and ultra-reliable.  Actually, the biggest question is typically “how do I convert a slave to a master?”

Well, the short answer is, a couple of ways.  These days, the very simple ‘pg_ctl promote’ works best.

Promotion

Essentially, promotion is the process of converting your read-only, slave database servers to a read-write capable server.  Essentially, this is going to break the replication and allow you to start using your slave.  This is the type of thing you would do in the event that your master DB failed for some reason.  Right now, the postgres core database doesn’t have built-in automated failover, that’s something left to the implementer to design and build.

So, in the event that postgres fails, how do we actually get a slave promoted?

Masters

When making the decision to failover it’s important to determine the state of the master.

  • Is the master really down?
  • Is the master flapping (down, up, down, up, etc…)
  • Is the server (or VM) up, but the postgres instance down, etc…

It’s important to determine the state of the master.  If you’ve decided that you don’t trust the master database anymore (for whatever reason), it’s time to promote the standby.

Split-Brain

Split-brain scenario is a common concept when dealing with clusters / replication.  In the context of PostgreSQL master-slave replication, it refers to a situation where you:

  1. notice a master is down
  2. promote a slave
  3. the old master comes up

You now have two postgresql databases that are read-write.  The danger is that you may have some application or process that is still pointing to the old master, when the old master comes up, this process will start executing against the wrong node and now you’ve got some data in one database and some in another.  Split-Brain!

To prevent this you have to ‘fence’ or isolate the old master.  Once you’ve decided that you’re going to promote, it’s EXTREMELY CRITICAL that you ensure the old master cannot be accessed by any clients.  If the old master is available, you can end up with some of your applications talking to one DB and some talking to your secondary.

Isolate the master

Machine up

edit pg_hba.conf

Add 2 lines BEFORE any existing rules:

local all all 0.0.0.0 deny
host all all 0.0.0.0 deny

Machine Down

This is the most dangerous case.  When the old master machine is down, it’s critical that it stay down.  If someone or something were to restart the box and it came back up, you would end up with a split-brain scenario (i.e. two read-write postgres servers).  This is one of the areas that causes automated failover the most heartburn.  When you’re doing manual failover and this happens, I would perform any and all of the following (if possible):

  1. Physically sever the network connection to this host
  2. Virtually sever the network connection
    1. disconnect the virtual network adapter
  3. Block all traffic to/from this host via a firewall

It’s important to make sure that the host cannot ‘accidentally’ come back; if you’ve promoted a slave and this old master returns, you’re going to have a very dangerous situation on your hands.

Runbook

I’m going to go through this as a ‘runbook’ so that it’s easy to follow:

preconditions:

  1. Ability to login to slave as ‘postgres’ user
    1. This is the user that is running the postgresql database
  2. Full Path to: pg_ctl (in the postgresql bin directory)
  3. Full Path to: data directory

Each of the above is very configurable based on where you got your postgresql installation.  Here’s a few defaults of the popular packaging mechanisms

BigSQL RPMs

  1. User             = postgres
  2. pg_ctl path = /opt/postgresql/pg9x/bin/pg_ctl
  3. data path     = /opt/postgresql/data

PGDG

  1. User             = postgres
  2. pg_ctl path = /usr/pgsql-9.x/bin/pg_ctl
  3. data path     = /var/lib/pgsql/<version>/data

Steps to Promote

  1. We notice that master DB appears down
  2. Determine – Am I going to fix the master, or failover?
  3. If you’re going to fix the master, stop here, go fix it!
  4. If you’re going to failover
    1. Fence (isolate) the master (as discussed above)
    2. Login to the slave
    3. su – postgres
    4. pg_ctl -D /path/to/data promote

Now your slave will be put in to read-write mode.  At this point, you’e moved the database.  Now you need to move the connections from your application.  This will be the topic of another post, but, typically a simple update to your application’s configuration file gets you where you need to go.