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();

or

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 install 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 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 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 stot 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 |