Migrate to PostgreSQL 9.6 with pg_upgrade
Introduction
You can migrate from a version of PostgreSQL to another with pg_dump (see my article on how to migrate from 9.4 ro 9.5 as a reference) or with pg_upgrade. pg_upgrade allows you to migrate with or without a copy of the files ("--link" option). The latest option used below is optimal when you don't have enough disk space to copy the database. pg_upgrade allows a migration with minimal downtime and is the best solution for high availability applications and large datasets.
Install PostgreSQL 9.6 on the server
apt-get update
apt-get upgrade
apt-get install -y postgresql-9.6
Create configurations
Look at the differences in confirmation files and replicate manually all personalized configuration from postgresql.conf and pg_hba.conf:
diff /etc/postgresql/9.5/main/postgresql.conf /etc/postgresql/9.6/main/postgresql.conf
# Replicate manually personalized configuration
diff /etc/postgresql/9.5/main/pg_hba.conf /etc/postgresql/9.6/main/pg_hba.conf
# Replicate manually personalized configuration
Create configuration symlinks
By default, pg_upgrade will search for postgresql.conf in "/var/lib/postgresql/9.X/main/". On Ubuntu and Debian, these files are stored in "/etc/postgresql/9.X/main/". An easy fix is to create 2 symlinks in these directories:
su postgres
postgres@db:/root$ ln -s /etc/postgresql/9.5/main/postgresql.conf /var/lib/postgresql/9.5/main/
postgres@db:/root$ ln -s /etc/postgresql/9.6/main/postgresql.conf /var/lib/postgresql/9.6/main/
Stop PostgreSQL clusters
postgres@db:/root$ /usr/lib/postgresql/9.5/bin/pg_ctl -D /var/lib/postgresql/9.5/main stop
postgres@db:/root$ /usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main stop
Test the migration
Before running the actual migration, it is recommended to test it with the "--check" flag:
postgres@db:/root$ cd /var/lib/postgresql
postgres@db:~$ /usr/lib/postgresql/9.6/bin/pg_upgrade --check --link \
--old-datadir=/var/lib/postgresql/9.5/main \
--new-datadir=/var/lib/postgresql/9.6/main \
--old-bindir=/usr/lib/postgresql/9.5/bin \
--new-bindir=/usr/lib/postgresql/9.6/bin
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for roles starting with 'pg_' ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
*Clusters are compatible*
If the output is valid, it is safe to proceed. If there is an error, have a look at "pg_upgrade_server.log".
Migrate data
Migrate the cluster with:
postgres@db:~$ /usr/lib/postgresql/9.6/bin/pg_upgrade --link \
--old-datadir=/var/lib/postgresql/9.5/main \
--new-datadir=/var/lib/postgresql/9.6/main \
--old-bindir=/usr/lib/postgresql/9.5/bin \
--new-bindir=/usr/lib/postgresql/9.6/bin
PostgreSQL 9.6 port
In /etc/postgresql/9.6/main/postgresql.conf, the connection port will be 5433:
listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5433 # (change requires restart)
Your previous cluster will typically use 5432. You can either replace the port in "postgresql.conf" with 5432 and make sure to start only the new cluster or change the port in your application to 5433.
Start PostgreSQL 9.6
# Start the new cluster
postgres@db:~ $ /usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main start
# Verify version
postgres@db:~$ psql -c "SELECT version();"
# version
#------------------------------------------------------------------------------------------
# PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
#(1 row)
# Analyze the new cluster
./analyze_new_cluster.sh
# Optionally vacuum all databases
/usr/lib/postgresql/9.6/bin/vacuumdb --all --analyze-only
#vacuumdb: vacuuming database "postgres"
#vacuumdb: vacuuming database "template1"
#vacuumdb: vacuuming database "test"
Delete old cluster
Once you ensure that the new cluster is working correctly, you can safely delete the old one:
./delete_old_cluster.sh
Remove the old version
# Back to root
exit
# List all packages installed for PostgreSQL:
dpkg -l | grep postgresql
#ii pgdg-keyring 2014.1 all keyring for apt.postgresql.org
#rc postgresql-9.5 9.5.4-1.pgdg80+2 amd64 object-relational SQL database, version 9.5 server
#ii postgresql-9.6 9.6.0-1.pgdg80+1 amd64 object-relational SQL database, version 9.6 server
#ii postgresql-9.6-ip4r 2.1.1-1.pgdg80+1 amd64 IPv4 and IPv6 types for PostgreSQL 9.6
#ii postgresql-client-9.6 9.6.0-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.6
#ii postgresql-client-common 177.pgdg80+1 all manager for multiple PostgreSQL client versions
#ii postgresql-common 177.pgdg80+1 all PostgreSQL database-cluster manager
#ii postgresql-contrib-9.6 9.6.0-1.pgdg80+1 amd64 additional facilities for PostgreSQL
# We now just have to remove all the packages relative to 9.5
apt-get remove postgresql-9.5
# Cleanup unused packages
apt-get autoremove
# The list might depend on your installation.