Re: 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(dot)klaver(at)aklaver(dot)com