Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Upgrading PostgreSQL from 9.3 to 9.4 when upgrading Ubuntu 14.04 to 14.10

TL;DR

Create a backup:

pg_dumpall > mybackup.sql

Perform the upgrade:

sudo pg_dropcluster 9.4 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

While upgrading Ubuntu 14.04 to 14.10, I got the following warning message:

The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster

Once the Ubuntu upgrade 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.4                                   - object-relational SQL database, version 9.4 server
i A postgresql-client-9.3                            - front-end programs for PostgreSQL 9.3
i A postgresql-client-9.4                            - front-end programs for PostgreSQL 9.4
i A postgresql-contrib-9.3                           - additional facilities for PostgreSQL
i A postgresql-contrib-9.4                           - additional facilities for PostgreSQL

Looks like the Ubuntu upgrade included PostgreSQL 9.4, but I still need to upgrade from 9.3 to 9.4.

Run pg_lsclusters, your 9.3 and 9.4 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.4 main    5433 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

There already is a cluster "main" for 9.4 (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.4/main also exists. The recommended procedure is to remove the 9.4 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.4 cluster and drop it.

sudo pg_dropcluster 9.4 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.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Check that the upgraded cluster works, then remove the 9.3 cluster.

sudo pg_dropcluster 9.3 main
@tony-caffe

This comment has been minimized.

Show comment Hide comment
@tony-caffe

tony-caffe Apr 15, 2015

Looks good.

Looks good.

@devton

This comment has been minimized.

Show comment Hide comment
@devton

devton Apr 19, 2015

nice!

devton commented Apr 19, 2015

nice!

@jamescgibson

This comment has been minimized.

Show comment Hide comment
@jamescgibson

jamescgibson May 10, 2015

Thank you!

Thank you!

@zacharystenger

This comment has been minimized.

Show comment Hide comment
@zacharystenger

zacharystenger May 19, 2015

Super helpful, thanks!

Super helpful, thanks!

@scigghia

This comment has been minimized.

Show comment Hide comment
@scigghia

scigghia Jun 16, 2015

πŸ‘

πŸ‘

@sdvcrx

This comment has been minimized.

Show comment Hide comment
@sdvcrx

sdvcrx Jun 30, 2015

Thanks!

sdvcrx commented Jun 30, 2015

Thanks!

@mro

This comment has been minimized.

Show comment Hide comment
@mro

mro Aug 8, 2015

πŸ‘

mro commented Aug 8, 2015

πŸ‘

@stereodenis

This comment has been minimized.

Show comment Hide comment
@stereodenis

stereodenis Sep 4, 2015

πŸ‘

πŸ‘

@iagopiimenta

This comment has been minimized.

Show comment Hide comment
@iagopiimenta

iagopiimenta Sep 6, 2015

πŸ‘

πŸ‘

@mzatko

This comment has been minimized.

Show comment Hide comment
@mzatko

mzatko Oct 26, 2015

great! Thank you!

mzatko commented Oct 26, 2015

great! Thank you!

@boblail

This comment has been minimized.

Show comment Hide comment
@boblail

boblail Nov 8, 2015

thanks! πŸ‘

boblail commented Nov 8, 2015

thanks! πŸ‘

@jgrocha

This comment has been minimized.

Show comment Hide comment
@jgrocha

jgrocha Nov 30, 2015

Great! But I need to stop 9.3 before the pg_upgradecluster.

sudo pg_dropcluster 9.4 main --stop
sudo /etc/init.d/postgresql stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

jgrocha commented Nov 30, 2015

Great! But I need to stop 9.3 before the pg_upgradecluster.

sudo pg_dropcluster 9.4 main --stop
sudo /etc/init.d/postgresql stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main
@petrikoz

This comment has been minimized.

Show comment Hide comment
@petrikoz

petrikoz Feb 26, 2016

thanks! πŸ‘

This work for 9.5 too

thanks! πŸ‘

This work for 9.5 too

@JChrist

This comment has been minimized.

Show comment Hide comment
@JChrist

JChrist Mar 19, 2016

πŸ‘ same steps for upgrading from 9.4 to 9.5

JChrist commented Mar 19, 2016

πŸ‘ same steps for upgrading from 9.4 to 9.5

@tech-vsilva

This comment has been minimized.

Show comment Hide comment
@tech-vsilva

tech-vsilva Jul 22, 2016

Thank you!

Thank you!

@taryneast

This comment has been minimized.

Show comment Hide comment
@taryneast

taryneast Dec 19, 2016

same for 9.3 to 9.6

Can I recommend that as your very first step, you run pg_dumpall > mybackup.sql so you have a backup of all your databases... especially do this before running any of the pg_dropcluster stuff... this saved my bacon.

same for 9.3 to 9.6

Can I recommend that as your very first step, you run pg_dumpall > mybackup.sql so you have a backup of all your databases... especially do this before running any of the pg_dropcluster stuff... this saved my bacon.

@TangMonk

This comment has been minimized.

Show comment Hide comment
@TangMonk

TangMonk Mar 6, 2017

If you face locale error, like this:

Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = "en_US.UTF-8",
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Error: The locale requested by the environment is invalid.
Error: Could not create target cluster

You can use following command to fix it:

export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8

TangMonk commented Mar 6, 2017

If you face locale error, like this:

Stopping old cluster...
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = "en_US.UTF-8",
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Error: The locale requested by the environment is invalid.
Error: Could not create target cluster

You can use following command to fix it:

export LC_CTYPE=en_US.UTF-8 export LC_ALL=en_US.UTF-8
@ismailakbudak

This comment has been minimized.

Show comment Hide comment
@ismailakbudak

ismailakbudak Apr 27, 2017

thanks.. πŸ‘

ismailakbudak commented Apr 27, 2017

thanks.. πŸ‘

@Kobold

This comment has been minimized.

Show comment Hide comment
@Kobold

Kobold Oct 9, 2017

πŸ‘ Thanks!

Kobold commented Oct 9, 2017

πŸ‘ Thanks!

@iremgokceyildirim

This comment has been minimized.

Show comment Hide comment
@iremgokceyildirim

iremgokceyildirim Feb 18, 2018

Thanks!

Thanks!

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