Message-ID: <37ed3aef-58e6-417b-8015-cdf030a6df4b@vondra.me>
Date: Tue, 13 Aug 2024 20:21:05 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: PG Dump on 11 - Restore on 16 - is possible?
To: Durumdara <durumdara@gmail.com>,
 Postgres General <pgsql-general@postgresql.org>
References: <CAEcMXhmBGDWQnVwvhcg-2SPn4PM=PS+aZCouZiu=FEusevWskA@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <CAEcMXhmBGDWQnVwvhcg-2SPn4PM=PS+aZCouZiu=FEusevWskA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/37ed3aef-58e6-417b-8015-cdf030a6df4b%40vondra.me>
Precedence: bulk

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