Skip to content

Instantly share code, notes, and snippets.

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

@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