Streaming replication setup on postgres 9.6

Postgres streaming replication in 7 steps.

1. Create replication user on primary and grant ‘REPLICATION’ and ‘LOGIN’ privilege.

reporting=# CREATE ROLE replication WITH REPLICATION PASSWORD 'replUs3r';
reporting=# alter user replication login;

2. PostgreSQL.conf changes on the primary

listen_addresses = '*'
wal_level = replica
max_wal_senders = 5 
wal_keep_segments = 32 
archive_mode = on
archive_command = 'test ! -f /var/untd/pgsql/archive/%f && cp %p /var/untd/pgsql/archive/%f'

listen_addresses variable is required to allow all hosts to connect to this database.

wal_level is minimal by default, we need to set it to replica so that PostgreSQL server logs all changes to transaction logs (xlogs).

wal_keep_segments specifies the number of past log file segments to be kept in pg_xlog directory. If the standby server falls far behind than these many segments, replication breaks.

We need to enable archiving so that we can easily sync standby if it falls too behind than the specified ‘wal_keep_segments’.

3. pg_hba.conf changes on the primary. Include IP address of standby database so that it can connect to the primary.

host replication replication md5

4. Cleanup datadir On standby and start copying data, we will be using pg_basebackup on standby db, -P flag will show progress.

rm -rf /var/untd/pgsql/data/*
pg_basebackup -h -D /var/untd/pgsql/data -U replication -v -P

5. After data is copied, we need to create recovery.conf on standby db. Provide primary connect details

standby_mode = 'on'
primary_conninfo = 'host= port=5432 user=replication password=replUs3r'
trigger_file = '/var/tmp/standby.trig'
restore_command = 'cp /var/untd/pgsql/archive/%f %p'

We are providing restore_command here so that if the standby is too far behind primary, we can copy the archives from primary to standby to this location and standby would look at this location for recovering missing xlogs on the primary.

6. Startup standby db

[[email protected] data]$ sudo /etc/init.d/postgresql start
Starting postgresql service: [ OK ]

7. Verify log file on standby

LOG: entering standby mode
cp: cannot stat `/var/untd/pgsql/archive/000000010000009500000017': No such file or directory
LOG: started streaming WAL from primary at 95/17000000 on timeline 1
LOG: redo starts at 95/17000108
LOG: consistent recovery state reached at 95/17015F10
LOG: database system is ready to accept read only connections

We could see that it started looking at archive directory on standby for xlogs, it did not find any, so it started streaming write ahead logs from primary.

Leave a Reply

Your email address will not be published. Required fields are marked *