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 tbl_1, tbl_2, tbl_3;
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.
select * from pg_catalog.pg_subscription
andselect * from pg_catalog.pg_subscription_rel
?select * from pg_catalog.pg_subscription_rel;
. srsubid | srrelid | srsubstate | srsublsn. ---------+---------+------------+---------- 61898 | 61853 | d |. 61898 | 61886 | d |.