Thread: Safest pgupgrade jump distance

Hi, I was wondering what is the safest pg_upgrade version upgrade distance going from 9.6 version. Do I need to go version by version or I can go from 9.6 to 15? We have a very huge database(TBs) with one replication server, so we will first run the pgupgrade on the main server and then rsync to a standby replica. I'm not sure whether it's safe to do it from 9.6 to 15 at once, I have tested the process on 9,6 to 10 yet. Would that be a wise approach to such an upgrade of the db?

On 2024-02-12 20:07, Dávid Suchan wrote:
> Hi, I was wondering what is the safest pg_upgrade version upgrade 
> distance
> going from 9.6 version. Do I need to go version by version or I can go 
> from
> 9.6 to 15? We have a very huge database(TBs) with one replication 
> server,
> so we will first run the pgupgrade on the main server and then rsync to 
> a
> standby replica. I'm not sure whether it's safe to do it from 9.6 to 15 
> at
> once, I have tested the process on 9,6 to 10 yet. Would that be a wise
> approach to such an upgrade of the db?
> Also, when upgrading a very big database with replication where none of 
> the
> data can be allowed to be lost, is the pgupgrade into rsync approach 
> the
> best one? Thanks.

pgupgrade from 9.6 directly to 16 should work fine.  That's part of the
test suite for one of my side projects (pgautoupgrade in Docker).

You'll probably want to use the "--link" option too, so it uses the 
existing
data files rather than recreating them.  It's *much* faster. :)

I'm not sure how the rsync of things will figure into it though.  If 
you're
just rsyncing files when the database is turned off, it should be 
straight
forward.

As always though, make sure you have a backup (that's known to work) 
before
you try it all out though. :)

+ Justin

Am Mon, Feb 12, 2024 at 09:31:50AM -0500 schrieb Ron Johnson:

> https://www.postgresql.org/docs/16/pgupgrade.html
> "pg_upgrade supports upgrades from 9.2.X and later to the current major
> release of PostgreSQL, including snapshot and beta releases."

Just to be sure: it should be stressed that the binaries of
the later version (16 in OPs case) should be used to run the
upgrade, right ?

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> Am Mon, Feb 12, 2024 at 09:31:50AM -0500 schrieb Ron Johnson:
>> https://www.postgresql.org/docs/16/pgupgrade.html
>> "pg_upgrade supports upgrades from 9.2.X and later to the current major
>> release of PostgreSQL, including snapshot and beta releases."

> Just to be sure: it should be stressed that the binaries of
> the later version (16 in OPs case) should be used to run the
> upgrade, right ?

Right.  Another thing worth pointing out here is that with such
a large jump distance, you'd better test application compatibility
before making the change on your production database.  There are
likely to be a few gotchas.

            regards, tom lane