Migrate to PostgreSQL 10 with pg_upgrade
Introduction
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.
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();"
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
./analyze_new_cluster.sh
# 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:
./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.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.