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.

Show comment
Hide comment
@nugged

nugged Feb 22, 2016

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

nugged commented Feb 22, 2016

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

@KarishmaGhiya

This comment has been minimized.

Show comment
Hide comment
@KarishmaGhiya

KarishmaGhiya Mar 10, 2016

Thank you so much. It is really helpful.

KarishmaGhiya commented Mar 10, 2016

Thank you so much. It is really helpful.

@messyOne

This comment has been minimized.

Show comment
Hide comment
@messyOne

messyOne Mar 23, 2016

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

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.

Show comment
Hide comment
@basiszwo

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

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.

Show comment
Hide comment
@rherrick

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

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.

Show comment
Hide comment
@Fokusnica

Fokusnica Apr 20, 2016

Really helpful! Thanks.

Fokusnica commented Apr 20, 2016

Really helpful! Thanks.

@thelongrun

This comment has been minimized.

Show comment
Hide comment
@thelongrun

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

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.

Show comment
Hide comment
@easybills-admin

easybills-admin May 29, 2016

Amazing, thank you very much! 😄

easybills-admin commented May 29, 2016

Amazing, thank you very much! 😄

@williscool

This comment has been minimized.

Show comment
Hide comment
@williscool

williscool commented Jun 5, 2016

👍

@ImTheDeveloper

This comment has been minimized.

Show comment
Hide comment
@ImTheDeveloper

ImTheDeveloper Jul 16, 2016

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

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.

Show comment
Hide comment
@bistaray

bistaray 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

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.

Show comment
Hide comment
@passmore

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

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.

Show comment
Hide comment
@Nidhoggur1993

Nidhoggur1993 Aug 17, 2016

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

Nidhoggur1993 commented Aug 17, 2016

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

@salmagomaa

This comment has been minimized.

Show comment
Hide comment
@salmagomaa

salmagomaa Aug 21, 2016

Thanks. Its perfect!

salmagomaa commented Aug 21, 2016

Thanks. Its perfect!

@niborg

This comment has been minimized.

Show comment
Hide comment
@niborg

niborg Aug 27, 2016

pithy and perfect!

niborg commented Aug 27, 2016

pithy and perfect!

@captainpete

This comment has been minimized.

Show comment
Hide comment
@captainpete

captainpete Aug 28, 2016

Thanks!!
🌷 🐢

captainpete commented Aug 28, 2016

Thanks!!
🌷 🐢

@aasaandinesh

This comment has been minimized.

Show comment
Hide comment
@aasaandinesh

aasaandinesh Aug 30, 2016

Thanks a lot. Perfect!

aasaandinesh commented Aug 30, 2016

Thanks a lot. Perfect!

@nathanielks

This comment has been minimized.

Show comment
Hide comment
@nathanielks

nathanielks commented Oct 7, 2016

thanks @johanndt!

@nmenardg

This comment has been minimized.

Show comment
Hide comment
@nmenardg

nmenardg 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

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.

Show comment
Hide comment
@werk

werk Nov 24, 2016

Great, thanks

werk commented Nov 24, 2016

Great, thanks

@PirunSeng

This comment has been minimized.

Show comment
Hide comment
@PirunSeng

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

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.

Show comment
Hide comment
@w-

w- Dec 29, 2016

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

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.

Show comment
Hide comment
@chamnap

chamnap Jan 3, 2017

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

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.

Show comment
Hide comment
@kevin-george

kevin-george Jan 7, 2017

The url given by nmenardg works!

kevin-george commented Jan 7, 2017

The url given by nmenardg works!

@inyerade

This comment has been minimized.

Show comment
Hide comment
@inyerade

inyerade 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

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.

Show comment
Hide comment
@senolsun

senolsun Mar 8, 2017

these instructions just "work". no mess involved

senolsun commented Mar 8, 2017

these instructions just "work". no mess involved

@ma0c

This comment has been minimized.

Show comment
Hide comment
@ma0c

ma0c commented Mar 25, 2017

+1

@ryanlederman

This comment has been minimized.

Show comment
Hide comment
@ryanlederman

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

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.

Show comment
Hide comment
@fermulator

fermulator 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

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.

Show comment
Hide comment
@obabawale

obabawale 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?

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.

Show comment
Hide comment
@karlingen

karlingen Mar 5, 2018

THAAANK YOOOUUU!!! <3

karlingen commented Mar 5, 2018

THAAANK YOOOUUU!!! <3

@fermulator

This comment has been minimized.

Show comment
Hide comment
@fermulator

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

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.

Show comment
Hide comment
@PPingot

PPingot May 18, 2018

It also works for 9.5 to 10 upgrade

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