Last active
June 21, 2018 10:35
-
-
Save mdpatrick/8895813 to your computer and use it in GitHub Desktop.
Upgrade PostgreSQL from 9.1 to 9.3 on Ubuntu 12.04
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 is exactly what I need to do, but not getting very far with these instructions.
response to line 7 (apt-get update).....
N: Ignoring file 'pgdg.' in directory '/etc/apt/sources.list.d/' as it has an invalid filename extension
W: GPG error: http://cran.stat.ucla.edu precise/ Release: The following signatures were invalid: KEYEXPIRED 1445181253 KEYEXPIRED 1445181253 KEYEXPIRED 1445181253
response to line 11 (apt-get install postgresql-9.3 pgadmin3 postgresql-contrib-9.3)......
Reading package lists... Done
Building dependency tree
Reading state information... Done
pgadmin3 is already the newest version.
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
postgresql-contrib-9.1 : Depends: postgresql-9.1 (= 9.1.24-1.pgdg12.4+1) but 9.1.23-0ubuntu0.12.04 is to be installed
N: Ignoring file 'pgdg.' in directory '/etc/apt/sources.list.d/' as it has an invalid filename extension
response to line 17 (psql -p 5433 -c "\l")...
$ psql -p 5433 "\l"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5433"?
result...
postgresql is downloaded to:
/usr/share/postgresql/9.3
/usr/include/postgresql/9.3
any ideas?