Easy migrating from PostgreSQL 9.3 to 9.4 for Debian servers
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 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.