Thread: subscription broken after upgrade to pg11
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 ?
Thanks,
Radoslav
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.
>
> Thanks,
> Radoslav
>
--
Adrian Klaver
adrian.klaver@aklaver.com