pg_upgrade bug: pg_upgrade successes even if the slots are defined, but they becomes unusable

From: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: 'PostgreSQL Hackers' <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: pg_upgrade bug: pg_upgrade successes even if the slots are defined, but they becomes unusable
Date: 2023-08-30 10:57:33
Message-ID: TYAPR01MB5866D277F6BEDEA4223B3559F5E6A@TYAPR01MB5866.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)TYAPR01MB5866(dot)jpnprd01(dot)prod(dot)outlook(dot)com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED

Attachment Content-Type Size
slots_error.sh application/octet-stream 1.2 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Dilip Kumar 2023-08-30 11:21:19 Re: New WAL record to detect the checkpoint redo location
Previous Message Ranier Vilela 2023-08-30 10:40:10 Re: Avoid a possible overflow (src/backend/utils/sort/logtape.c)