On Mon, 7 Feb 2022 at 00:33, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> 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.
and
https://github.com/chobostar/ansible-playbook-upgrade-pg (mostly for the references)
Thanks,
Vijay
