pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedSafest pgupgrade jump distance
7+ messages / 7 participants
[nested] [flat]
* Safest pgupgrade jump distance
@ 2024-02-12 10:07 Dávid Suchan <david.suchan.ds@gmail.com>
2024-02-12 10:15 ` Re: Safest pgupgrade jump distance Justin Clift <justin@postgresql.org>
2024-02-12 13:31 ` Re: Safest pgupgrade jump distance Johnathan Tiamoh <johnathantiamoh@gmail.com>
2024-02-12 14:31 ` Re: Safest pgupgrade jump distance Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-12 16:54 ` Re: Safest pgupgrade jump distance Greg Sabino Mullane <htamfids@gmail.com>
0 siblings, 4 replies; 7+ messages in thread
From: Dávid Suchan @ 2024-02-12 10:07 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Safest pgupgrade jump distance
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
@ 2024-02-12 10:15 ` Justin Clift <justin@postgresql.org>
3 siblings, 0 replies; 7+ messages in thread
From: Justin Clift @ 2024-02-12 10:15 UTC (permalink / raw)
To: Dávid Suchan <david.suchan.ds@gmail.com>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Safest pgupgrade jump distance
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
@ 2024-02-12 13:31 ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Johnathan Tiamoh @ 2024-02-12 13:31 UTC (permalink / raw)
To: Dávid Suchan <david.suchan.ds@gmail.com>; +Cc: pgsql-general
I have upgraded from 9.5 to 14 using the -link option. It works fine.
I equally had streaming replication running on it.
I break(split brain) replication and upgrade the standby, once it done and
everything is running smoothly, I then install version 14 to the old
primary and just configure streaming on it and all now runs on 14.
On Mon, Feb 12, 2024 at 5:08 AM Dávid Suchan <david.suchan.ds@gmail.com>
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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Safest pgupgrade jump distance
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
@ 2024-02-12 14:31 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-12 16:07 ` Re: Safest pgupgrade jump distance Karsten Hilbert <Karsten.Hilbert@gmx.net>
3 siblings, 1 reply; 7+ messages in thread
From: Ron Johnson @ 2024-02-12 14:31 UTC (permalink / raw)
To: pgsql-general
On Mon, Feb 12, 2024 at 5:08 AM Dávid Suchan <david.suchan.ds@gmail.com>
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
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."
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Safest pgupgrade jump distance
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
2024-02-12 14:31 ` Re: Safest pgupgrade jump distance Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-12 16:07 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-12 16:52 ` Re: Safest pgupgrade jump distance Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Karsten Hilbert @ 2024-02-12 16:07 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Safest pgupgrade jump distance
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
2024-02-12 14:31 ` Re: Safest pgupgrade jump distance Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-12 16:07 ` Re: Safest pgupgrade jump distance Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2024-02-12 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2024-02-12 16:52 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Safest pgupgrade jump distance
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
@ 2024-02-12 16:54 ` Greg Sabino Mullane <htamfids@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Greg Sabino Mullane @ 2024-02-12 16:54 UTC (permalink / raw)
To: Dávid Suchan <david.suchan.ds@gmail.com>; +Cc: pgsql-general
On Mon, Feb 12, 2024 at 5:08 AM Dávid Suchan <david.suchan.ds@gmail.com>
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?
You can go direct. You really should go to 16 though. If nothing else, you
gain lots of performance improvements and another year before your version
goes EOL (end of life).
> 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 assume you mean the three-way rsync recipe in the docs.
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.
>
Not sure about "best", it is certainly the fastest. For data loss concerns,
that's where your backup system (e.g. pgBackRest) is key.
Cheers,
Greg
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-02-12 16:54 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-12 10:07 Safest pgupgrade jump distance Dávid Suchan <david.suchan.ds@gmail.com>
2024-02-12 10:15 ` Justin Clift <justin@postgresql.org>
2024-02-12 13:31 ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2024-02-12 14:31 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-12 16:07 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2024-02-12 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-12 16:54 ` Greg Sabino Mullane <htamfids@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox