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

-D        Data Directory
-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:

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

hot_standby = on

SELECT * FROM pg_create_physical_replication_slot('primary_slot');

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

pg_ctl start
pg_ctl status

cd $HOME/11/main/pg_wal
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();
SELECT pg_wal_replay_pause();
SELECT pg_wal_replay_resume();
 
-- Note some function names changed in PostgreSQL 10
-- Run on subscriber server
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 restart
pg_ctl status

pg_dumpall -s > schemas.sql
psql -d doob -f schemas.sql

Any schema changes after this point will not be replicated.

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

To copy the initial table data, the replication role must have SELECT privileges on all of the tables (or be a superuser).

-- All Tables
CREATE PUBLICATION pub_upgrade FOR ALL TABLES;
-- Selected Tables
CREATE PUBLICATION pub_upgrade;
ALTER PUBLICATION pub_upgrade ADD TABLE customers;
ALTER PUBLICATION pub_upgrade ADD TABLE orders;

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;

-- On Publisher
\dRp
SELECT * FROM PG_REPLICATION_SLOTS;
SELECT * FROM PG_STAT_REPLICATION;
 
-- On Subscriber
\dRs

DDL changes will not be replicated to the subscriber.
May be possible to replicate via an Event Trigger?
https://www.enterprisedb.com/postgres-tutorials/how-use-event-triggers-postgresql
https://blog.dbi-services.com/can-i-do-it-with-postgresql-16-ddl-triggers/