Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Small pg_upgrade HowTo for Debian and Ubuntu for PG 9.1 to 9.3 (also works for other versions as well)
# Probably excessive, but it makes these instructions simpler
sudo -i
# Add postgresql repo and update apt listing
echo "deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main" > /etc/apt/sources.list.d/pgdg.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update
# For some reason this is necessary with PostgreSQL on Ubuntu 12.04
update-alternatives --remove postmaster.1.gz /usr/share/postgresql/9.1/man/man1/postmaster.1.gz
apt-get install postgresql-9.3 pgadmin3 postgresql-contrib-9.3 # contrib is needed for hstore
# Before stopping the clusters, check the LC Collation of your running 9.1 instance and export this while recreating 9.3 main
# More Infos: https://www.sysorchestra.com/2014/07/28/installchangecheck-locale-for-new-or-productive-postgresql-cluster/
su - postgres
psql -p 5432 -c "\l" # check the collation column and note that (for ex. de_DE.UTF8)
psql -p 5433 -c "\l" # If the collation is the same, continue. If not, recreate the 9.3 cluster with the following commands
# Recreate 9.3 cluster with collation from old 9.1 cluster (NOT NEEDED IF BOTH ALREADY HAVE THE SAME!)
pg_ctlcluster 9.3 main stop
pg_dropcluster 9.3 main
export LC_ALL="<old-clusters-collation>" # For ex. LC_ALL="de_DE.UTF8"
pg_createcluster 9.3 main
# Stop all running postgresql servers -- needed for migration of 9.1 data to 9.3 (pg_upgrade execution)
/etc/init.d/postgresql stop
# Must link conf file into data directory since it's expected there by pg_upgrade.
# Unfortunately, Ubuntu places it in /etc default (which complicates the upgrade)
ln -s /etc/postgresql/9.1/main/postgresql.conf /var/lib/postgresql/9.1/main/postgresql.conf
ln -s /etc/postgresql/9.3/main/postgresql.conf /var/lib/postgresql/9.3/main/postgresql.conf
# Run the pg_upgrade, but as the postgres user instead of root.
su postgres
cd ~postgres
/usr/lib/postgresql/9.3/bin/pg_upgrade -d /var/lib/postgresql/9.1/main -D /var/lib/postgresql/9.3/main -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.3/bin -k -v
# Hopefully upgrade finished without error! In which case, we can start up PostgreSQL...
/etc/init.d/postgresql start
@martinseener

This comment has been minimized.

Copy link
Owner Author

@martinseener martinseener commented Aug 14, 2014

This also works great on Debian

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