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