postgres streaming replication

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

Postgres - logical replication

Master DB:

1. Create a publication

CREATE PUBLICATION alltables FOR ALL TABLES;

2. Create a replication user

create role replication_user with login password 'some-pass' replication; 
--GRANT whatever permissions are required
grant usage on schema some_schema to replication_user;
grant select on all tables in schema some_schema to replication_user;

Note: with logical replication, user does not seem to have to have the replication rights; this is required with std streaming replication

3. Modify the postgresql.conf to allow connections to the machine via its ip

listen_addresses = 'localhost,server_ip'

and set up a proper WAL lvl

wal_level = logical			# minimal, replica, or logical

4. Modify pg_hba.conf to allow connections from specified slaves

host    all     replication_user         slave_ip/0                 md5

5. Dump db structure

pg_dump.exe --host localhost --port 5432 --username "some-user-name" --no-password  --format plain --schema-only --verbose --file "C:\temp\db_schema.sql" --schema "schema-name" "db-name"

6. Review publication and replication slots (when a subscription is created)

select * from pg_publication;
select * from pg_replication_slots; --when subscription is created
--when need to drop replication slot
select pg_drop_replication_slot('slot_name')

7. Check replication status

select * from pg_publication;
select * from pg_stat_replication;


Replica DB:

1. recreate db structure - this is required in order to make the subscription work

createdb --host localhost --port 5432 --username user-name  db-name
psql --host localhost --port 5432 --username user-name -w --dbname db-name --command "create extension if not exists postgis"
psql --host localhost --port 5434 --username postgres -w --dbname db-name --file "c:\temp\db_schema.sql"

Note: installing postgis is not required of course

2. Create a subscription

create subscription my_sub 
connection 'host=master-ip dbname=db-to-replicate user=replication_user password=some-password port=5432' 
publication alltables;

3. Review subscription

select * from pg_subscription;

4. Check replication status

select * from pg_stat_subscription;

More info here: https://www.postgresql.org/docs/10/logical-replication.html