Logical replication was introduced in PostgreSQL 10. It offers better flexibility than stream replication in a number of ways:
Publisher and subscriber can be on different major versions
Replication can be done at table level, sharing a subset of the whole db
In my implementation below, I setup a Google cloud VM to host PostgreSQL and then set up a subscriber on it.
On main DB (publisher):
a. In /etc/postgresql/12/main/postgresql.conf
change wal_level=logical
b. in /etc/postgresql/12/main/pg_hba.conf, add below
hostssl ensures the replication is run over ssl
hostssl replication repuser subscriber_ip/32 md5
c. Create a replication user repuser, with REPLICATION and LOGIN attribute AND SELECT on tables and USAGE on schema where those tables are sitting in
d. sudo service postgresql restart to reload config
e. create publication logical_all_tables for all tables (this command create a publication for all tables, an easy command but you’ll have to re-create it and sync replication if you need to remove any table from it later on so it’s probably not flexible for production use)
On replication DB(subscriber)
a. Need to change wal_level=logical in postgresql.conf
b. CREATE SUBSCRIPTION logical_all_tables CONNECTION 'dbname=xxx host=xxx.xxx.xxx.xxx port=7707 user=repuser password=xxxxx' PUBLICATION logical_all_tables
*I found special characters such as . or \ cannot be used, they may need to be escaped.
Done!
Useful commands
DROP PUBLICATION logical_all_tables
select * from pg_publication_tables
select * from pg_stat_replication;
ALTER SUBSCRIPTION logical_all_tables_for_tss REFRESH PUBLICATION;
drop SUBSCRIPTION logical_all_tables
select current_timestamp at time zone 'australia/sydney'
Other thoughts
Replication supports SSL but the default SSL may not be strong enough, you can follow this post to enable CA certificate yourself.
https://blog.raveland.org/post/self_signed/
scram-sha-256 can be used instead of md5 in pg_hba.conf; Before it can be used, password_encryption = scram-sha-256 needs to be enabled in postgresql.conf and followed by service postgresql restart. Scram is a newer, more secure way of encrypting login data.
Before replication starts, slave database should only contain empty tables without data; this can be done in pgAdmin -> restore schema only, if you cannot remember the pg_restore syntax.
To further secure your Slave database (in my case it’s on Google Cloud Platform), firewall rules on egress and ingress can be set to only allow PG’s port. This default port 5432 can be changed to an arbitrary number(e.g 7707 in the above example), so to disclose as minimal information as possible.(So that a hacker cannot even guess there is a PG instance behind the firewall).
pg_hba.conf on your master db can be configured to allow subscriber’s IP address(instead of a network) only.
Note, postgresql.conf on your master can be configured to listen_addresses='*', this only means which IPs the server will answer on, not which IPs the server will permit connections to authenticate from. * here means the server will accept incoming connections on any ip assigned to an interface on the pg server host; it’s pg_hba.conf’s role to control which IPs the server will accept logins from, for what dbs and users.