Logical Replication
Revision as of 17:19, 11 May 2022 by Ajay (talk | contribs) (Created page with "=== On the host server === Modify postgresql.conf to be ready for logical replication wal_level=logical max_wal_senders=16 max_sync_workers_per_subscription=16 max_logical_replication_workers=20 Setup a replication user CREATE ROLE repl WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS CONNECTION LIMIT -1 PASSWORD 'SOME SECURE PASSWORD'; Then, on all databases you want to sync, grant select per...")
On the host server
Modify postgresql.conf to be ready for logical replication
wal_level=logical max_wal_senders=16 max_sync_workers_per_subscription=16 max_logical_replication_workers=20
Setup a replication user
CREATE ROLE repl WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS CONNECTION LIMIT -1 PASSWORD 'SOME SECURE PASSWORD';
Then, on all databases you want to sync, grant select permissions
grant select on all tables in schema public
Next, create a publication on each database
CREATE PUBLICATION publication_name FOR ALL TABLES;
On the target server
Modify postgresql config to speed up replication
max_wal_senders=16 max_sync_workers_per_subscription=16 max_logical_replication_workers=20
Ensure schema has been fully copied already, but no data has been copied
Then, create a subscription on each database
CREATE SUBSCRIPTION publication_name CONNECTION 'host=<IP Addres> port=<PORT> user=repl password=<PASSWORD> dbname=<DB NAME>' PUBLICATION publication_name;
Make sure to check logs when doing this to ensure there aren't permissions errors.