Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save johanndt/6436bfad28c86b28f794 to your computer and use it in GitHub Desktop.
Save johanndt/6436bfad28c86b28f794 to your computer and use it in GitHub Desktop.
Upgrading PostgreSQL from 9.3 to 9.5 on Ubuntu


Install Postgres 9.5, and then:

sudo pg_dropcluster 9.5 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

Specifically for using the WAL-E enabled Docker image, make sure WAL-E is turned off:

sudo pkill backupcron

Install postgresql 9.5:

chmod a+x
sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g'
sudo ./

sudo apt-get install postgresql-9.5
sudo apt-get install postgresql-client-9.5
sudo apt-get install postgresql-contrib-9.5

Once the installation finished, I used aptitude search to check which versions of postgres I have installed.

i   postgresql                                                        - object-relational SQL database (supported version)
i A postgresql-9.3                                                    - object-relational SQL database, version 9.3 server
i A postgresql-9.5                                                    - object-relational SQL database, version 9.5 server
i A postgresql-client-9.3                                             - front-end programs for PostgreSQL 9.3
i A postgresql-client-9.5                                             - front-end programs for PostgreSQL 9.5
i A postgresql-contrib-9.3                                            - additional facilities for PostgreSQL
i A postgresql-contrib-9.5                                            - additional facilities for PostgreSQL

Looks like we successfully installed PostgreSQL 9.5, but I still need to upgrade from 9.3 to 9.5.

Run pg_lsclusters, your 9.3 and 9.5 main clusters should be "online".

Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

There already is a cluster "main" for 9.5 (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.3/main when 9.5/main also exists. The recommended procedure is to remove the 9.5 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.5 cluster and drop it.

sudo pg_dropcluster 9.5 main --stop

Upgrade the 9.3 cluster to the latest version.

sudo pg_upgradecluster 9.3 main

Your 9.3 cluster should now be "down".

Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5433 down   postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

Check that the upgraded cluster works, then remove the 9.3 cluster.

sudo pg_dropcluster 9.3 main

Now push an initial WAL-E base backup:

sudo -u postgres envdir /etc/wal-e.d/env wal-e backup-push
Copy link

Really helpful! Thanks.

Copy link


I was a bit gotcha'd by the fact that pg_upgradecluster asked for a password thrice, for some reason. I knew I was typing the correct one so kept the faith.

Copy link

ghost commented May 29, 2016

Amazing, thank you very much! 😄

Copy link


Copy link

Great work! This was flawless on ubuntu 14.0 with 9.3 to 9.5 upgrade path

Copy link

Also works for 9.4 to 9.5 upgrade on Ubuntu 14.04 LTS - shut down 9.4 before running pg_upgradecluster

Copy link

Thanks, after the tension of doing a release upgrade to Ubuntu 16.04 I was looking for some reassurance on the Postgres upgrade. Your notes worked perfectly.

Copy link

Thanks.This works like a charm!
And thanks to rherrick as well!

Copy link

Thanks. Its perfect!

Copy link

niborg commented Aug 27, 2016

pithy and perfect!

Copy link

🌷 🐢

Copy link

Thanks a lot. Perfect!

Copy link

thanks @johanndt!

Copy link

@johanndt the first link for responds with 404.
A quick search on postgresql's wiki points me to
Are they the same? If so, you should update this gist

Copy link

werk commented Nov 24, 2016

Great, thanks

Copy link

It seems many people have tried through this doc successfully.
+1 for ERROR 404 Not found, and I haven't continued yet.
Everyone here got success without this error?

Copy link

w- commented Dec 29, 2016

Was able to use this doc to upgrade from 9.3 to 9.6

Copy link

chamnap commented Jan 3, 2017

Need to run this export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8 when local error.

Copy link

The url given by nmenardg works!

Copy link

inyerade commented Feb 7, 2017

Greate doc!! Thx

I add my situation, maybe this help someone

When I do the

pg_upgradecluster 9.3 main

and I have active client connections I get the error

Stopping old cluster...
pg_ctl: server does not shut down
HINT: The "-m fast" option immediately disconnects sessions rather than
waiting for session-initiated disconnection.
Error: Could not stop old cluster

Then I can not login to pg and running again pg_upgradecluster 9.3 main give me

psql: FATAL:  the database system is shutting down
psql: FATAL:  the database system is shutting down
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/ line 925.
psql: FATAL:  the database system is shutting down
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/ line 931.
Use of uninitialized value $ctype in scalar chomp at /usr/share/perl5/ line 934.
Use of uninitialized value $collate in scalar chomp at /usr/share/perl5/ line 935.

I can run again pg_upgradecluster 9.3 main after running

sudo service postgresql restart

Copy link

senolsun commented Mar 8, 2017

these instructions just "work". no mess involved

Copy link

ma0c commented Mar 25, 2017


Copy link

aremmell commented Apr 5, 2017

A warning: just like @inyerade, I forgot to stop a service that had an open connection to the 9.3 cluster when I tried to upgrade it. I didn't realize that it was a terminal error and dropped it without testing the 9.5 cluster.

Needless to say, that didn't go well for me, and I had to restore from backup.

So, if the pg_upgradecluster 9.3 main does not say 'Success' and that you can now test the 9.5 cluster, DO NOT PROCEED!! Stop the connections to the old cluster and run the pg_upgradecluster again.

Copy link

This link has a slightly more concise note regarding how to deal with active connections.

pg_dropcluster 9.5 main --stop
pg_upgradecluster 9.3 main # GOTCHA
service postgresql start
sleep 5
service postgresql stop
pg_upgradecluster 9.3 main # worked ok
pg_dropcluster 9.3 main

Copy link

Thanks for such a wonderful article. I tried to access the following link (wget you posted above but I learned it doesn't exist any more. Can I get a new link?

Copy link


Copy link

+1 for the thanks!

also got prompted 3x times for password on pg_upgradecluster ... wasn't able to find any "reason" or "explanation" online;
nor does postgresql documentation state that this might be expected
(might be worth denoting this in your instructions as an FYI)

Copy link

PPingot commented May 18, 2018

It also works for 9.5 to 10 upgrade

Copy link

@johanndt Thanks again this has worked liked a charm for me as well....took quiet long almost 24 hours on about 500GB but finally came through.
Those that need new link to the apt file...please see below

Copy link

With some help from the comments, this worked for me. Thanks @johanndt!

thanks @cnamuwenje for the apt file link
thanks @fermulator for the tip on killing active connections
thanks @chamnap for the env variables tip

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