Close modal

Blog Post

Replication with Postgresql

Development
Fri 18 March 2016
0 Comments


So there's already a heap of guides, why do we need another one? Good question, the problem is it took me a heap of guides to understand this - ultimately the Postgres wiki entry IS correct and the most accurate, however for those whose understanding is tentative, it might not exaplain why certain things are what they are or what to change.

How Replication Works

The latest (and greatest) versions of Postgres use a write ahead log (WAL) to enable live streaming replication when set to 'hot standby'. In this situation you have one master streaming updates to one or more slaves. It is worth noting that slaves are relegated to being able to serve readonly requests as they are slaves, slaves may become masters during a failover but there is only one master at a time. Some configurations do not even make use of slaves for read-only requests, but we're paying for them so why not let them offload some of the master servers readonly requests for load balancing. I won't even begin to discuss anything related to multiple master setups, there are people who have tried, but that's a road fraught with far more difficulty than most of us would care to deal with.

Setup

All Postgresql Instances

I use Debian, this guide will be focused on Debian specific (In this case 8.0 - Jessie). It's likely applicable to Ubuntu as well. With other distros your mileage may vary but the general gist of it will be the same.

Setup on the master will be pretty typical. I suggest you use the Postgres APT sources for the most up to date versions: http://www.postgresql.org/download/linux/debian/

With this configuration the real configs (and not the auto generated ones live here)

# ls /etc/postgresql/9.5/main/
environment  pg_ctl.conf  pg_hba.conf  pg_ident.conf  postgresql.conf  start.conf

This is true for both the master and the slave.

Master Setup

If for some reasom your master does not have a cluster, create one as such:

# pg_createcluster -d /var/lib/postgresql/9.5/main 9.5 main

Again, in this instance the actual postgres database and instance specific data otherwise known as ${PG_ROOT} in many examples is: /var/lib/postgresql/9.5/main

For replication you will need to add a role of 'replication' or whatever you see fit to call it. Do this as postgres as root will (or should not) be given superuser role in postgres.

# sudo -u postgres psql -c "CREATE USER replication REPLICATION LOGIN ENCRYPTED PASSWORD '<SOMEPASS>';"

Add the following entry(ies) to the end of /etc/postgresql/9.5/main/pg_hba.conf.

host    replication     replication     <SLAVE>/0         md5

vim /etc/postgresql/9.5/main/postgresql.conf

Uncomment and modify the fields below as appropriate in /etc/postgresql/9.5/main/postgresql.conf.

listen_address = '*' # (or enter specific interfaces here)
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32

You will need to re(start) your master server for these changes to take effect.

Slave(s) Setup

If you're relatively new to this let me save you some time with a couple of 'gotchas':

  • Master and slave must be the exact SAME database, they have a unique ID that identifies them.
  • Essentially we are to copy the database over at a file level from master to slave, it's not fancy.
  • Once the slave is loaded up with a copy of the DB, we enable logging and it may catch up.

First we need to setup a few configs on the slave (mostly like the master). We don't need to change the listen unless we wish to configure the failover (we won't cover this for now).

wal_level = hot_standby     #same
max_wal_senders = 5         #same
wal_keep_segments = 32      #same
hot_standby = on            #new

Here are the commands to run:

Stop the server as root if it's running:

# service postgresql stop

Now we do the actual meat of the work - nuking all our postgres data (except whats in /etc) and loading whatever the master has instead.

postgres$ rm -rf /var/lib/postgresql/9.5/main
postgres$ pg_basebackup -h $MASTER -D /var/lib/postgresql/9.5/main -P -U replication --xlog-method=stream

postgres$ bash -c "cat > /var/lib/postgresql/9.5/main/recovery.conf <<- _EOF1_
  standby_mode = 'on'
  primary_conninfo = 'host=${MASTER} port=5432 user=replication password=${PASS} sslmode=require'
  trigger_file = '/tmp/postgresql.trigger'
_EOF1_
"

Cross your fingers and start the server as root:

# service postgresql start

If all went well, you can see both changes from the server propagate here. There should also be activity in the logs files on the master and slave servers.

]