Migrate to PostgreSQL 9.6 with pg_upgrade

Published on Nov. 20, 2017 by Gabriel Bordeaux


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 \
 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 \

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

# 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:


Remove the old version

# Back to root

# 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.