On 3/19/19 9:35 AM, Radoslav Nedyalkov wrote:
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 ?
https://www.postgresql.org/docs/11/sql-altersubscription.html
REFRESH PUBLICATION
Fetch missing table information from publisher. This will start
replication of tables that were added to the subscribed-to publications
since the last invocation of REFRESH PUBLICATION or since CREATE
SUBSCRIPTION.
refresh_option specifies additional options for the refresh
operation. The supported options are:
copy_data (boolean)
Specifies whether the existing data in the publications that
are being subscribed to should be copied once the replication starts.
The default is true.
Try with:
copy_data=true
Thanks,
Radoslav
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
