Upgrading PostgreSQL from 9.4 to 9.6 on Debian Jessie

upgrade-postgres-9.4-to-9.6.md

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)

Thanks, this was very helpful, and worked perfectly!

Thank you! Cool topic!

Thank you! Very helpful.

Thank you! You helped a LOT! Great topic.

Thank you !

Worked wonderfully on Debian 9.3 stretch

Thanks! This was very Helpful

Clean and straightforward. Helped me upgrade both a dev and production environment today. Thanks!

Thanks a lot Dmitry. Really great und very usefull post.
I spent 2 days with various tries (pgdump_all, pg_ctl stop/start etc), none of wich worked satisfactory.
It took me 15 minutes to test your procedure in development and 5 minutes to upgrade in production.
You saved my Easter.

I just used the same tutorial to migrate from PG 9.4 to PG 10 without any issues. Thank you so much!

Successful 9.4 -> 9.6 on Ubuntu 16.04.4 LTS. Thanks!

Thanks, successful update on debian stretch :).

Thank you, you saved me hours of pain!!!

when I run the wget command, I get " gpg: no valid OpenPGP data found " .
Can someone tell me how to resolve this? Been looking around everywhere.
Thank you in advance

when I run the wget command, I get " gpg: no valid OpenPGP data found " .
Can someone tell me how to resolve this? Been looking around everywhere.
Thank you in advance

I am having same issue if you found any solution Then plz forward it to me

Regards,

Thank you, worked as outlined!

But when using postgis, you need to update to the version used within the new pg version before the cluster upgrade.

Thank you. I change the http to https but before that I need to install apt-transport-https package.

One suggested addition. If you have any extensions that you compiled and installed from source, AND they are referenced in you usage, you MUST recompile, and do the install steps for the binaries first before doing this. You only need for the upgrade to be able to locate the binaries, you DO NOT need to do the create extension from within the new DB server.

thank you very much.
it worked perfectly.
but i'd still want to remake the template databases for the current version, 11.
please see https://stackoverflow.com/questions/27992560/template0-and-template1-database-dropped-accidently

Sweet upgrade on Debian 10 (Buster) in one go. Much appreciated.

Sweet upgrade on Debian 10 (Buster) in one go. Much appreciated.

+1 Thanks, topicstarter!

Thank you very much, very helpful :)

Thanks! it worked perfectly!!

Worked perfectly on me too! except apt.postgresql is no longer supported as of april 2021 and is replaced by apt-archive.postgresql.org (source).

So, instead of adding deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main, I added deb https://apt-archive.postgresql.org/pub/repos/apt jessie-pgdg-archive main & deb-src https://apt-archive.postgresql.org/pub/repos/apt jessie-pgdg-archive main to the sources.list.