Hi All,
We're testing upgrade from postgres 10.6 to postgres 11.2 with pg_upgrade.
Before stopping pg10 we disabled subscription.
After upgrade and launching pg11, the existing logical replication subscription is there and disabled.
stest=# \dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
------+----------+---------+-------------+--------------------+-----------------------------------------
sub1 | postgres | f | {pub2} | off | host=10.2.5.8 dbname=test user=postgres
(1 row)
However after enabling it, the target table does not get updates,
although the subscription looks okay according to the status below.
stest=# alter subscription sub1 enable;
ALTER SUBSCRIPTION
# no updates here
stest=# \dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
------+----------+---------+-------------+--------------------+-----------------------------------------
sub1 | postgres | t | {pub2} | off | host=10.2.5.8 dbname=test user=postgres
stest=# select * from pg_subscription;
subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+---------+----------+------------+-----------------------------------------+-------------+---------------+-----------------
16402 | sub1 | 10 | t | host=10.2.5.8 dbname=test user=postgres | sub1 | off | {pub2}
stest=# select * from pg_stat_subscription;
subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
-------+---------+------+-------+--------------+--------------------+-------------------------------+----------------+-------------------------------
16413 | sub1 | 2810 | | 0/145C3400 | | 2019-03-19 16:23:23.650073+00 | 0/145C3320 | 2019-03-19 16:23:23.446651+00
Issuing a refresh
stest=# alter subscription sub1 refresh publication with (copy_data = false);
ALTER SUBSCRIPTION
resumes updates , but with a gap in data. Everything up-to to the refresh statement is missing in the target.
What we're doing wrong ?
Thanks,
Radoslav
