Re: subscription broken after upgrade to pg11

On Tue, Mar 19, 2019 at 10:37 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.

>
> >
> > Thanks,
> > Radoslav
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>