Upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu 16.10

by

A Step-by-Step Guide

  1. Make a backup. Make sure that your database is not being updated.

     pg_dumpall > outputfile

  2. Install Postgres 10. Follow instructions on this page: https://www.postgresql.org/download/linux/ubuntu/

    Then run sudo apt-get install postgresql-10. A newer version will be installed side-by-side with the earlier version.

  3. Run 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

    There already is a cluster main for 10 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.6/main when 10/main also exists. The recommended procedure is to remove the 10 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

  4. Stop the 10 cluster and drop it:

     sudo pg_dropcluster 10 main --stop

  5. Stop all processes and services writing to the database. Stop the database:

     sudo systemctl stop postgresql

  6. Upgrade the 9.6 cluster:

     sudo pg_upgradecluster -m upgrade 9.6 main

  7. Start PostgreSQL again

     sudo systemctl start postgresql

  8. Run pg_lsclusters . Your 9.6 cluster should now be “down”, and the 10 cluster should be online at 5432:

     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
 10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

  9. First, check that everything works fine. After that, remove the 9.6 cluster:

      sudo pg_dropcluster 9.6 main --stop

Some notes on pg_upgradecluster

This guide works fine for upgrading from 9.5 to 10.1. When upgrading from an older version, consider omitting -m upgrade on the step #6:

sudo pg_upgradecluster 9.6 main

If you have a really big cluster, you may use pg_upgradecluster with a --link option, so that the upgrade will be in-place. However, this is dangerous — you can lose the cluster in an event of failure. Just don’t use this option if not necessary, as -m upgrade is already fast enough.

Based on:

  • Docs: Upgrading a PostgreSQL Cluster
  • Gist #1: delameko/upgrade-postgres-9.5-to-9.6.md
  • Gist #2: johanndt/upgrade-postgres-9.3-to-9.5.md
  • What happens if I interrupt or cancel pg_upgradecluster?
  • Ubuntu manpage for pg_upgradecluster

Update

This guide works fine for upgrading from 9.6 to 11 and from 10 to 11, as well as from 10 to 13.

Leave a Comment