Skip to content

Instantly share code, notes, and snippets.

@ronicthomas

ronicthomas/postgres-upgrade-9.3-to-9.6.txt

Created June 24, 2020 16:40
Show Gist options
  • Save ronicthomas/bc20bd875eae5d55830d8add3dca38c4 to your computer and use it in GitHub Desktop.
Save ronicthomas/bc20bd875eae5d55830d8add3dca38c4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgres-upgrade-9.3-to-9.6.txt
# Add postgres deb sources
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
# Add GPG key
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
# Update packages & details
sudo apt-get update
# Install postgres
sudo apt-get install postgresql-9.6 postgresql-server-dev-9.6 postgresql-contrib-9.6 -y
# OPTIONAL STEPS START
# At this point, modify /etc/postgresql/9.6/postgresql.conf to point to your new data directory
# Also copy /var/lib/postgresql/9.6/main to the new location
# Restart postgres after this change
sudo service postgresql stop
sudo sed -i "s+/var/lib/postgresql/9.6/main+/mnt/custom_dir/9.6/main+g" /etc/postgresql/9.6/postgresql.conf
mkdir -p /mnt/custom_dir/
cp -R /var/lib/postgresql/9.6 /mnt/custom_dir/
chown -R postgres:postgres /mnt/custom_dir/9.6
sudo service postgresql start
# OPTIONAL STEPS END
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS hstore;'"
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";'"
sudo service postgresql stop
sudo su - postgres -c '/usr/lib/postgresql/9.6/bin/pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.6/bin \
-d /mnt/custom_dir/9.6/main/ -D /mnt/custom_dir/9.6/main/ \
-O "-c config_file=/etc/postgresql/9.6/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" --link'
sudo apt-get remove postgresql-9.3 -y
sudo sed -i "s:5433:5432:g" /etc/postgresql/9.6/main/postgresql.conf
sudo service postgresql start
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment