From: | "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com> |
---|---|
To: | 'Amit Kapila' <amit(dot)kapila16(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Julien Rouhaud <rjuju123(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com> |
Subject: | RE: [PoC] pg_upgrade: allow to upgrade publisher node |
Date: | 2023-08-03 09:28:33 |
Message-ID: | TYAPR01MB5866180816D9ABC67F1A1091F508A@TYAPR01MB5866.jpnprd01.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Dear Amit,
> I see your point related to WALAVAIL_REMOVED status of the slot but
> did you test the scenario I have explained in my comment? Basically, I
> want to know whether it can impact the user in some way. So, please
> check whether the corresponding subscriptions will be allowed to drop.
> You can test it both before and after the upgrade.
Yeah, this is a real issue. I have tested and confirmed the expected things.
Even if the status of the slot is 'lost', it may be needed for dropping
subscriptions properly.
* before upgrading, the subscription which refers the lost slot could be dropped
* after upgrading, the subscription could not be dropped as-is.
users must ALTER SUBSCRIPTION sub SET (slot_name = NONE);
Followings are the stepped what I did:
## Setup
1. constructed a logical replication system
2. disabled the subscriber once
3. consumed many WALs so that the status of slot became 'lost'
```
publisher=# SELECT slot_name, wal_status FROM pg_replication_slots ;
slot_name | wal_status
-----------+------------
sub | lost
(1 row)
```
# testcase a - try to drop sub. before upgrading
a-1. enabled the subscriber again.
At that time following messages are shown on subscriber log:
```
ERROR: could not start WAL streaming: ERROR: can no longer get changes from replication slot "sub"
DETAIL: This slot has been invalidated because it exceeded the maximum reserved size.
```
a-2. did DROP SUBSCRIPTION ...
a-3. succeeded.
```
subscriber=# DROP SUBSCRIPTION sub;
NOTICE: dropped replication slot "sub" on publisher
DROP SUBSCRIPTION
```
# testcase b - try to drop sub. after upgrading
b-1. did pg_upgrade command
b-2. enabled the subscriber. From that point an apply worker connected to new node...
b-3. did DROP SUBSCRIPTION ...
b-4. failed with the message:
```
subscriber=# DROP SUBSCRIPTION sub;
ERROR: could not drop replication slot "sub" on publisher: ERROR: replication slot "sub" does not exist
```
The workaround was to disassociate the slot, which was written in the document.
```
subscriber =# ALTER SUBSCRIPTION sub DISABLE;
ALTER SUBSCRIPTION
subscriber =# ALTER SUBSCRIPTION sub SET (slot_name = NONE);
ALTER SUBSCRIPTION
subscriber =# DROP SUBSCRIPTION sub;
DROP SUBSCRIPTION
```
PSA the script for emulating above tests.
Best Regards,
Hayato Kuroda
FUJITSU LIMITED
Attachment | Content-Type | Size |
---|---|---|
test_0803.sh | application/octet-stream | 2.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Matthias van de Meent | 2023-08-03 09:31:01 | Re: Adding a pg_servername() function |
Previous Message | Jian Guo | 2023-08-03 09:21:39 | Re: On disable_cost |