Month: July 2017

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';
CREATE ROLE
reporting=# alter user replication login;
ALTER ROLE

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. Read More