DB Wiki

# su - postgres
# createuser -U postgres repmgr -P -c 5 --replication

Add a rule that will allow the database user from the standby access the primary.

# Allow replication connections
host     replication     repmgr         [standby-IP]/32        md5

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/pg_log_archive/%f && cp %p /var/lib/pgsql/pg_log_archive/%f'
max_wal_senders = 3

pg_ctl stop
pg_ctl status

cd /var/lib/postgresql/10
rm -rf main

pg_basebackup -D <OutputDirectory> -h <HostName> -U <User> -v -P -c fast -X stream -R

-v        Verbose
-P        Progress Reporting
-c fast   Fast Checkpoint
-X stream Copy WAL files at the same time
-R        Create minimal recovery.conf file

Once the copy has completed, you will see the following message in the nohup.out file:

pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

hot_standby = on

standby_mode = on
primary_conninfo = 'host=[Primary IP] port=5432 user=repmgr password=[Password]'

pg_ctl start
pg_ctl status

ls -lrt $(ps -ef | grep postgres | grep recovering | grep -v grep | awk {'print $12'})

tail -f /var/log/postgresql/postgresql-9.4-main.log
WAIT FOR : consistent recovery state reached at ...
           database system is ready to accept read only connections

SELECT * FROM PG_STAT_REPLICATION;
SELECT PG_IS_IN_RECOVERY();
SELECT TXID_CURRENT_SNAPSHOT();
SELECT * FROM PG_LAST_XACT_REPLAY_TIMESTAMP();
 
-- Note some function names changed in PostgreSQL 10
SELECT pg_is_in_recovery(),
pg_is_wal_replay_paused(),
pg_last_wal_receive_lsn(),
pg_last_wal_replay_lsn(),
pg_last_xact_replay_timestamp(),
round(EXTRACT(epoch FROM now() - pg_last_xact_replay_timestamp())) AS slave_lag;

wal_level = replica

pg_ctl stop
pg_ctl status

pg_dumpall -s >schemadump.sql
psql -d postgres -f schemadump.sql

Any schema changes after this point will not be replicated.

CREATE ROLE repuser REPLICATION LOGIN PASSWORD '<password>';

CREATE PUBLICATION pub_upgrade FOR ALL TABLES;

This needs to be done on every database in the instance.

CREATE SUBSCRIPTION sub_upgrade 
CONNECTION 'host=192.168.0.2 port=5432 dbname=doob user=repuser password='<password>' 
PUBLICATION pub_upgrade;