public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
Date: Thu, 23 Oct 2025 13:56:58 -0400
Message-ID: <CANzqJaCiDH16CRjMPAY6eOVcrWtc91RWdZcZOyanapNDpyeOKw@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmK9Fjb5YngH4BSb8Lmk3qh10nw=1M3xdcLatr9-U-f-Ug@mail.gmail.com>
References: <CAJ4rSwspyYBppQS701C=BVFX2yrP1JErgDCZXbC15BQh2zAS1Q@mail.gmail.com>
<CAKAnmmK47O_rYDB4iPe=K3QiqzJcCnnxCoYs_40Q1YWb=x0nDg@mail.gmail.com>
<CAJ4rSwvsRJhwfuem0vsws1WwRZPN8Ju2Nw0e0Oc2j7n=MXUhMg@mail.gmail.com>
<CAKAnmmK9Fjb5YngH4BSb8Lmk3qh10nw=1M3xdcLatr9-U-f-Ug@mail.gmail.com>
On Thu, Oct 23, 2025 at 11:21 AM Greg Sabino Mullane <[email protected]>
wrote:
>
>> -
>>
>> *Acceptable downtime:* ~1 day
>> -
>>
>> *Logical replication:* Not feasible due to the number of schemas,
>> tables, and overall data volume
>>
>> I'm not sure why this is not feasible. Can you expand on this?
>
> * For a *15 TB database* with roughly *1 day downtime*, what would be the
>> most reliable approach to migrate from *RHEL 7 → RHEL 9* while avoiding
>> collation/index corruption issues?
>
>
> 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.
>
Last year. I did a dump/restore of a 4.3TB (inclusive of indices; heavy on
poorly-compressible BYTEA) database from RHEL6 + 9.6.24 to RHEL 8 +
14.latest. It took just under 11 hours.
Gzip Level = 1
Remote database size: 4307406 MB
RemoteThreads: 16
LocalThreads: 24
SharedBuffs: 32 GB
MaintWorkMem: 3 GB
CheckPoint: 30 min
MaxWalSize: 36 GB
WalBuffs: 128 MB
Both systems were SAN-attached ESX VMs on the same virtual network
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Index corruption issue after migration from RHEL 7 to RHEL 9 (PostgreSQL 11 streaming replication)
In-Reply-To: <CANzqJaCiDH16CRjMPAY6eOVcrWtc91RWdZcZOyanapNDpyeOKw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox