Upgrading PostgreSQL from 9.4 to 9.6 on Debian Jessie

To use the most modern version of Postgres software we need to add postgresql repository. Edit /etc/apt/sources.list or create /etc/apt/sources.list.d/pgdg.list and add there a line: deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main Then import the repository signing key, and update the package lists:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Install a new version of PostgreSQL server.

Once the Debian upgrade finished, I used dpkg-query -l postgresql* to check which versions of postgres I have installed.

dpkg-query -l postgresql*
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                        Version            Architecture       Description
+++-===========================-==================-==================-============================================================
ii  postgresql                  9.6+177.pgdg80+1   all                object-relational SQL database (supported version)
un  postgresql-7.4              <none>             <none>             (no description available)
un  postgresql-8.0              <none>             <none>             (no description available)
un  postgresql-9.1              <none>             <none>             (no description available)
ii  postgresql-9.4              9.4.9-0+deb8u1     amd64              object-relational SQL database, version 9.4 server
ii  postgresql-9.6              9.6.1-1.pgdg80+1   amd64              object-relational SQL database, version 9.6 server
un  postgresql-client           <none>             <none>             (no description available)
ii  postgresql-client-9.4       9.4.10-1.pgdg80+1  amd64              front-end programs for PostgreSQL 9.4
ii  postgresql-client-9.6       9.6.1-1.pgdg80+1   amd64              front-end programs for PostgreSQL 9.6
ii  postgresql-client-common    177.pgdg80+1       all                manager for multiple PostgreSQL client versions
ii  postgresql-common           177.pgdg80+1       all                PostgreSQL database-cluster manager
ii  postgresql-contrib-9.6      9.6.1-1.pgdg80+1   amd64              additional facilities for PostgreSQL
un  postgresql-doc              <none>             <none>             (no description available)
un  postgresql-doc-9.4          <none>             <none>             (no description available)
un  postgresql-doc-9.6          <none>             <none>             (no description available)

Looks like the Debian upgrade included PostgreSQL 9.6, but I still need to upgrade from 9.4 to 9.6.

Run pg_lsclusters, your 9.4 and 9.6 main clusters should be "online".

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

There already is a cluster "main" for 9.6 (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.4/main when 9.6/main also exists. The recommended procedure is to remove the 9.6 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.6 cluster and drop it.

sudo pg_dropcluster 9.6 main --stop

Upgrade the 9.4 cluster to the latest version.

sudo pg_upgradecluster 9.4 main

Your 9.4 cluster should now be "down".

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

Check that the upgraded cluster works, then remove the 9.4 cluster.

sudo pg_dropcluster 9.4 main

After all you may totally remove version 9.4 from the server:

sudo apt-get --purge remove postgresql-client-9.4 postgresql-9.4

dpkg-query -l postgresql*
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                        Version            Architecture       Description
+++-===========================-==================-==================-============================================================
ii  postgresql                  9.6+177.pgdg80+1   all                object-relational SQL database (supported version)
un  postgresql-7.4              <none>             <none>             (no description available)
un  postgresql-8.0              <none>             <none>             (no description available)
un  postgresql-9.1              <none>             <none>             (no description available)
ii  postgresql-9.6              9.6.1-1.pgdg80+1   amd64              object-relational SQL database, version 9.6 server
un  postgresql-client           <none>             <none>             (no description available)
ii  postgresql-client-9.6       9.6.1-1.pgdg80+1   amd64              front-end programs for PostgreSQL 9.6
ii  postgresql-client-common    177.pgdg80+1       all                manager for multiple PostgreSQL client versions
ii  postgresql-common           177.pgdg80+1       all                PostgreSQL database-cluster manager
ii  postgresql-contrib-9.6      9.6.1-1.pgdg80+1   amd64              additional facilities for PostgreSQL
un  postgresql-doc              <none>             <none>             (no description available)
un  postgresql-doc-9.6          <none>             <none>             (no description available)

Piokaz/upgrade-postgres-9.4-to-9.6.md