Re: Logical replication stuck and no data being copied

After further investigation, I discovered the following.

On aurora ( the subscriber).
select now()::timestamp(0),a.subname,b.srsubstate,count(*) from
pg_subscription_rel b, pg_subscription a where  b.srsubid=a.oid group by
1,2,3 order by 2,3;

         now         |         subname         | srsubstate | count 
---------------------+-------------------------+------------+-------
 2021-02-18 08:53:31 | sub_prodza_big          | r          |    11
 2021-02-18 08:53:31 | sub_prodza_default      | r          |   108
 2021-02-18 08:53:31 | sub_prodza_trade        | r          |     1
 2021-02-18 08:53:31 | sub_prodza_tradearchive | i          |     1


For the table where the snapshot isn't being taken and the state remains in
initialize. I drop the other subscriptions and publications.

Then I get the following:
prodza=# select * from pg_replication_slots ;
-[ RECORD 1 ]-------+---------------------------------------------
slot_name           | test_slot
plugin              | test_decoding
slot_type           | logical
datoid              | 221550750
database            | prodza
temporary           | f
active              | f
active_pid          | 
xmin                | 
catalog_xmin        | 798588827
restart_lsn         | 7A7/520BAD48
confirmed_flush_lsn | 7A7/520BAD80
-[ RECORD 2 ]-------+---------------------------------------------
slot_name           | sub_prodza_tradearchive_7775427_sync_3552943
plugin              | pgoutput
slot_type           | logical
datoid              | 221550750
database            | prodza
temporary           | t
active              | t
active_pid          | 25001
xmin                | 
catalog_xmin        | 799048174
restart_lsn         | 7AC/6003D048
confirmed_flush_lsn | 7AC/6003D080
-[ RECORD 3 ]-------+---------------------------------------------
slot_name           | sub_prodza_tradearchive
plugin              | pgoutput
slot_type           | logical
datoid              | 221550750
database            | prodza
temporary           | f
active              | t
active_pid          | 24835
xmin                | 
catalog_xmin        | 799048182
restart_lsn         | 7AC/60058488
confirmed_flush_lsn | 7AC/600584C0

Now it seems that a snapshot is being taken for the table, but when I create
another publication and subscription, both are in state 'd'.
on aurora (subscriber)

prodza=> select now()::timestamp(0),a.subname,b.srsubstate,count(*) from
pg_subscription_rel b, pg_subscription a where  b.srsubid=a.oid group by
1,2,3 order by 2,3;

         now         |         subname         | srsubstate | count 
---------------------+-------------------------+------------+-------
 2021-02-18 09:38:24 | sub_prodza_trade        | d          |     1
 2021-02-18 09:38:24 | sub_prodza_tradearchive | d          |     1

Which is not correct, since the table trade's data is already synced it
should be in srsubstate = 'r'.
Does this mean that the tradearchive has to complete before data will be
published to the subscriber for the table trade?

Also, why do the replication slots share the same restart_lsn and
confirmed_flush lsn?.

-[ RECORD 1 ]-------+---------------------------------------------
slot_name           | sub_prodza_trade
plugin              | pgoutput
slot_type           | logical
datoid              | 221550750
database            | prodza
temporary           | f
active              | t
active_pid          | 25381
xmin                | 
catalog_xmin        | 799051115
restart_lsn         | 7AC/60CD9F10
confirmed_flush_lsn | 7AC/60CD9F80
-[ RECORD 2 ]-------+---------------------------------------------
slot_name           | sub_prodza_tradearchive_7775427_sync_3552943
plugin              | pgoutput
slot_type           | logical
datoid              | 221550750
database            | prodza
temporary           | t
active              | t
active_pid          | 25001
xmin                | 
catalog_xmin        | 799048174
restart_lsn         | 7AC/6003D048
confirmed_flush_lsn | 7AC/6003D080
-[ RECORD 3 ]-------+---------------------------------------------
slot_name           | sub_prodza_tradearchive
plugin              | pgoutput
slot_type           | logical
datoid              | 221550750
database            | prodza
temporary           | f
active              | t
active_pid          | 24835
xmin                | 
catalog_xmin        | 799051115
restart_lsn         | 7AC/60CD9F10
confirmed_flush_lsn | 7AC/60CD9F80








--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html