Migrate from PostgreSQL 9.4 to 9.5 with easy steps
Introduction
PostgreSQL 9.5 was released on 01/07/2016. A list of new and upgraded features is available on the official documentation. The guide below provides a step by step documentation to migrate from PostgreSQL 9.4 to PostgreSQL 9.5 on Debian / Ubuntu servers.
Birds migrating from PostgreSQL 9.4 to 9.5
Backup the current database
sudo su # become root
su postgres # postgres
pg_dumpall > /tmp/bkp.sql # backup
exit # go back to user root
# Create a copy of the backup
cp /tmp/bkp.sql /home/bkp.sql
Install PostgreSQL 9.5 on the server
If you are switching servers, you can install PostgreSQL 9.5 in a few easy steps.
apt-get update
apt-get upgrade
apt-get install -y postgresql-9.5
Stop the PostgreSQL server
/etc/init.d/postgresql stop
Migrate the configuration files from the old version to the new version
Copy the configuration files from PostgreSQL 9.4 to 9.5:
su postgres
# Define Vars
PG_94="/etc/postgresql/9.4/main"
PG_95="/etc/postgresql/9.5/main"
# Backup default configs
cp $PG_95/pg_hba.conf $PG_95/pg_hba.conf.bak # backup pg_hba.conf
cp $PG_95/postgresql.conf $PG_95/postgresql.conf.bak # backup postgresql.conf
# Copy old configs to new version
cp $PG_94/pg_hba.conf $PG_95/ # copy pg_hba.conf from old version
cp $PG_94/postgresql.conf $PG_95/ # copy postgresql.conf from old version
# Replace references from the old version (9.4) in the config files
sed -i 's/9.4/9.5/g' $PG_95/postgresql.conf
# Check the changes
grep -rF --exclude="*.bak" '9.' $PG_95/
# Output should look like:
# /etc/postgresql/9.5/main/postgresql.conf:data_directory = '/var/lib/postgresql/9.5/main'# use data in another directory
# /etc/postgresql/9.5/main/postgresql.conf:hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'# host-based authentication file
# /etc/postgresql/9.5/main/postgresql.conf:ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'# ident configuration file
# /etc/postgresql/9.5/main/postgresql.conf:external_pid_file = '/var/run/postgresql/9.5-main.pid'# write an extra PID file
# /etc/postgresql/9.5/main/postgresql.conf:stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
Keep PostgreSQL 9.4 from starting
sed -i 's/auto/disabled/g' $PG_94/start.conf
Try restarting PostgreSQL
exit # Back to root
/etc/init.d/postgresql start
# [ ok ] Starting postgresql (via systemctl): postgresql.service.
Restore PostgreSQL 9.4's backup
su postgres
/usr/lib/postgresql/9.5/bin/psql -d postgres -f /tmp/bkp.sql
# In case you rebooted the server and lost the content of /tmp, you can use the copy of the backup that we made in /home/bkp.sql
Restart PostgreSQL and check the version
Login to PostgreSQL:
psql
Check the version and view the restored tables:
SELECT version();
# PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
# List tables
\d
# List of relations
# Schema | Name | Type | Owner
# --------+------+-------+----------
# public | tata | table | postgres
# public | toto | table | postgres
# (2 rows)
Remove the old version
# List all packages installed for PostgreSQL:
dpkg -l | grep postgresql
# ii pgdg-keyring 2014.1 all keyring for apt.postgresql.org
# ii postgresql-9.4 9.4.5-2.pgdg80+1 amd64 object-relational SQL database, version 9.4 server
# ii postgresql-9.4-ip4r 2.0.2-4.pgdg80+1 amd64 IPv4 and IPv6 types for PostgreSQL 9.4
# ii postgresql-9.4-postgis-2.1 2.1.8+dfsg-5~97.git43a09cc.pgdg80+1 amd64 Geographic objects support for PostgreSQL 9.4
# ii postgresql-9.4-postgis-scripts 2.1.8+dfsg-5~97.git43a09cc.pgdg80+1 all Geographic objects support for PostgreSQL 9.4 -- scripts
# ii postgresql-9.5 9.5.0-1.pgdg80+2 amd64 object-relational SQL database, version 9.5 server
# ii postgresql-client-9.4 9.4.5-2.pgdg80+1 amd64 front-end programs for PostgreSQL 9.4
# ii postgresql-client-9.5 9.5.0-1.pgdg80+2 amd64 front-end programs for PostgreSQL 9.5
# ii postgresql-client-common 172.pgdg80+1 all manager for multiple PostgreSQL client versions
# ii postgresql-common 172.pgdg80+1 all PostgreSQL database-cluster manager
# ii postgresql-contrib-9.4 9.4.5-2.pgdg80+1 amd64 additional facilities for PostgreSQL
# ii postgresql-contrib-9.5 9.5.0-1.pgdg80+2 amd64 additional facilities for PostgreSQL
# We now just have to remove all the packages relative to 9.4
apt-get remove postgresql-9.4 postgresql-9.4-ip4r postgresql-9.4-postgis-2.1 postgresql-9.4-postgis-scripts \
postgresql-client-9.4 postgresql-contrib-9.4
# Cleanup unused packages
apt-get autoremove
# The list might depend on your installation.