Horton

Thread

  1. PG Dump on 11 - Restore on 16 - is possible?

    Durumdara <durumdara@gmail.com> — 2024-08-13T09:47:10Z

    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

  2. Re: PG Dump on 11 - Restore on 16 - is possible?

    Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2024-08-13T09:53:13Z

    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 regards
> dd
>

  3. Re: PG Dump on 11 - Restore on 16 - is possible?

    Ron <ronljohnsonjr@gmail.com> — 2024-08-13T11:58:04Z

    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?
>

https://www.postgresql.org/docs/release/16.0/
"See Section 19.6 <https://www.postgresql.org/docs/16/upgrading.html> for
general information on migrating to new major releases."

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!

  4. Re: PG Dump on 11 - Restore on 16 - is possible?

    Ron <ronljohnsonjr@gmail.com> — 2024-08-13T12:00:23Z

    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!

  5. Re: PG Dump on 11 - Restore on 16 - is possible?

    Tomas Vondra <tomas@vondra.me> — 2024-08-13T18:21:05Z

    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

  6. Re: PG Dump on 11 - Restore on 16 - is possible?

    Ron <ronljohnsonjr@gmail.com> — 2024-08-13T18:32:50Z

    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?
>

OP has a physical server, and the cabinet's disk trays are all full (or at
least not enough free trays to add enough disk space).

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!