Tables in the destination database are empty even after subscription is successful. I am extremely new to logical replication within Postgres. I am trying to replicate three tables from the source database to the destination database. These two databases are running on difference RDS instances
I followed the steps laid out on this link https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html
I have enabled replication across both the database instances by changing the parameter rds.logical_replication
to 1 (enabled). The parameter groups are in sync after the restart.
On the source database, I created a publication
create publication master_data_publication for table tbl1, tbl2, tbl3;
On the destination I created a table with the same name and same columns and then created a subscription
create subscription master_data_sub CONNECTION 'host=sourcedbhost.some-code.some-region.rds.amazonaws.com port=5432 dbname=sourcedb user=sourceuser password=userpassword' publication master_data_publication;
if i run select * from pg_catalog.pg_stat_subscription;
, it shows the following. With this, we would assume that the replication should be working.
But the tables are shown to be completely empty.
On Source
select count(*) from tbl_1; count ------- 332
On Destination
select count(*) from tbl_1; count ------- 0
I am now stuck on how to proceed from here.