Dear hackers,
While testing pg_upgrade for [1], I found a bug related with logical replication
slots.
# Found bug
Status of logical replication slots are still "reserved", but they are not usable.
```
tmp=# SELECT slot_name, slot_type, restart_lsn, confirmed_flush_lsn, wal_status FROM pg_replication_slots;
slot_name | slot_type | restart_lsn | confirmed_flush_lsn | wal_status
------------+-----------+-------------+---------------------+------------
new_on_tmp | logical | 0/196C7B0 | 0/196C7E8 | reserved
(1 row)
tmp=# SELECT * FROM pg_logical_slot_get_changes('new_on_tmp', NULL, NULL);
ERROR: requested WAL segment pg_wal/000000010000000000000001 has already been removed
```
I did not check about physical slots, but it may also similar problem.
# Condition
This happens when logical slots exist on new cluster before doing pg_upgrade.
It happened for HEAD and REL_16_STABLE branches, but I think it would happen
all supported versions.
## How to reproduce
You can get same ERROR with below steps. Also I attached the script for
reproducing the bug,
1. do initdb for old and new cluster
2. create logical replication slots only on new cluster. Note that it must be
done aother database than "postgres".
3. do pg_upgrade.
4. boot new cluster and executed pg_logical_slot_get_changes()
# My analysis
The immediate cause is that pg_resetwal removes WALs required by logical
replication slots, it cannot be skipped.
Therefore, I think it is better not to allow upgrade when replication slots are
defined on the new cluster. I was not sure the case for physical replication,
so I want to hear your opinion.
I will create a patch if it is real problem. Any comments for that are very
welcome.
[1]:
https://www.postgresql.org/message-id/flat/TYAPR01MB58664C81887B3AF2EB6B16E3F5939@TYAPR01MB5866.jpnprd01.prod.outlook.com
Best Regards,
Hayato Kuroda
FUJITSU LIMITED