Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@nugged

This comment has been minimized.

nugged commented Feb 22, 2016

perfect doc. As well as TL;DR :). 👍
worked seamlessly

@KarishmaGhiya

This comment has been minimized.

KarishmaGhiya commented Mar 10, 2016

Thank you so much. It is really helpful.

@messyOne

This comment has been minimized.

messyOne commented Mar 23, 2016

Was prepared to spend my evening getting the new version running. Was finished in couple of minutes. Thanks.

@basiszwo

This comment has been minimized.

basiszwo commented Mar 29, 2016

Thank you very much! The only thing I had to do in addition to what you wrote was to manually stop the 9.3 instance.

@rherrick

This comment has been minimized.

rherrick commented Apr 9, 2016

One note on something that can bite you. When you go to upgrade the cluster, make sure there are no active client connections! This includes open psql clients, service applications like Tomcat (which is what happened to me: Puppet automatically restarted Tomcat while I was doing something else!), or anything like that. If there is an active client connection, you'll end up with a message like this:

root@gumby:~# pg_upgradecluster 9.3 main 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

Thanks for the post, saved me a ton of time figuring out the upgrade process!

@Fokusnica

This comment has been minimized.

Fokusnica commented Apr 20, 2016

Really helpful! Thanks.

@thelongrun

This comment has been minimized.

thelongrun commented May 16, 2016

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.

@easybills-admin

This comment has been minimized.

easybills-admin commented May 29, 2016

Amazing, thank you very much! 😄

@williscool

This comment has been minimized.

williscool commented Jun 5, 2016

👍

@ImTheDeveloper

This comment has been minimized.

ImTheDeveloper commented Jul 16, 2016

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

@bistaray

This comment has been minimized.

bistaray commented Jul 23, 2016

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

@passmore

This comment has been minimized.

passmore commented Aug 11, 2016

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

This comment has been minimized.

Nidhoggur1993 commented Aug 17, 2016

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

@salmagomaa

This comment has been minimized.

salmagomaa commented Aug 21, 2016

Thanks. Its perfect!

@niborg

This comment has been minimized.

niborg commented Aug 27, 2016

pithy and perfect!

@captainpete

This comment has been minimized.

captainpete commented Aug 28, 2016

Thanks!!
🌷 🐢

@aasaandinesh

This comment has been minimized.

aasaandinesh commented Aug 30, 2016

Thanks a lot. Perfect!

@nathanielks

This comment has been minimized.

nathanielks commented Oct 7, 2016

thanks @johanndt!

@nmenardg

This comment has been minimized.

nmenardg commented Oct 27, 2016

@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

This comment has been minimized.

werk commented Nov 24, 2016

Great, thanks

@PirunSeng

This comment has been minimized.

PirunSeng commented Dec 22, 2016

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-

This comment has been minimized.

w- commented Dec 29, 2016

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

@chamnap

This comment has been minimized.

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

This comment has been minimized.

kevin-george commented Jan 7, 2017

The url given by nmenardg works!

@inyerade

This comment has been minimized.

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

This comment has been minimized.

senolsun commented Mar 8, 2017

these instructions just "work". no mess involved

@ma0c

This comment has been minimized.

ma0c commented Mar 25, 2017

+1

@ryanlederman

This comment has been minimized.

ryanlederman 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

This comment has been minimized.

fermulator commented Apr 6, 2017

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

This comment has been minimized.

obabawale commented Aug 17, 2017

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

This comment has been minimized.

karlingen commented Mar 5, 2018

THAAANK YOOOUUU!!! <3

@fermulator

This comment has been minimized.

fermulator commented Mar 28, 2018

+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

This comment has been minimized.

PPingot commented May 18, 2018

It also works for 9.5 to 10 upgrade

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