Migrate to PostgreSQL 10 with pg_upgrade

Published on Nov. 9, 2017 by Gabriel Bordeaux


You can migrate from a version of PostgreSQL to another with pg_dump 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.

PostgreSQL 10

Install PostgreSQL 10 on the server

apt-get update
apt-get install -y postgresql-10

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.6/main/postgresql.conf /etc/postgresql/10/main/postgresql.conf
# Replicate manually personalized configuration

diff /etc/postgresql/9.6/main/pg_hba.conf /etc/postgresql/10/main/pg_hba.conf
# Replicate manually personalized configuration

Stop PostgreSQL clusters

su postgres
/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main stop
/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main stop

Migrate data

Migrate the cluster with:

# Use option `--check` to do a dry run
# Use option `--link` to migrate without copying

/usr/lib/postgresql/10/bin/pg_upgrade \
 --old-datadir=/var/lib/postgresql/9.6/main \
 --new-datadir=/var/lib/postgresql/10/main \
 --old-bindir=/usr/lib/postgresql/9.6/bin \
 --new-bindir=/usr/lib/postgresql/10/bin \
 --old-options '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
 --new-options '-c config_file=/etc/postgresql/10/main/postgresql.conf'

If there is an error, have a look at "pg_upgrade_server.log".

PostgreSQL 10 port

In /etc/postgresql/10/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 10

# Start the new cluster
postgres@db:~ $ /usr/lib/postgresql/10/bin/pg_ctl\
  -D /var/lib/postgresql/10/main \
  -o '-c config_file=/etc/postgresql/10/main/postgresql.conf' start

# Verify version
postgres@db:~$ psql -c "SELECT version();"
 PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 row)

# Analyze the new cluster

# Optionally vacuum all databases
/usr/lib/postgresql/10/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.6                 9.6.4-1.pgdg80+2             amd64        object-relational SQL database, version 9.6 server
#ii  postgresql-10                 10.0-1.pgdg80+1             amd64        object-relational SQL database, version 10 server
#ii  postgresql-10-ip4r            2.1.1-1.pgdg80+1             amd64        IPv4 and IPv6 types for PostgreSQL 10
#ii  postgresql-client-10          10.0-1.pgdg80+1             amd64        front-end programs for PostgreSQL 10
#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-10         10.0-1.pgdg80+1             amd64        additional facilities for PostgreSQL

# We now just have to remove all the packages relative to 9.6
apt-get remove postgresql-9.6

# Cleanup unused packages
apt-get autoremove

# The list might depend on your installation.