Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
@Decoherent

This comment has been minimized.

Decoherent commented Aug 17, 2017

Thanks, this was very helpful, and worked perfectly!

@ValeryDubrava

This comment has been minimized.

ValeryDubrava commented Aug 17, 2017

Thank you! Cool topic!

@bozhenkodm

This comment has been minimized.

bozhenkodm commented Oct 31, 2017

Thank you! Very helpful.

@JohnDP23

This comment has been minimized.

JohnDP23 commented Feb 11, 2018

Thank you! You helped a LOT! Great topic.

@Piokaz

This comment has been minimized.

Piokaz commented Feb 12, 2018

Thank you !

@jackm

This comment has been minimized.

jackm commented Mar 12, 2018

Worked wonderfully on Debian 9.3 stretch

@returnusername

This comment has been minimized.

returnusername commented Mar 13, 2018

Thanks! This was very Helpful

@jaysylvester

This comment has been minimized.

jaysylvester commented Mar 25, 2018

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

@MathiasZaja

This comment has been minimized.

MathiasZaja commented Mar 28, 2018

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.

@nsitbon

This comment has been minimized.

nsitbon commented May 9, 2018

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

@rdica

This comment has been minimized.

rdica commented Jul 19, 2018

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

@lenalebt

This comment has been minimized.

lenalebt commented Jul 31, 2018

Thanks, successful update on debian stretch :).

@rafaelrozon

This comment has been minimized.

rafaelrozon commented Aug 1, 2018

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

@JayGhb

This comment has been minimized.

JayGhb commented Oct 10, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment