subscription broken after upgrade to pg11

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@aklaver.com

On Tue, Mar 19, 2019 at 10:37 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

I don't think focus is on REFRESH here. It is existing subscription

that should resume and apply changes without discrepancies in the flow.
First I tried simply to re-create subscription by retaining replication
slot on the source. However the slot sent already applied changes.
Obviously exact LSN was lost somehow during upgrade. (how? should it ?)
So the solution:

On the target before upgrade - disable subscription and get remote_lsn.

stest=# alter subscription sub1 disable;
ALTER SUBSCRIPTION
stest=# select * from pg_replication_origin_status ;
local_id | external_id | remote_lsn | local_lsn
----------+-------------+------------+-----------
1 | pg_16473 | *0/146E41E0* | 0/2ABDC48
(1 row)

Upgrade here.

On the target after upgrade execute:

stest=# alter subscription sub1 set (slot_name = NONE);
ALTER SUBSCRIPTION
stest=# drop subscription sub1 ;
DROP SUBSCRIPTION
stest=# create subscription sub1 CONNECTION 'host=10.2.5.8 dbname=test
user=postgres' PUBLICATION pub2 with (slot_name = sub1, create_slot=false,
enabled=false, copy_data=false);
CREATE SUBSCRIPTION
stest=# select oid,* from pg_subscription;
oid | subdbid | subname | subowner | subenabled |
subconninfo | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+-----------------------------------------+-------------+---------------+-----------------
16474 | 16402 | sub1 | 10 | f | host=10.2.5.8
dbname=test user=postgres | sub1 | off | {pub2}
(1 row)
stest=# select pg_replication_origin_advance('pg_16474', '*0/146E41E0*');
stest=# select * from pg_replication_origin_status;
local_id | external_id | remote_lsn | local_lsn
----------+-------------+------------+-----------
1 | pg_16474 | *0/146E41E0* | 0/0
(1 row)
stest=# alter subscription sub1 enable;
ALTER SUBSCRIPTION

After that the stream resumed from the right point.