master db
1. setup pg_hba.conf so remote connection is allowed (requires db restart)
add:
host replication replication_user S.OM.E.IP/0 md5
host replication replication_user S.OM.E.IP/0 md5
notice the name of the db - replication:
The value replication specifies that the record matches if a physical replication connection is requested (note that replication connections do not specify any particular database). More in postgres docs.
2. configure firewall to allow incoming traffic from the replica server for each port dbs are listening on; add the replica ip to the white list
3. adjust postgresql.conf (requires db restart)
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 10
hot_standby = on
primary_conninfo = 'host=S.OM.E.IP port=some_port user=replication_user password=some_pass'
primary_slot_name = 'physical_slot_1'
4. Create replication role
create role replication_user with login password 'some_pass' replication;
5. Create replication slot
SELECT * FROM pg_create_physical_replication_slot('physical_slot_1');
replica db
1. backup and restore dbs on replica server (requires the db data folders to be empty, as all the stuff should be brought over)
pg_basebackup --host=S.OM.E.IP --port=some_port --username=replication_user --wal-method=fetch --pgdata=destination_dir
create standby.signal file in the data dir of each database
2. start replica; logs should show the replication is working:
2020-04-17 20:16:18.224 CEST [9276] LOG: entering standby mode
2020-04-17 20:16:18.290 CEST [9276] LOG: redo starts at 0/2000028
2020-04-17 20:16:18.323 CEST [9276] LOG: consistent recovery state reached at 0/2000138
2020-04-17 20:16:18.326 CEST [19868] LOG: database system is ready to accept read only connections
2020-04-17 20:16:18.416 CEST [15924] LOG: started streaming WAL from primary at 0/3000000 on timeline 1