Potential pitfalls for upgrading PostgreSQL from 9.x/10.x to 13.x?

Posted on

Question :

I have inherited from a predecessor a large number of Debian 9 systems that we are now looking at upgrading to Debian 11. Most of these run PostgreSQL 9 or 10, installed from Postgres’s own repositories, with the balance being just about in favour of the 9.x systems. Obviously we will be updating everything as part of the system upgrades, including PostgreSQL itself. We’d probably still be using Postgres’ own repo rather than the Debian one, but be using 13 over 14 simply on software maturity grounds, which I believe comes with Debian 11 anyway.

My question is: is such a large jump likely to introduce problems? Will a dist upgrade from Debian 9 to 10 to 11 (as per official recommendation) with this setup still have an accessible database at the end without having to dump and re-import? I know I’m probably going to have to do an apt install postgres-13 at some point along this chain and an apt remove postgres-9; will the data stay present when this is done or do I need to dump the data at the start, do all the upgrading, then re-import it all back at the end?

Answer :

There was never a PostgreSQL version 9, but that is beside the point here.

Most of the time, you won’t have a problem with such an upgrade. But each major version of PostgreSQL introduced some incompatibilities, which are documented in the release notes of the base release (9.6, 10, 11, …), so you cannot be certain.

You should read through all these release notes to get an idea what problems you may encounter, but the important part is that you test your application thoroughly. A test is the only way to make sure you won’t run into problems.

Leave a Reply

Your email address will not be published. Required fields are marked *