Logical replication
It uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node.
Logical replication of a table typically starts with taking a snapshot of the data on the publisher database and copying that to the subscriber. Once that is done, the changes on the publisher are sent to the subscriber as they occur in real-time. Logical replication allows us to replicate changes from an older version PostgreSQL to a later version.
- Source DB- AWS RDS /Azure Postgres -------> Publisher Node
- Destination DB - BA Cluster -------> Subscriber Node
Prerequisites on AWS RDS :
1) Modify the DB cluster parameter group , Set the rds.logical_replication static parameter to 1. This will change wal_level to logical from replica , this requires to Reboot the DB instance.
2) Make require changes in Networking for connectivity .
3) If a new user is created for logical replication , then grant required privileges to the new user .
(rds_superuser permissions)
Prerequisites on Azure Postgres :
1) Modify Server parameter wal_level to logical for your Azure postgres DB
2) Make require changes in Networking for connectivity .
3) Grant Replication role to your user
postgres=> alter user postgres with replication ;
ALTER ROLE
Prerequisites on BA Cluster :
1) User (edb_admin) on your destination DB should have superuser privileges
Below is the example of logical replication from AWS RDS TO BA cluster :
On source - AWS RDS
bdrtest=> CREATE TABLE Test (a int PRIMARY KEY);
CREATE TABLE
bdrtest=> INSERT INTO LogicalReplicationTest VALUES (generate_series(1,10000));
INSERT 0 10000
bdrtest=> CREATE PUBLICATION testpub FOR TABLE Test;
CREATE PUBLICATION
On Destination -BA Cluster
* need to create structure before creating subscription for the particular table which you want to replicate
bdrdb=> CREATE TABLE Test (a int PRIMARY KEY);
CREATE TABLE
bdrdb=> CREATE SUBSCRIPTION testsub CONNECTION
'host=mptest.postgres.database.azure.com port=5432 dbname=postgres user=postgres password=xxxx' PUBLICATION testpub;
NOTICE: created replication slot "testsub" on publisher
CREATE SUBSCRIPTION
** host details will be of your publisher / source node
** you can now verify the count after creating subscriber node , data must be replicated
bdrdb=> SELECT count(*) FROM LogicalReplicationTest;
-[ RECORD 1 ]
count | 10000
* You can also add new table to publisher node by using alter command
-ALTER PUBLICATION <pub name> ADD TABLE <new table name>
* After adding new table to publisher node , need to refresh publisher node on subscriber
ALTER SUBSCRIPTION <subsc name> REFRESH PUBLICATION;
Monitoring Commands :
On Publication / Source node
1)select * from pg_catalog.pg_publication;
2)select * from pg_catalog.pg_publication_tables;
3)select pg_current_wal_lsn();
4)select * from pg_replication_slots;
5)select * from pg_stat_replication;
On Subscription / Destination node
1)select received_lsn, latest_end_lsn from pg_catalog.pg_stat_subscription;
2)select * from pg_catalog.pg_stat_subscription;
Known issues
Issue 1 - For AWS RDS
"ERROR: could not create replication slot "rds_migration": ERROR: must be superuser or replication role to use logical replication slots in the 'rds' namespace"
It may be caused by the subscription name having rds as the prefix. Change the name of the subscription will resolve the error.