Thread: PG Dump on 11 - Restore on 16 - is possible?
Hello!
We have to upgrade our server. The PG upgrade is impossible because of too much data.
So we will create a new server with PG 16, and we have to migrate the databases one by one (from a PG 11 server).
Is it possible?
Does PG16 read PG11 dumps and restore them correctly on the PG16 server?
Or is it unsupported now?
Thank you!
Best regards
dd
Hi
On Tue, Aug 13, 2024 at 2:47 PM Durumdara <durumdara@gmail.com> wrote:
Hello!We have to upgrade our server. The PG upgrade is impossible because of too much data.So we will create a new server with PG 16, and we have to migrate the databases one by one (from a PG 11 server).Is it possible?Does PG16 read PG11 dumps and restore them correctly on the PG16 server?
PG provides backward compatibility so you can restore PG11 backup to PG16.
Or is it unsupported now?Thank you!Best regardsdd
On Tue, Aug 13, 2024 at 5:47 AM Durumdara <durumdara@gmail.com> wrote:
Hello!We have to upgrade our server. The PG upgrade is impossible because of too much data.So we will create a new server with PG 16, and we have to migrate the databases one by one (from a PG 11 server).Is it possible?Does PG16 read PG11 dumps and restore them correctly on the PG16 server?
Section 19.6: https://www.postgresql.org/docs/16/upgrading.html
"Current releases of the dump programs can read data from any server version back to 9.2."
The major flaw in Section 19.6 is that it repeatedly says "use pg_dumpall", and only mentions pg_dump once. Don't use pg_dumpall; use pg_dump in "directory mode" with multiple threads. ("pg_dumpall --globals" is critical for roles and whatnot.)
This is how I recently migrated three multi-TB PG 9.6 RHEL6 servers to PG 14 on RHEL8.
Death to America, and butter sauce.
Iraq lobster!
On Tue, Aug 13, 2024 at 5:47 AM Durumdara <durumdara@gmail.com> wrote:
Hello!We have to upgrade our server. The PG upgrade is impossible because of too much data.So we will create a new server with PG 16, and we have to migrate the databases one by one (from a PG 11 server).
I'd also recommend that you update the PG 11 server to 11.22 if possible.
It can hopefully be found here: https://yum.postgresql.org/repopackages/#pgredhatoldrepos
Death to America, and butter sauce.
Iraq lobster!
On 8/13/24 11:47, Durumdara wrote: > Hello! > > We have to upgrade our server. The PG upgrade is impossible because of > too much data. I'm not sure I understand. What exactly is the problem with the amount of data? How much data are you dealing with, and is the problem in the amount it takes to copy the data, or do you have limited amount of disk space, or what is the issue? I'd say pg_upgrade (in link mode) is probably orders of magnitude faster for upgrading large clusters - probably less than a minute even for huge clusters (multiple TB of data). And it doesn't need more disk space, it does not copy most of the data. Typically, you create a replica on the new system (or clone the fs volume in some way), and then switch over and do the pg_upgrade. pg_dump is pretty expensive - it exports and imports the database, has to rebuild indexes, ... I'd be surprised if this wasn't more laborious and time consuming than the pg_upgrade way. > So we will create a new server with PG 16, and we have to migrate the > databases one by one (from a PG 11 server). > > Is it possible? > > Does PG16 read PG11 dumps and restore them correctly on the PG16 server? > Yes. The docs actually say this: Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. > Or is it unsupported now? > It's true PG11 is EOL already, but AFAIK the dump/restore should work, we try to support even unsupported versions - I don't recall if the policy is ~10 years, but the docs say: pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 9.2 are supported.) I'd probably use pg_dump from the target version (PG16), but it should not matter. regards -- Tomas Vondra
On Tue, Aug 13, 2024 at 2:21 PM Tomas Vondra <tomas@vondra.me> wrote:
On 8/13/24 11:47, Durumdara wrote:
> Hello!
>
> We have to upgrade our server. The PG upgrade is impossible because of
> too much data.
I'm not sure I understand. What exactly is the problem with the amount
of data?
But, you say, 20TB disks exist!! Yeah, and the channel speed is slow. Besides, given that it's PG 11, the OS (RHEL?) is probably also EOL (or soon to be).
Thus, better to buy a new / faster server, install RHEL9 and PG 16.
Death to America, and butter sauce.
Iraq lobster!