Setting up a failover Database for PostgreSQL

This is a short guide, more a memento for me, to explain how I did setup a failover for our postgresql DB. It actually was a lot simpler than I thought of firsthand, but like all devops stuff, you just keep shaving yaks until everything works, and it looks like magic.

I am hardly a devops guy, and even less of a database guy. So it definitely feels like magic, and there are many parts / tweakings that I completely ignore.

The goal

Given a machine whose job is only to be a Database with PostgreSQL (master), create another machine (standby) such that:

  • it contains the data pushed to the master shortly after (even a few seconds would be acceptable)
  • it creates minimal overhead on the master
  • it can become a new master in case something really really bad happens to it

Luckily, this is a very common use case for PostgreSQL, and it is thoroughly documented, either with the names hot standby, if the standby machine can be used for queries (and only that) or warm standby if it’s used only for the failover event, as in our case.

The main idea: Log-shipping

The keystone to all of this is Write-ahead Log (WAL): in a nutshell, all the changes to data files are bundled in the WAL (which is written sequentially) and, when filled (typically to 16MB) written to the actual disk, thus reducing the number of total writes to disk (especially true with many small transactions).

This has an awesome side effect:

  • WAL data can be archived and replayed from a previous snapshot,
  • WAL data can be sent to another machine, log shipping, which is what we’ll be doing.

There are mainly two ways of shipping logs:

  • File-based: you move the WAL files from one machine to the other, for example with rsync
  • Streaming-replication: the standby server connects to the master, which streams WAL records as soon as they are generated, without waiting for the WAL to be completely filled

I found many many resources online that help you cope with the first approach, which I don’t find much desirable:

  • I don’t want to think about files
  • I don’t want to think about servers communicating between themselves with any other than a postgres connection (in many guides they start by making the server talk to themselves with ssh, copying keys and whatnot. I don’t care for that)
  • I don’t want to think about a failure of data communication

Most of the guides / tutorials that I found, have somewhat limited problems from these points but just because they assume the master and standby server have both access to at least a common folder, mounted in their filesystem. I definitely don’t want those assumptions in my solution.

In general, you have much more things to consider. I want something simple.

The guide

Both master and standby have to be, essentially, very similar. Same version of PostgreSQL, same architecture, etc. I think this is not really a problem nowadays, but it’s an important premise to made.

On master

Create a user (which i decided to call rep) that will be used for replication (feel free to change the password)


And edit the following files in the directory /etc/postgresql/9.3/main (eventually change the PSQL version):

In pg_hba.conf, append somewhere not at the end

host    replication     rep     IP_SLAVE/32   md5

In postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 3

Just restart postgres with

sudo service postgresql restart

On standby

In this machine, stop postgresql with

sudo service postgresql stop

In pg_hba.conf, append

host    replication     rep     IP_MASTER/32   md5

Add also here to postgresql.conf

listen_addresses = '*'
hot_standby = on

Then, we need to create a base backup

sudo -u postgres pg_basebackup -h IP_MASTER -D /var/lib/postgresql/9.3/main -U rep -v -P --xlog-method=stream

Note that in my machine, I had to delete the existing folder that I wanted to override.

Then, create (if not existing) edit the file /var/lib/postgresql/9.3/main/recovery.conf to contain:

standby_mode = 'on'
primary_conninfo = 'host=IP_MASTER port=5432 user=rep password=yourpassword'
trigger_file = '/tmp/postgresql.trigger.5432'

The last line in the file, trigger_file, is one of the most interesting parts of the entire configuration. If you create a file at that location on your slave machine, your slave will reconfigure itself to act as a master.

This will break your current replication, especially if the master server is still running, but is what you would need to do if your master server goes down. This will allow the slave to begin accepting writes. You can then fix the master server and turn that into the slave.

Then simply start postgres, the log (found at /var/log/postgresql/postgresql-9.3-main.log) should show something like:

2016-05-04 16:59:29 CEST LOG:  entering standby mode
2016-05-04 16:59:29 CEST LOG:  consistent recovery state reached at 0/49000130
2016-05-04 16:59:29 CEST LOG:  record with zero length at 0/49000130
2016-05-04 16:59:29 CEST LOG:  database system is ready to accept read only connections
2016-05-04 16:59:29 CEST LOG:  started streaming WAL from primary at 0/49000000 on timeline 1


I had a couple of issues that made this process a bit bumpy:

  • Make sure you can connect from the standby to the master: in my case, I am using IPs in the local network, and use very strict firewall rules.

Just quickly check with the command psql -h IP_MASTER that everything is all right.

  • If you see, in the standby,
2016-05-04 16:58:06 CEST FATAL:  the database system is starting up

You forgot to put hot_standby = on in postgresql.conf