Create a gist now

Instantly share code, notes, and snippets.

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.

Show comment Hide comment
@Decoherent

Decoherent Aug 17, 2017

Thanks, this was very helpful, and worked perfectly!

Thanks, this was very helpful, and worked perfectly!

@ValeryDubrava

This comment has been minimized.

Show comment Hide comment
@ValeryDubrava

ValeryDubrava Aug 17, 2017

Thank you! Cool topic!

Thank you! Cool topic!

@bozhenkodm

This comment has been minimized.

Show comment Hide comment
@bozhenkodm

bozhenkodm Oct 31, 2017

Thank you! Very helpful.

Thank you! Very helpful.

@JohnDP23

This comment has been minimized.

Show comment Hide comment
@JohnDP23

JohnDP23 Feb 11, 2018

Thank you! You helped a LOT! Great topic.

Thank you! You helped a LOT! Great topic.

@Piokaz

This comment has been minimized.

Show comment Hide comment
@Piokaz

Piokaz Feb 12, 2018

Thank you !

Piokaz commented Feb 12, 2018

Thank you !

@jackm

This comment has been minimized.

Show comment Hide comment
@jackm

jackm Mar 12, 2018

Worked wonderfully on Debian 9.3 stretch

jackm commented Mar 12, 2018

Worked wonderfully on Debian 9.3 stretch

@returnusername

This comment has been minimized.

Show comment Hide comment
@returnusername

returnusername Mar 13, 2018

Thanks! This was very Helpful

Thanks! This was very Helpful

@jaysylvester

This comment has been minimized.

Show comment Hide comment
@jaysylvester

jaysylvester Mar 25, 2018

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

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

@MathiasZaja

This comment has been minimized.

Show comment Hide comment
@MathiasZaja

MathiasZaja 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.

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.

Show comment Hide comment
@nsitbon

nsitbon 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!

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!

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