Forked from mdpatrick/postgresql_9.1_to_9.3_on_ubuntu_12.04
Last active
March 14, 2017 10:17
-
-
Save martinseener/cc1ffb016817c68233a1 to your computer and use it in GitHub Desktop.
Small pg_upgrade HowTo for Debian and Ubuntu for PG 9.1 to 9.3 (also works for other versions as well)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This also works great on Debian