Upgrading to PostgreSQL 11 in Debian Stretch
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.logAt 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.logAt 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