Postgresql Streaming Replication
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).
Step 9 - Restart postgresql on the slave
systemctl start postgresql-9.3