Table of Contents

About

If you wish to switch from older PostgreSQL version to new one it is required to perform database cluster upgrade. Below is short instruction on how to proceed. We assume that you are upgrading from PostgreSQL 10 to 11 using default clusters (main).

Preparation

Before you'll try to upgrade PostgreSQL cluster you need to perform system upgrade to be sure that all required PostgreSQL libraries are up to date. First, use dry run to see what will be upgraded:

poldek --up -t -v --upgrade-dist

If everything seems ok perform an upgrade:

poldek -v --upgrade-dist

Now install new PostgreSQL packages (if you require modules or contrib package install them as well):

poldek -i -v -h postgresql-11 postgresql-11-clients

Initialize new PostgreSQL 11 cluster. If you need to create cluster with different locale (default is C) please adjust your configuration in /etc/sysconfig/postgresql-11:

service postgresql-11 init

Stop existing PostgreSQL 10 cluster that you wish to upgrade:

service postgresql-10 stop

Make sure that nothing can access your database. Usually stopping all services/apps using database is not an option so you'll need to temporarily disconnect database host from network or prevent connections on firewall.

Edit pg_hba.conf of PostgreSQL 10 cluster and temporarily set authentication method to trust so you don't have to provide password during upgrade.

Upgrade

PostgreSQL comes with special tool for performing cluster upgrade - pg_upgrade. It requires to be run under postgres user so lets login to postgres account:

su - postgres

Run pg_upgrade (if you are running 32-bit system, replace /usr/lib64 with /usr/lib):

/usr/lib64/postgresql/11/bin/pg_upgrade -b /usr/lib64/postgresql/10/bin/ -B /usr/lib64/postgresql/11/bin/ -d /var/lib/postgresql/10/main/ -D /var/lib/postgresql/11/main/

Sometimes upgrade may fail if there are some changes in new version that break backward compatibility. In such cases you'll need to fix all issuses in existing PostgreSQL 10 cluster, drop and recreate PostgreSQL 11 cluster and try to run pg_upgrade again.

If upgrade was successfull remember to update your postgresql.conf and pg_hba.conf in /var/lib/postgresql/11/main so they reflect configuration of old PostgreSQL 10 cluster. Also do not forget to change authentication method from trust to whatever you are using.

© TLD Linux