0

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. enter image description here 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.

7
  • What versions are you using?
    – jjanes
    Apr 1 '21 at 15:29
  • Have you looked in the log files? "tbl_1" is not the same thing as "tbl1". Is that a problem in your question, or in the real situation?
    – jjanes
    Apr 1 '21 at 15:33
  • @jjanes I am using Postgres version 12.3. The tbl1/tbl_1 was a typo on my end. while posting this message. Apr 2 '21 at 4:54
  • What is in select * from pg_catalog.pg_subscription and select * from pg_catalog.pg_subscription_rel?
    – jjanes
    Apr 4 '21 at 16:27
  • select * from pg_catalog.pg_subscription_rel;. srsubid | srrelid | srsubstate | srsublsn. ---------+---------+------------+---------- 61898 | 61853 | d |. 61898 | 61886 | d |. Apr 4 '21 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.