Easy migrating from PostgreSQL 9.3 to 9.4 for Debian servers

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction

PostgreSQL 9.4 was released on 12/18/2014. More information about the release is available on the official PostgreSQL documentation. This article is written to provide an easy migration solution from PostgreSQL 9.3 to 9.4 for Debian servers.

Migration

Migration from fish bowl 9.3 to fish bowl 9.4

Backup the current database

sudo bash # root
su postgres # postgres
cd /tmp # go to tmp dir
pg_dumpall > BkpOldPG.sql # backup
 
exit # go back to user root
cp /tmp/BkpOldPG.sql /home/BkpOldPG.sql # create another copy of the backup outside /tmp

Install the new version packages

apt-get update
apt-get upgrade
apt-get install postgresql-9.4

Stop the PostgreSQL server

/etc/init.d/postgresql stop

Migrate the configuration files from the old version to the new version

This step is ONLY necessary if you made any changes to the PostgreSQL configuration files (like pg_hba.conf and postgresql.conf) and if you would like to keep these changes.

su postgres
 
# Backup default config files before to replace them
cd /etc/postgresql/9.4/main/ # go to the new PG dir
cp pg_hba.conf pg_hba.conf_default # backup pg_hba.conf
cp postgresql.conf postgresql.conf_default # backup postgresql.conf
 
# Replace new config files with old ones
cp ../../9.3/main/pg_hba.conf ./ # copy pg_hba.conf from old version
cp ../../9.3/main/postgresql.conf ./ # copy postgresql.conf from old version
 
# Replaces references to the old version (9.3) in the replaced config files
sed -i 's/9.3/9.4/g' postgresql.conf
 
# Check the replacement
grep '9.' postgresql.conf
# Output should look like:
# data_directory = '/var/lib/postgresql/9.4/main'# use data in another directory
# hba_file = '/etc/postgresql/9.4/main/pg_hba.conf'# host-based authentication file
# ident_file = '/etc/postgresql/9.4/main/pg_ident.conf'# ident configuration file
# external_pid_file = '/var/run/postgresql/9.4-main.pid'# write an extra PID file

Change the configuration of the old version to keep it from starting

emacs /etc/postgresql/9.3/main/start.conf

# Replace the last line "auto" by "disabled".

Try to restart PostgreSQL

# If you start PostgreSQL now you should see:
/etc/init.d/postgresql start
# [ ok ] Starting PostgreSQL 9.3 database server:.
# [ ok ] Starting PostgreSQL 9.4 database server: main. 

Restore the backups from the old version

su postgres
cd /tmp
/usr/lib/postgresql/9.4/bin/psql -d postgres -f BkpOldPG.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/BkpOldPG.sql

Check the new version

Logon a PostgreSQL database and check the server version. You should see 9.4.

postgres=# SHOW SERVER_VERSION;
 server_version 
----------------
 9.4.0
(1 row)

postgres=# 

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.3                     9.3.5-2.pgdg70+1              amd64        object-relational SQL database, version 9.3 server
# ii  postgresql-9.3-ip4r                2.0.2-2.pgdg70+1              amd64        IPv4 and IPv6 types for PostgreSQL 9.3
# ii  postgresql-9.3-postgis-2.1         2.1.4+dfsg-1.pgdg70+3         amd64        Geographic objects support for PostgreSQL 9.3
# ii  postgresql-9.3-postgis-scripts     2.1.4+dfsg-1.pgdg70+3         all          Geographic objects support for PostgreSQL 9.3 -- scripts
# ii  postgresql-9.4                     9.4.0-1.pgdg70+1              amd64        object-relational SQL database, version 9.4 server
# ii  postgresql-9.4-ip4r                2.0.2-2.pgdg70+1              amd64        IPv4 and IPv6 types for PostgreSQL 9.4
# ii  postgresql-9.4-postgis-2.1         2.1.4+dfsg-1.pgdg70+3         amd64        Geographic objects support for PostgreSQL 9.4
# ii  postgresql-9.4-postgis-scripts     2.1.4+dfsg-1.pgdg70+3         all          Geographic objects support for PostgreSQL 9.4 -- scripts
# ii  postgresql-client-9.3              9.3.5-2.pgdg70+1              amd64        front-end programs for PostgreSQL 9.3
# ii  postgresql-client-9.4              9.4.0-1.pgdg70+1              amd64        front-end programs for PostgreSQL 9.4
# ii  postgresql-client-common           164.pgdg70+2                  all          manager for multiple PostgreSQL client versions
# ii  postgresql-common                  164.pgdg70+2                  all          PostgreSQL database-cluster manager
# ii  postgresql-contrib-9.3             9.3.5-2.pgdg70+1              amd64        additional facilities for PostgreSQL
# ii  postgresql-contrib-9.4             9.4.0-1.pgdg70+1              amd64        additional facilities for PostgreSQL
 
# We now just have to remove all the packages relative to 9.3
apt-get remove postgresql-9.3 postgresql-9.3-ip4r postgresql-9.3-postgis-2.1 postgresql-9.3-postgis-scripts postgresql-client-9.3 postgresql-contrib-9.3
# The list might depend with your installation.

Extend your knowledge

There are other migration methods described in PostgreSQL's documentation which you can read here.