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

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