Re: restarting logical replication after upgrading standby

On Mon, 7 Feb 2022 at 00:33, Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

>
>
> *# so finally, i truncate the table and re-sync :( and everything works,
> but i think i am missing something here. i am pretty sure we *
> *# can play with pg_replication_origin_advance
> or pg_replication_slot_advance to move the lsn to continue subscription
> without a sync, i'll have to spend some time to understand that*
> *# but for now, in summary, subscription breaks in upgrade. After an
> upgrade, we need to enable it back and possibly refresh publication
> (re-sync) from scratch.*
> *# but i am hoping i can be corrected by more experienced people here.*
>
>

I am sorry, I have messed up the mail flow. i'll probably use gist for code
dump or use an attachment in the future, and then stick to inlining.

now,
ok I just tried it again with *copy_data = false*, i was able to get the
changes *after* refresh was done, but I lost the changes before i
re-enabled subscription and refresh publication.

postgres=# table pg_stat_subscription;
-[ RECORD 1 ]---------+------
subid | 16403
subname | mysub
pid |
relid |
received_lsn |
last_msg_send_time |
last_msg_receipt_time |
latest_end_lsn |
latest_end_time |

postgres=# table pg_subscription;
-[ RECORD 1 ]---+----------
oid | 16403
subdbid | 16401
subname | mysub
subowner | 10
*subenabled | f*
subbinary | f
substream | f
subconninfo | port=8001
subslotname | mysub
subsynccommit | off
subpublications | {mypub}

postgres=# table pg_subscription_rel;
(0 rows)

postgres=# alter subscription mysub refresh publication with ( copy_data =
false );
ERROR: ALTER SUBSCRIPTION ... REFRESH is not allowed for disabled
subscriptions
postgres=# alter subscription mysub enable;
ALTER SUBSCRIPTION
postgres=# table pg_stat_subscription;
-[ RECORD 1 ]---------+---------------------------------
subid | 16403
subname | mysub
pid | 3007
relid |
received_lsn | 0/15E35F0
last_msg_send_time | 2022-02-07 00:53:09.760154+05:30
last_msg_receipt_time | 2022-02-07 00:53:09.760176+05:30
latest_end_lsn | 0/15E35F0
latest_end_time | 2022-02-07 00:53:09.760154+05:30

postgres=# table pg_subscription;
-[ RECORD 1 ]---+----------
oid | 16403
subdbid | 16401
subname | mysub
subowner | 10
*subenabled | t*
subbinary | f
substream | f
subconninfo | port=8001
subslotname | mysub
subsynccommit | off
subpublications | {mypub}

postgres=# table pg_subscription_rel;
(0 rows)

*postgres=# alter subscription mysub refresh publication with ( copy_data =
false );*
ALTER SUBSCRIPTION
postgres=# table pg_subscription_rel;
-[ RECORD 1 ]-----
srsubid | 16403
srrelid | 16384
*srsubstate | r*
srsublsn |

*# i inserted '3' at the publisher instance, but it did not get reflected
here.*
postgres=# table t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2

*# but when i insert a new row '4' at publisher i could get the changes
just fine, but still not '3'..*
*# so i am doing something wrong or not using the origin advance function
correctly, somewhere, but i hope this gives you some idea on how to check*

postgres=# table t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
-[ RECORD 3 ]
id | 4

at publisher
postgres=# table t;
-[ RECORD 1 ]
id | 1
-[ RECORD 2 ]
id | 2
*-[ RECORD 3 ]*
*id | 3 -- this is missing*
-[ RECORD 4 ]
id | 4

btw,
you can look at the inplace major version upgrade. although this is on the
top of patroni, but just in case.
spilo/postgres-appliance/major_upgrade at master · zalando/spilo
(github.com)
<https://github.com/zalando/spilo/tree/master/postgres-appliance/major_upgrade>
and
https://github.com/chobostar/ansible-playbook-upgrade-pg *(mostly for the
references)*

> --
> Thanks,
> Vijay
> LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>
>

--
Thanks,
Vijay
LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>