Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Star 73 You must be signed in to star a gist
  • Fork 15 You must be signed in to fork a gist
  • Save dmitrykustov/27c673ec4f7abd716912e4c830910019 to your computer and use it in GitHub Desktop.
Save dmitrykustov/27c673ec4f7abd716912e4c830910019 to your computer and use it in GitHub Desktop.
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
Copy link

Thanks, this was very helpful, and worked perfectly!

@ValeryDubrava
Copy link

Thank you! Cool topic!

@bozhenkodm
Copy link

Thank you! Very helpful.

@JohnDP23
Copy link

Thank you! You helped a LOT! Great topic.

@Piokaz
Copy link

Piokaz commented Feb 12, 2018

Thank you !

@jackm
Copy link

jackm commented Mar 12, 2018

Worked wonderfully on Debian 9.3 stretch

@returnusername
Copy link

Thanks! This was very Helpful

@jaysylvester
Copy link

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

@MathiasZaja
Copy link

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
Copy link

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
Copy link

rdica commented Jul 19, 2018

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

@lenalebt
Copy link

Thanks, successful update on debian stretch :).

@rafaelrozon
Copy link

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

@JayGhb
Copy link

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
Copy link

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
Copy link

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
Copy link

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

@stanbrow
Copy link

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
Copy link

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
Copy link

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

@digitalm8
Copy link

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

+1 Thanks, topicstarter!

@JoooostB
Copy link

JoooostB commented Jun 9, 2020

Thank you very much, very helpful :)

@sanderbohm
Copy link

Thanks! it worked perfectly!!

@fahdikrie
Copy link

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.

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