Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication) - Mailing list pgsql-general

From Francisco Olarte
Subject Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
Date
Msg-id CA+bJJbwSZg6fiQ78N-_2NwRBbp=6e5WjnSX9SPa_DOxnaU63Vg@mail.gmail.com
Whole thread Raw
In response to Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)  (Greg Sabino Mullane <htamfids@gmail.com>)
List pgsql-general

On Thu, 23 Oct 2025 at 17:21, Greg Sabino Mullane <htamfids@gmail.com> wrote

pg_dump is the most reliable, and the slowest. Keep in mind that only the actual data needs to move over (not the indexes, which get rebuilt after the data is loaded). You could also mix-n-match pg_logical and pg_dump if you have a few tables that are super large. Whether either approach fits in your 24 hour window is hard to say without you running some tests.

Long time ago I had a similar problem and did a "running with scissors" restore. This means:

1.- Prepare normal configuration, test, etc for the new version.
2.- Prepare a restore configuration, with fsync=off, wallevel=minimal, whatever option gives you any speed advantage.

As the target was empty, if restore failed we could just clean and restart.

3.- Dump, boot with the restore configuration, restore, clean shutdown, switch to production configuration, boot again and follow on.

Time has passed and I lost my notes, but I remember the restore was much faster than doing it with the normal production configuration. Given current machine speeds, it maybe doable.


Francisco Olarte.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
Next
From: David Rowley
Date:
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)