public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Ross <[email protected]>
To: [email protected]
Subject: Re: Postgres 12 update to new major version
Date: Fri, 5 Jun 2026 16:38:25 -0600
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CALeMo=Ef44kKRZYVOjjdC70zBv8vj+YRi06DXTVA_sJHsqFTtw@mail.gmail.com>
<[email protected]>
On 6/5/26 2:30 PM, Adrian Klaver wrote:
> On 6/5/26 5:11 AM, Boris Dovčík wrote:
>> Hello all,
>>
>> I have a question regarding the update of the production database
>> (multiple TB of data) from version 12 to higher major version. We would
>
> How many TB?
>
>> like to jump to 15 or 16. DB is installed on a Linux server with one
>> replica on another, it is not on cloud.
>
> What replication is being done now?
>
> Where are the servers relative to each other network distance wise?
>
>>
>> First question is should we perform updates one by one? 12 -> 13 ->
>> 14 etc. or is it viable to jump to 16 right away? (We have few
>> functions but usually its plain tables)
>>
>> Second we are contemplating options for the update since we are
>> limited with downtime. We could get 4 hours of downtime for the whole
>> update (either via pg_update or dumps). We are not sure how to
>> estimate the
>
> Did you mean pg_upgrade?
>
>> time for direct update or dumps. What would be the recommended way
>> for it?
>>
>> One option we see could be logical replication to the new major
>> version on a new machine, but we received feedback that in our setup
>> it might
>
> What was the feedback that indicated it was not advisable?
>
>> not be recommendable. Does it make sense to try logical replica? What
>> would be the downsides of it?
>>
>> Best regards,
>> Boris Dovčík
I've got just a little over 1 TB in prod with a logical replica in the
same network.
We upgraded from 10 to 16 in one jump by first setting up the logical
replica to run 16 and getting replication up and in sync.
When it came time to do the upgrade from 10 to 16 it was just a few
simple steps, and with minimal (less than 5 minutes downtime):
Stop the pgbouncers, which stopped all network traffic
Set the sequences on the 16 replica
Updated pgbouncer configs to point to the 16 server
Turned the pgbouncers back on
Setting the sequences was the most time consuming part but it was really
not long.
Logical replication did the trick for us.
I see that in 19 we won't even have to set the sequences because they
are also replicated! Hooray!
One of the advantages to the logical replication route is that we could
take advantage of testing a ton of our code against 16 on the replica
*before* we ever flipped the switch.
Jeff
P.S. I can't wait to hear how I did it all wrong or could have done it
better but this absolutely worked ;-)
view thread (5+ messages)
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], [email protected]
Subject: Re: Postgres 12 update to new major version
In-Reply-To: <[email protected]>
* 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