Re: subscription broken after upgrade to pg11 - Mailing list pgsql-general

From Radoslav Nedyalkov
Subject Re: subscription broken after upgrade to pg11
Date
Msg-id CANhtRiaiMpQE1fWWkp_uwPs=DnDLrEHgT7fRGYdcaa=QVefs=g@mail.gmail.com
Whole thread Raw
In response to Re: subscription broken after upgrade to pg11  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general


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.
 

>
> Thanks,
> Radoslav
>


--
Adrian Klaver
adrian.klaver@aklaver.com

pgsql-general by date:

Previous
From: Hendrickx Pablo
Date:
Subject: Re: WSL (windows subsystem on linux) users will need to turn fsyncoff as of 11.2
Next
From: "Zwettler Markus (OIZ)"
Date:
Subject: AW: Postgres Enhancement Request