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