Recently, I have come upon the need to deploy a high-availability/performance postgres cluster, and I looked into various replication solutions. Of the ones I saw, Streaming Replication was the easiest to set up, fairly performant and a good fit for what we wanted to do, and it’s built into postgres 9.0+.

Unfortunately, checking the documentation and howtos, I gained little insight into how it works. There are plenty of detailed guides on how to set it up, but none explain how exactly it works, so it was a bit of a black box for me. Fortunately, the guys in #postgresql are extremely helpful, and quickly explained to me the specifics of replication, which I am going to post here for future reference and in the hopes it will help someone.

The way postgres does SR is simple. Initially, you need to copy the master database to the slave, after issuing a pg_start_backup command so the slave can know at which point to start recovery. After this is done and the database is copied to the slave, you can start it. This is assuming that all the configuration is correct and the slave can connect to the master.

After it has been started, the slave will connect to the master and start receiving WAL segments as they are created in the master. Any write that occurs on the master forms a WAL segment, which the slave receives and applies to its own database.

Of course, if the slave falls behind a bit, the master will need to send it all the old segments. This is where the wal_keep_segments directive comes in. This instructs the master to keep that many more segments than it would normally keep, so slaves that have fallen behind can get up to speed again. If a slave is too far back, and there is no archive for it to read data from, it cannot sync again.

The recovery from archive is what the restore_command does. The master will archive all of its WAL segments in a directory which the slave can access in some way, so the slave will attempt to restore from that directory if direct recovery fails. The archive is not mandatory for replication, although it does insure the database against failure when a slave is offline for too long. To forgo this, just comment out the restore_command on the slave, and postgres will not attempt to read the archive.

If you like, you can attempt to write something to the master and watch it show up in the slaves in real-time. You can also attempt to write something to the slaves, which will fail (if it doesn’t, your configuration is wrong).

This is a short summary of how SR works, and you now hopefully know enough to make more sense of the howtos and the commands and directives they offer. Good luck!