Migrate from PostgreSQL 9.4 to 9.5 with easy steps

Published on Mar. 27, 2017 by Gabriel Bordeaux

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.

Migration

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.