Adding more detail to pg_upgrade documentation

Hi,

We are upgrading from Postgres 11 to 13. During upgrade we found that the
physical slots on the old cluster are not copied to the new cluster.

This information is not mentioned in the documentation -
https://www.postgresql.org/docs/13/pgupgrade.html

Just thought it would be good to have this detail

Thanks and Regards,
Nikhil

Hi Nikhil,

From the pgupgrade docs:

9. Prepare for standby server upgrades

If you are upgrading standby servers using methods outlined in section
Step 11, verify that the old standby servers are caught up by running
pg_controldata against the old primary and standby clusters. Verify
that the “Latest checkpoint location” values match in all clusters.
(There will be a mismatch if old standby servers were shut down before
the old primary or if the old standby servers are still running.)
Also, make sure wal_level is not set to minimal in the postgresql.conf
file on the new primary cluster.

(source: https://www.postgresql.org/docs/devel/pgupgrade.html)

I'm a new contributor so please forgive me if I'm on the wrong track,
but if you follow this step, won't you also be ensuring that replication
slots do not need to be migrated, since you've just ensured that standby
clusters are in sync with the primary cluster? Please let me know if I'm
missing anything!

Thank You,
Jack DeVries

Hi Jack,

I followed the same steps multiple times but each time, slot had to be
recreated.

The slots under directory pg_replslot are not created in the new cluster
during pg_upgrade.

Thanks and Regards,
Nikhil

On Sat, 4 Jun 2022 at 1:48 AM, Jack DeVries <jdevries3133@gmail.com> wrote:

On Sat, Jun 4, 2022 at 03:08:58PM +0530, Nikhil Shetty wrote:

Hi Jack,

I followed the same steps multiple times but each time, slot had to be
recreated.

The slots under directory pg_replslot are not created in the new cluster during
pg_upgrade.

Thanks and Regards,
Nikhil

On Sat, 4 Jun 2022 at 1:48 AM, Jack DeVries <jdevries3133@gmail.com> wrote:

Hi Nikhil,

From the pgupgrade docs:

9. Prepare for standby server upgrades

If you are upgrading standby servers using methods outlined in section
Step 11, verify that the old standby servers are caught up by running
pg_controldata against the old primary and standby clusters. Verify
that the “Latest checkpoint location” values match in all clusters.
(There will be a mismatch if old standby servers were shut down before
the old primary or if the old standby servers are still running.)
Also, make sure wal_level is not set to minimal in the postgresql.conf
file on the new primary cluster.

(source: https://www.postgresql.org/docs/devel/pgupgrade.html)

I'm a new contributor so please forgive me if I'm on the wrong track,
but if you follow this step, won't you also be ensuring that replication
slots do not need to be migrated, since you've just ensured that standby
clusters are in sync with the primary cluster? Please let me know if I'm
missing anything!

I have written the attached patch to document this.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

On Sat, Jul 9, 2022 at 10:38:30PM -0400, Bruce Momjian wrote:

(source: https://www.postgresql.org/docs/devel/pgupgrade.html)

I'm a new contributor so please forgive me if I'm on the wrong track,
but if you follow this step, won't you also be ensuring that replication
slots do not need to be migrated, since you've just ensured that standby
clusters are in sync with the primary cluster? Please let me know if I'm
missing anything!

I have written the attached patch to document this.

Patch applied to all supported versions.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson

Thank you Bruce !

On Fri, 15 Jul 2022 at 2:04 AM, Bruce Momjian <bruce@momjian.us> wrote:

On Sat, May 28, 2022 at 12:11:31AM +0530, Nikhil Shetty wrote:

Hi,

We are upgrading from Postgres 11 to 13. During upgrade we found that the
physical slots on the old cluster are not copied to the new cluster.

This information is not mentioned in the documentation - https://
www.postgresql.org/docs/13/pgupgrade.html

Just thought it would be good to have this detail

All releases after 2022-07-14 have this mentioned:

commit 47ae6948f0
Author: Bruce Momjian <bruce@momjian.us>
Date: Thu Jul 14 16:34:30 2022 -0400

pg_upgrade doc: mention that replication slots must be recreated

Reported-by: Nikhil Shetty

Discussion: /messages/by-id/CAFpL5Vxastip0Jei-K-=7cKXTg=5sahSe5g=om=x68NOX8+PUA@mail.gmail.com

Backpatch-through: 10

So, these releases:

https://www.postgresql.org/about/news/postgresql-145-138-1212-1117-1022-and-15-beta-3-released-2496/
PostgreSQL 14.5, 13.8, 12.12, 11.17, 10.22

See here:

https://www.postgresql.org/docs/15/pgupgrade.html
Section 11.7
--> Replication slots are not copied and must be recreated.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Indecision is a decision. Inaction is an action. Mark Batterson