Re: restarting logical replication after upgrading standby

Поиск
Список
Период
Сортировка
От Vijaykumar Jain
Тема Re: restarting logical replication after upgrading standby
Дата
Msg-id CAM+6J97gLW6uuomRTL6e6+UOSevOsQHUvSJeL+HtofRm5ekL=A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: restarting logical replication after upgrading standby  (Vijaykumar Jain <vijaykumarjain.github@gmail.com>)
Список pgsql-admin
Дерево обсуждения
On Mon, 7 Feb 2022 at 00:33, Vijaykumar Jain <vijaykumarjain.github@gmail.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.
and

 


--

В списке pgsql-admin по дате отправления:

Предыдущее
От: Vijaykumar Jain
Дата:
Сообщение: Re: restarting logical replication after upgrading standby
Следующее
От: Goti
Дата:
Сообщение: Re: Does aurora PostgreSQL use wal_buffers?