Upgrading: Postgresql 9.6 to 10
Rational
PostgreSQL community is very busy developing the worlds leading opensource relational 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 [email protected]
root@postgres:~# pg_dropcluster 9.6 main
Start postgresql with:
root@postgres:~# systemctl start [email protected]
or:
root@postgres:~# systemctl start postgresql
Either commands have the same ouotcome after dropping the old cluster.
Fin
Thats all!