Skip to content

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.

Copy link

@Decoherent Decoherent commented Aug 17, 2017

Thanks, this was very helpful, and worked perfectly!

@ValeryDubrava

This comment has been minimized.

Copy link

@ValeryDubrava ValeryDubrava commented Aug 17, 2017

Thank you! Cool topic!

@bozhenkodm

This comment has been minimized.

Copy link

@bozhenkodm bozhenkodm commented Oct 31, 2017

Thank you! Very helpful.

@JohnDP23

This comment has been minimized.

Copy link

@JohnDP23 JohnDP23 commented Feb 11, 2018

Thank you! You helped a LOT! Great topic.

@Piokaz

This comment has been minimized.

Copy link

@Piokaz Piokaz commented Feb 12, 2018

Thank you !

@jackm

This comment has been minimized.

Copy link

@jackm jackm commented Mar 12, 2018

Worked wonderfully on Debian 9.3 stretch

@returnusername

This comment has been minimized.

Copy link

@returnusername returnusername commented Mar 13, 2018

Thanks! This was very Helpful

@jaysylvester

This comment has been minimized.

Copy link

@jaysylvester 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.

Copy link

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

Copy link

@nsitbon 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.

Copy link

@rdica rdica commented Jul 19, 2018

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

@lenalebt

This comment has been minimized.

Copy link

@lenalebt lenalebt commented Jul 31, 2018

Thanks, successful update on debian stretch :).

@rafaelrozon

This comment has been minimized.

Copy link

@rafaelrozon rafaelrozon commented Aug 1, 2018

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

@JayGhb

This comment has been minimized.

Copy link

@JayGhb 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

@abdulwajid016

This comment has been minimized.

Copy link

@abdulwajid016 abdulwajid016 commented Mar 12, 2019

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,

@EHJ-52n

This comment has been minimized.

Copy link

@EHJ-52n EHJ-52n commented Jul 1, 2019

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.

@prabowomurti

This comment has been minimized.

Copy link

@prabowomurti prabowomurti commented Jul 19, 2019

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

@stanbrow

This comment has been minimized.

Copy link

@stanbrow stanbrow commented Dec 6, 2019

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.

@alexbodn

This comment has been minimized.

Copy link

@alexbodn alexbodn commented Feb 6, 2020

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

@clicktechnology

This comment has been minimized.

Copy link

@clicktechnology clicktechnology commented Feb 29, 2020

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

@digitalm8

This comment has been minimized.

Copy link

@digitalm8 digitalm8 commented Mar 10, 2020

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

+1 Thanks, topicstarter!

@JoooostB

This comment has been minimized.

Copy link

@JoooostB JoooostB commented Jun 9, 2020

Thank you very much, very helpful :)

@sanderbohm

This comment has been minimized.

Copy link

@sanderbohm sanderbohm commented Jun 9, 2020

Thanks! it worked perfectly!!

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