Replication

Version 9.6

On Primary

postgresql.conf

hot_standby = 'on'
hot_standby_feedback = 'on'
wal_level = 'logical'
wal_compression = 'on'
max_wal_senders = '20'
max_replication_slots = '40'
wal_keep_segments = '100'

pg_hba.conf

host    replication   all                        172.17.10.51/32        trust

Reload configuration

select pg_reload_conf();

systemctl reload postgresql-9.6.service

Create new replication slot

SELECT * FROM pg_create_physical_replication_slot('the_slot_name');

Verify the slot creation

SELECT * FROM pg_replication_slots;

If you need to remove the created slot

-- Run only to remove the slot!
SELECT pg_drop_replication_slot('the_slot_name');

On Secondary

After installing the same PostgreSQL version from primary, do the initdb.

/usr/pgsql-9.6/bin/initdb --pgdata=/var/lib/pgsql/9.6/data
# or
/usr/pgsql-9.6/bin/postgresql96-setup initdb

Stop Postgres and remove the data directory

rm -rf /var/lib/pgsql/9.6/data

Create replicated DB, with the postgres user. Use tmux for this.

/usr/pgsql-9.6/bin/pg_basebackup --pgdata=/var/lib/pgsql/9.6/data --write-recovery-conf --slot=the_slot_name --progress --host=10.0.3.33 --xlog-method=stream --checkpoint=fast

Start the Postgres service, with the root user, and using screen too.

# This start command will run for a while. Be patient.
systemctl start postgresql-9.6.service

Verify the status

systemctl status postgresql-9.6.service

Go back to the primary and verify the slot status (active field must be true)

SELECT * FROM pg_replication_slots;

-[ RECORD 1 ]-------+-----------------------------
slot_name           | the_slot_name
plugin              |
slot_type           | physical
datoid              |
database            |
active              | t
active_pid          | 242213
xmin                | 1936192908
catalog_xmin        |
restart_lsn         | 65966/54EE6000
confirmed_flush_lsn |