Upgrading to PostgreSQL 11 in Debian Stretch

Two elephants crossing each other

PostgreSQL has taken an enormous lap in last years, not only in features, but in performance and modules, in several cases we have no hurry to upgrading 9.6 while there is LTS. In many other cases we might want to next latest features or test extensions like PipelineDB, we will walk you thhrough the update procedure in Debian to reach release 11, these steps will be sufficient for any mayor release upgrade.

As we saw in /en/installing-latest-postgresql-release-debian-9-and-8 Debian repositories don't hold latest PostgreSQL releases so the first step will be adding postgresql repositories before upgrading.

Upgrading between minor releases has no additional work, just upgrading package will be enough, this will upgrade binaries and restart services. But when it comes to major releases there are structural changes that make database files to be not compatible between releases and that's why you need extra steps after installing new release.

Don't panic

Upgrade procedure is quite straightforward, and on top of all is safe for your data, the only issue you will face during upgrade is a the downtime - how long the downtime is depends on your database size and the speed of your system, but won't be a few seconds - we will cover in a further article how to make this process without downtime but in most scenarios a small downtime is something affordable and you don't need extra hassle.

Databases are set all together in so-called clusters (I don't like the name becuase it generates confusion) so, a cluster is in this context a set of files containing configuration, data and logs from a set of databases. Using this feature upgrade can be done without overwriting any current data.

Before starting... a stupid check

Upgrade process will create a second cluster that will have more or less the same size than the original one, so it's nothing stupid checking we have enough spare room to hold a second cluster in our system, to check the available room you can use:

df -h
Filesystem      Size  Used Avail Use% Mounted on
udev             10M     0   10M   0% /dev
tmpfs           393M   41M  352M  11% /run
/dev/vda1        20G   11G  7.9G  59% /
tmpfs           982M  4.0K  982M   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           982M     0  982M   0% /sys/fs/cgroup
tmpfs           197M     0  197M   0% /run/user/1001

And now you can check database size:

sudo du -h /var/lib/postgresql/9.6/
612K    /var/lib/postgresql/9.6/main/global
4.0K    /var/lib/postgresql/9.6/main/pg_serial
7.4M    /var/lib/postgresql/9.6/main/base/1
26M     /var/lib/postgresql/9.6/main/base/16398
4.0K    /var/lib/postgresql/9.6/main/base/pgsql_tmp
12M     /var/lib/postgresql/9.6/main/base/16397
2.2G    /var/lib/postgresql/9.6/main/base/16395
96M     /var/lib/postgresql/9.6/main/base/16396
180M    /var/lib/postgresql/9.6/main/base/16393
11M     /var/lib/postgresql/9.6/main/base/16394
7.1M    /var/lib/postgresql/9.6/main/base/13030
7.4M    /var/lib/postgresql/9.6/main/base/13031
2.6G    /var/lib/postgresql/9.6/main/base
1.2M    /var/lib/postgresql/9.6/main/pg_clog
4.0K    /var/lib/postgresql/9.6/main/pg_dynshmem
232K    /var/lib/postgresql/9.6/main/pg_subtrans
4.0K    /var/lib/postgresql/9.6/main/pg_twophase
4.0K    /var/lib/postgresql/9.6/main/pg_logical/snapshots
4.0K    /var/lib/postgresql/9.6/main/pg_logical/mappings
16K     /var/lib/postgresql/9.6/main/pg_logical
12K     /var/lib/postgresql/9.6/main/pg_notify
4.0K    /var/lib/postgresql/9.6/main/pg_xlog/archive_status
129M    /var/lib/postgresql/9.6/main/pg_xlog
4.0K    /var/lib/postgresql/9.6/main/pg_snapshots
4.0K    /var/lib/postgresql/9.6/main/pg_commit_ts
4.0K    /var/lib/postgresql/9.6/main/pg_replslot
4.0K    /var/lib/postgresql/9.6/main/pg_stat
4.0K    /var/lib/postgresql/9.6/main/pg_tblspc
4.0K    /var/lib/postgresql/9.6/main/pg_stat_tmp
36K     /var/lib/postgresql/9.6/main/pg_multixact/members
12K     /var/lib/postgresql/9.6/main/pg_multixact/offsets
52K     /var/lib/postgresql/9.6/main/pg_multixact
2.7G    /var/lib/postgresql/9.6/main
2.7G    /var/lib/postgresql/9.6/

So there are no room issues in this case.

Upgrading packages

First step is upgrading packages, if we are using metapackages (that is the packages without release number: pgbouncer postgresql postgresql-client-common postgresql-common) we just need to upgrade packages:

sudo apt update && sudo apt upgrade

If we are using release specific packages (which will allow us to have several releases at same time) we should need to select the exact release we want:

sudo apt update && sudo apt install postgresql-11 postgresql-client-11

Get:1 http://ftp.debian.org/debian stretch/main amd64 libc-ares2 amd64 1.12.0-1+deb9u1 [81.6 kB]
Get:2 http://security.debian.org stretch/updates/main amd64 libexpat1-dev amd64 2.2.0-2+deb9u3 [134 kB]


Success. You can now start the database server using:

    pg_ctlcluster 11 main start

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5433 down   postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

At the end of the upgrade we will have a new cluster running the new release on the next free port (in this case 5433)

Upgrading cluster

Package installer creates an empty cluster, but this is not what we want because we want to migrate our previous one (the one from release 9.6) so we will destroy the newly created one:

sudo pg_dropcluster --stop 11 main

There are two choices when it comes to upgrade cluster, postgres offers binaries to do so and also Debian offers script (which use that binaries) but are adapted to Debian file structure, and the latter is the recommended one if we are using the Debian way of handling structure. If we are using our own file structure there is no gain from using Debian scripts. In this case we will use Debian scripts:

pg_upgradecluster -v 11 9.6 main
Stopping old cluster... <- aquí deja de estar disponible la base de datos
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Creating new PostgreSQL cluster 11/main ...


Configuring old cluster to use a different port (5433)...
Starting target cluster on port 5432... <- aquí vuelve a estar disponible

Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 9.6 main

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

At this point we have two clusters with same data but different release, the new cluster will be running in the default port(or the port we had configured for old cluster) and now will be the old cluster the one running on the next free port, if we are using pgbouncer then it's recommended restarting it to avoid old connections running against old cluster. Once we restart pgbouncer and all is working fine (take your time to check all your apps) we can clean all the remaining fils from old cluster.

What happens if something is wrong?

I haven't experienced any kind of issue with the upgrade procedure besides having a particular that's making use of system tables that might be affected for any structure change (i.e. statistics table) that might force us to keep using old cluster while we adapt apps.

The best point of this approach is we still have old cluster there so we can point the conflictive apps to old cluster while we fix the issue, to start old cluster just do:

pg_ctlcluster 9.6 main start

So we can keep using both and migrate the conflicting database once we have finished updating it, when we are ready it's time to clean old cluster.

Cleaning the house

Once old is working and we are happy with new database release we can clean old cluster and binaries:

sudo pg_dropcluster --stop 9.6 main

sudo apt remove postgresql-9.6

And that's it PostgreSQL 11 is running in our server