Introduction

Here we are, Debian Buster (10) has been out as the new stable release for 4 months now, so it’s definitely time to upgrade your servers !

As long as PHP 7.3 (), there are plenty of new awesome stuffs to take in consideration.
The target of this short post is PostgreSQL.

Back in the “past”, Debian Stretch (9.9) proposed the version 9.6 of the RDBMS, whereas Buster (10.X) now allows you to enjoy the 11th major version.

But there is a caveat : this upgrade is supposed to be as straightforward as the distribution itself, but IMHO, it’s not.
So here is one more step-by-step guide.

System upgrade

# Let's upgrade to the latest Stretch
apt update && apt upgrade -y

# Major upgrade to Buster
sed -i 's/stretch/buster/g' /etc/apt/sources.list
apt update && apt dist-upgrade

A first issue

So at this step, you should have a running PostgreSQL 9.6 cluster… as before then.
Pretty confusing, huh ? That’s not the all of it.

Due to a minor update of the glibc changing system’s locales, you’ll need to re-index all your databases entries.

So let’s do it :

su - postgres -c 'reindexdb --all'

Migration to PostgreSQL 11

What is even more confusing is that the upgrade process has installed on your system the postgresql-11 package, initialized with an empty (useless) main cluster.
As upgrading to 11 is not a meaningless operation, it looks like maintainers preferred letting the 9.6 version available on Buster, including once the upgrade is done.

You can actually check your setup this way :

su - postgres -c 'pg_lsclusters'

# or this way :
systemctl status postgresql.service

systemctl status postgresql@9.6-main.service
systemctl status postgresql@11-main.service

So let’s migrate then !

Before anything else here, I’d advise you to transfer your specific PostgreSQL configuration from /etc/postgresql/9.6/* to /etc/postgresql/11/*.

Once it’s done :

# We first delete the new empty default `main` cluster
su - postgres -c 'pg_dropcluster --stop 11 main'

# Now we may effectively upgrade from 9.6 to 11 this way
su - postgres -c 'pg_upgradecluster 9.6 main'

PS : If your setup runs multiple clusters, don’t forget to upgrade them all !

Final cleanup

When you have verified that everything works well against v11 cluster(s), you may safely remove the old (outdated) PostgreSQL 9.6 packages :

su - postgres -c 'pg_dropcluster --stop 9.6 main'

apt autoremove postgresql-9.6
apt autoclean

Conclusion

Even if PostgreSQL 9.6 is still currently supported, this major version has to be considered outdated from a Debian point of view.

Hope it was clear enough and that you’ll figure something out.
If you encounter any issue, please let a comment below, I’ll do my best to review them.

Bye