Subject: Can we directly upgrade postgresql from 13 to 15.4

From: jayesh thakare <[email-masked]>
Date: Fri, 8 Nov 2024 23:42:37 +0530

Hi all,

Can we upgrade postresql from 13 to 15.4 directly?
Can we upgrade postgresql from 13 to 16 directly?

Ideally how many major version can we jump to from older version ?

Regards,
Jayeshthakare
8828986182
Postgresql dba | Clover Infotech

---

From: Christophe Pettus <[email-masked]>
Date: Fri, 8 Nov 2024 10:16:39 -0800

> On Nov 8, 2024, at 10:12, jayesh thakare <[email-masked]> wrote:

Yes.

> Ideally how many major version can we jump to from older version ?

There's no practical limit, assuming the old version is not an ancient version of PostgreSQL (pre-9.0, say).

That being said, PostgreSQL's behavior changes with every major release, and there is always the possibility of query plan changes or other surprises.  It's essential to test your application thoroughly on the target version, and do dry runs of pg_upgrade.

---

From: Adrian Klaver <[email-masked]>
Date: Fri, 8 Nov 2024 12:23:03 -0800

> Can we upgrade postgresql from 13 to 16 directly?

Yes either dump/restore:

https://www.postgresql.org/docs/current/app-pgdump.html

https://www.postgresql.org/docs/current/app-pgrestore.html

Or pg_upgrade:

https://www.postgresql.org/docs/current/pgupgrade.html

Pay particular attention to the Notes section at each of the above links

> Ideally how many major version can we jump to from older version ?
Some of that is covered in the Notes section.

You should also look at the Release Notes:

https://www.postgresql.org/docs/release/

The important ones are for Postgres version 10+ those for X.0 where X is 
the major release e.g. 10, 11, 12, etc.

For 9- the release to look for is X.X.0 where X.X is 9.6, 9.5, 9.4, etc.

These will show you the major changes that you might need to deal with. 
For example in Postgres 15 the langauges plpythonu/plpython2u where 
removed, so if you are using plpython* functions you need to make sure 
they have been migrated to plpython3u.

-- 
Adrian Klaver
[email-masked]

---

From: Achilleas Mantzios <[email-masked]>
Date: Sat, 9 Nov 2024 07:46:04 +0200

> There's no practical limit, assuming the old version is not an ancient version of PostgreSQL (pre-9.0, say).

In which case I guess it is possible to use pg_upgrade of the maximum 
version in the same supported "window". e.g. pg_upgrade of 9.4 to 
upgrade from 8.3, and so forth, in iterations till someone hits the 
maximum desired version.

IMHO one should pay attention with pre-PGXS modules (such as intarray) , 
I stumped across some ugly situations with that.