Postgresql Upgrade 9.6 to 10

Rational

PostgreSQL community is very busy developing the worlds leading opensource relation database. Every few years a new version is available with many new features, improvements and bug fixes. In this article we will go through an upgrade procedure for postgresql version 9.6 to version 10. Minor downtime is required.

Attention!! First and foremost be sure to have a valid backup before you proceed with this guide.

LETS BEGIN

First lets stop the postgresql service:

root@postgres:~# systemctl stop postgresql

Now we need to install the apt repo and key for Postgres 10:

root@postgres:~# echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/postgresql10.list
root@postgres:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Then we need to update the repositories cache and install PostgreSQL 10 packages:

root@postgres:~# apt update
root@postgres:~# apt install postgresql-10 postgresql-client-10 postgresql-contrib-10

After installation is finished we should verify installed packages by running the folling command:

root@postgres:~# dpkg -l | grep postgresql
ii  pgdg-keyring                        2018.2                                     all          keyring for apt.postgresql.org
ii  postgresql-10                       10.10-1.pgdg16.04+1                        amd64        object-relational SQL database, version 10 server
ii  postgresql-9.6                      9.6.15-1.pgdg16.04+1                       amd64        object-relational SQL database, version 9.6 server
ii  postgresql-client-10                10.10-1.pgdg16.04+1                        amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-9.6               9.6.15-1.pgdg16.04+1                       amd64        front-end programs for PostgreSQL 9.6
ii  postgresql-client-common            204.pgdg16.04+1                            all          manager for multiple PostgreSQL client versions
ii  postgresql-common                   204.pgdg16.04+1                            all          PostgreSQL database-cluster manager
ii  postgresql-contrib-9.6              9.6.15-1.pgdg16.04+1                       amd64        additional facilities for PostgreSQL

Also verify that two clusters are now available as shown bellow:

root@postgres:~# pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

Ok so now that we have a version 10 cluster lets drop it and upgrade the current 9.6 version to the latest:

root@postgres:~# pg_dropcluster 10 main --stop

Arguments -m upgrade means the upgrade method is upgrade rather that dump (which is the default) and -k means that the dataset is not copied and uses links instead, making the whole procedure almost instant.

root@postgres:~# pg_upgradecluster -m upgrade -k 9.6 main

After the later is finnished we should have a version 10 cluster up and running with our dataset. Start the postgresql service again and drop cluster 9.6.

root@postgres:~# systemctl stop postgresql@96-main.service
root@postgres:~# pg_dropcluster 9.6 main

Start postgresql with:

root@postgres:~# systemctl stop postgresql@10-main.service

or:

root@postgres:~# systemctl stop postgresql

Either commands have the same ouotcome after dropping the old cluster.

Fin

Thats all!