101 lines
3.7 KiB
Markdown
101 lines
3.7 KiB
Markdown
---
|
|
title: "From Stretch to Buster : How to upgrade a 9.6 PostgreSQL cluster to 11 ?"
|
|
date: 2019-11-15
|
|
url: from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11
|
|
layout: post
|
|
category: Tutorials
|
|
image: /img/blog/from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11.png
|
|
description: "A step-by-step tutorial for a not-so-guided Debian migration"
|
|
---
|
|
|
|
[](/img/blog/from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11.png)
|
|
|
|
### 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 (:relieved:), 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](https://www.debian.org/releases/buster/amd64/release-notes/ch-information.en.html#postgresql-reindex), 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](https://www.postgresql.org/support/versioning/), this major version [has to be considered outdated](https://tracker.debian.org/pkg/postgresql-9.6) 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 :wave:
|