blog/_posts/2019-11-15-from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11.md

title	date	url	layout	category	image	description
From Stretch to Buster : How to upgrade a 9.6 PostgreSQL cluster to 11 ?	2019-11-15	from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11	post	Tutorials	/img/blog/from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11.png	A step-by-step tutorial for a not-so-guided Debian migration

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

{% highlight bash %}

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 {% endhighlight %}

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 :

{% highlight bash %} su - postgres -c 'reindexdb --all' {% endhighlight %}

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 :

{% highlight bash %} 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 {% endhighlight %}

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 :

{% highlight bash %}

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' {% endhighlight %}

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 :

{% highlight bash %} su - postgres -c 'pg_dropcluster --stop 9.6 main'

apt autoremove postgresql-9.6 apt autoclean {% endhighlight %}

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 👋