Subject: Upgrade Ubuntu 22 -> 24 may break PostgreSQL

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 31 Aug 2024 18:54:45 +0200

'Tis the season again.

Ubuntu 24.04.1 has just been released, so many Ubuntu LTS users will now
be prompted to upgrade from 22.04 to 24.04.

A word of warning to those who use Postgresql from the Ubuntu repo (not
PGDG):

As usual, a newer Ubuntu version comes with a newer Postgres version (16
instead of 14). Also as usual, I got a message during the upgrade that
Postgres 14 is obsolete,. but the binaries have been left installed and
I should upgrade to Postgres 16 manually ASAP.

But after the reboot, PostgreSQL failed to start because it needed a
shared library (libldap) which was no longer there. So a normal
pg_upgradecluster wouldn't work.

In my case the quickest way to recover was to install postgresql-14 on a
VM, copy the data direcory into that instance and make a fresh dump,
then install postgresql-16 on my laptop and restore the dump. Annoying,
but no big deal for the small test database I keep on my laptop. If you
have multi-terabyte databases, your situation may be different.

I'm not exactly sure what went wrong (I got some conflicts during the
upgrade and maybe I shouldn't have invoked apt autoremove?), and you may
not have this problem, but make sure you have a backup before the
upgrade.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email-masked]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 31 Aug 2024 10:35:01 -0700

> be prompted to upgrade from 22.04 to 24.04.

Which I ignore.

> A word of warning to those who use Postgresql from the Ubuntu repo (not

Why I do use the PGDG repo's.

> pg_upgradecluster wouldn't work.

sudo apt install libldap-X.x did not work?

-- 
Adrian Klaver
[email-masked]

---

From: Justin Clift <[email-masked]>
Date: Sun, 01 Sep 2024 12:32:43 +1000

> I should upgrade to Postgres 16 manually ASAP.

It'd *technically* be possible to automatically run an upgrade of the
PostgreSQL repository (via scripting?) at launch time, though just 
blindly
doing it for everyone would be a *major* change of behaviour.

Some people would likely love it, while others would be horrified (etc).

That being said, if we announce it ahead of time as a feature of a major
release (ie PG 18 or something), and if we have a clear way to not
automatically upgrade (a variable in postgresql.conf?), then we might be
able to solve this problem ~permanently.

We'd also need to figure out how to handle (say) rebuilding of indexes
that need updating between major versions and stuff like that.

Thoughts?

Regards and best wishes,

Justin Clift

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 1 Sep 2024 09:08:21 -0700

> PostgreSQL repository (via scripting?) at launch time, though just blindly

The OP was using the Ubuntu repo and for a given major version of Ubuntu 
that is pinned to a given major version of Postgres. I am not seeing 
changing that would go over well. Now if a user is using the PGDG repo's 
that is a different story, then you point at the repo's for the new 
Ubuntu version do an update/upgrade and you are back on track.

> Thoughts?

This is something the end user needs to work out ahead of time as there 
is an overhead in the process they need to take into consideration. 
Cranking up a new version of Ubuntu/Debian and have it take off doing 
things behind the scenes to the Postgres instance(s) would disturb me.