Postgresql Streaming Replication

From Wiki
Jump to: navigation, search

Step 1 - Stop postgres on the slave.

First make sure postgresql is not running on the slave.

systemctl stop postgresql-9.3

Step 2 - Remove existing data Remove the existing data directory on the slave. This will be recreated by the pg_basebackup command.

rm -rf /var/lib/postgresql/9.3/data

Step 3 - Configure pg_hba.conf on master

Add host entries for any IPs that need access to the pg_hba.conf file on the master.

host    replication    replicator    192.168.1.20/32    md5
hostssl replication    replicator    192.168.1.20/32    md5

This now allows user replicator from IP 192.168.1.20 using md5 password method.

Step 4 - Configure postgresql.conf on master

Update the postgresql.conf file and change the settings required to get streaming replication working.

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

Be sure to check the last setting, checkpoint_segments, as this may have been manually updated if you use the pgtune tool.

Restart postgresql to apply changes.

systemctl restart postgresql

Step 6 - Create replicator user on the master

psql -U postgres -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'your-password';"

Step 7 - Configure postgresql.conf on the slave

listen_addresses = '*'
hot_standby = on
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

Step 8 - Copy data from master to slave

On the slave, we will use the pg_basebackup utility to get a copy of the master's data. This will prompt for a password which is the one you configured in Step 6 when you created the replicator user.

pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/9.3/main -U replicator -P -v -X stream

Next create recovery.conf file to manage failover settings.

primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=your-password'
trigger_file = '/var/lib/postgresql/9.3/data/failover'
standby_mode = 'on'

There are a couple of other options you can specify for the primary_conninfo (and probably more).

keepalives_idle=60
sslmode=require

Step 9 - Restart postgresql on the slave

systemctl start postgresql-9.3