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/tyapr01mb58664c81887b3af2eb6b16e3f5...@tyapr01mb5866.jpnprd01.prod.outlook.com Best Regards, Hayato Kuroda FUJITSU LIMITED
slots_error.sh
Description: slots_error.sh