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

TL;DR

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:

wget https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh
chmod a+x apt.postgresql.org.sh
sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g' apt.postgresql.org.sh
sudo ./apt.postgresql.org.sh

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

pg_lsclusters 
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".

pg_lsclusters 
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
@Fokusnica
Copy link

Really helpful! Thanks.

@bhatiavivek
Copy link

Perfect.

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

@williscool
Copy link

👍

@ImTheDeveloper
Copy link

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

@bistaray
Copy link

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

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

@Nidhoggur1993
Copy link

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

@salmagomaa
Copy link

Thanks. Its perfect!

@niborg
Copy link

niborg commented Aug 27, 2016

pithy and perfect!

@captainpete
Copy link

Thanks!!
🌷 🐢

@aasaandinesh
Copy link

Thanks a lot. Perfect!

@nathanielks
Copy link

thanks @johanndt!

@nmenardg
Copy link

@johanndt the first link for apt.postgresql.org.sh responds with 404.
A quick search on postgresql's wiki points me to https://anonscm.debian.org/cgit/pkg-postgresql/postgresql-common.git/plain/pgdg/apt.postgresql.org.sh
Are they the same? If so, you should update this gist

@werk
Copy link

werk commented Nov 24, 2016

Great, thanks

@PirunSeng
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?

@w-
Copy link

w- commented Dec 29, 2016

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

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

@kevin-george
Copy link

The url given by nmenardg works!

@inyerade
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/PgCommon.pm line 925.
psql: FATAL:  the database system is shutting down
Use of uninitialized value $out in pattern match (m//) at /usr/share/perl5/PgCommon.pm line 931.
Use of uninitialized value $ctype in scalar chomp at /usr/share/perl5/PgCommon.pm line 934.
Use of uninitialized value $collate in scalar chomp at /usr/share/perl5/PgCommon.pm line 935.

I can run again pg_upgradecluster 9.3 main after running

sudo service postgresql restart

@senolsun
Copy link

senolsun commented Mar 8, 2017

these instructions just "work". no mess involved

@ma0c
Copy link

ma0c commented Mar 25, 2017

+1

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

@fermulator
Copy link

This link has a slightly more concise note regarding how to deal with active connections.
https://gist.github.com/hvanhonacker/5da4d97e5aff49f23a21

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

@obabawale
Copy link

Hello,
Thanks for such a wonderful article. I tried to access the following link (wget https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh) you posted above but I learned it doesn't exist any more. Can I get a new link?

@karlingen
Copy link

THAAANK YOOOUUU!!! <3

@fermulator
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)

@PPingot
Copy link

PPingot commented May 18, 2018

It also works for 9.5 to 10 upgrade

@cnamuwenje
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
wget https://gist.github.com/petere/5893799/raw/d1b3a4a7e92e397e2c323ca9ce93ec958dcf6474/apt.postgresql.org.sh

@dmarcelino
Copy link

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

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