Table of Contents

Upgrading PostgreSQL 9.2 to 9.6

This is a tutorial for upgrading PostgreSQL database on CentOS 7 server. Upgrading your database is necessary to have it properly patched, secure and to have all DB features your applications need. Upgrade process in this article installs another PostgreSQL engine alongside the old one and migrates data. Since original data are untouched during the upgrade, this howto is also suitable for production deployments.

In this howto we upgrade from PostgreSQL 9.2 (with systemd unit postgresql.service and base directory /data/pgsql/9.2) to PostgreSQL 9.6 (with systemd unit postgresql-9.6.service and base directory /data/pgsql/9.6). However, this tutorial is suitable for migrating between other versions too, assuming you take proper precautions.

This tutorial is written for CentOS 7 and may need adjustments for other Linux distributions.

Risks and considerations

When you upgrade the PostgreSQL database where the IdM repository is located, you should take care of a few things.

Preparation and prerequisites

Before you start the upgrade, check that you have all necessary thing prepared:

Upgrade process

Turn off applications and change listening port of old postgres

Check that you have turned off all applications that use postgres(Tomcat - IdM). During the upgrade, the PostgreSQL needs to be running, but we also have to make sure that no daemon accesses the database. We ensure this by changing the TCP port the database listens on. In file /etc/systemd/system/postgresql.service set:

Environment=PGPORT=6000		

Then restart postgres and check it's listening on port 6000:

systemctl daemon-reload			
systemctl restart postgresql			
systemctl status postgresql			

netstat -nlpt | grep 6000
#expected output: 
#tcp        0      0 127.0.0.1:6000          0.0.0.0:*               LISTEN      1815/postmaster

netstat -nlpt | grep 5432
#expected output: nothing

Backup all databases on old postgres

Under user postgres, create dumps of all databases you want to preserve (in this example is database czechidm and postgres).

cd /data/postgres_upgrade/backup_postgres92	
su - postgres			
pg_dump --compress=9 --create --port=6000 czechidm > czechidm_backup.sql.gz					
pg_dump --compress=9 --create --port=6000 postgres > postgres_backup.sql.gz

Stop old postgres

systemctl stop postgresql
systemctl disable postgresql

Install new postgres

Install new postgres(example 9.6) and, create new data directory:

yum install -y postgresql96-server postgresql96-contrib pgstat2_96 pg_top96 postgresql96-libs	
	
mkdir -p /data/pgsql/9.6/data/	
chown -R postgres:postgres /data/pgsql/9.6/
chmod 700 /data/pgsql/9.6/
cp /usr/lib/systemd/system/postgresql-9.6.service /etc/systemd/system/	
During the installation yum will complain that it cannot create symlinks to postgres binaries. This is because there are still binaries from the 9.2 version. Save the list of symlinks that could not be created so you can create them manually after uninstalling the 9.2 version.

Set up new data directory for newly installed postgres.

In file /etc/systemd/system/postgresql-9.6.service set:

Environment=PGDATA=/data/pgsql/9.6/data/
Environment=PGPORT=7000

In file ~postgres/.bash_profile set:

PGDATA=/data/pgsql/9.6/data/

Reload setting and initialize new database (run all following commands as root):

systemctl daemon-reload	
/usr/pgsql-9.6/bin/postgresql96-setup initdb	
systemctl start postgresql-9.6.service	
systemctl status postgresql-9.6.service	

Upgrade the database

Stop both postgres engines:

systemctl stop postgresql-9.6.service		
systemctl stop postgresql		
systemctl disable postgresql-9.6.service		
systemctl disable postgresql		
In postgres 9.2 there is bug in pg_ctl so you need do this workaround:

Backup pg_ctl:

cp /usr/bin/pg_ctl /data/postgres_upgrade/pg_ctl

Modify the binary and set correct permitions:

mv /usr/bin/pg_ctl /usr/bin/pg_ctl-orig
echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

Run the upgrade

Run pg\_upgrade binary from the new postgres and add path to both old and new binaries and data directories. You also need to add old and new port number:

su - postgres		
/usr/pgsql-9.6/bin/pg_upgrade -v --old-datadir="/data/pgsql/9.2/data/" --new-datadir="/data/pgsql/9.6/data/" --old-bindir="/usr/bin/" --new-bindir="/usr/pgsql-9.6/bin/" --old-port=6000 --new-port=7000

This command will migrate the database for you and it takes some time to complete. After it finishes, the database has been upgraded and datafiles under new postgres base have been populated.

Do NOT delete old data directory yet.

Revert the changes you made to pg_ctl (if you had to do them).
mv -f /usr/bin/pg_ctl-orig /usr/bin/pg_ctl

Backup default configuration of new postgres. Then move prepared configuration options from /data/postgres\_upgrade/pg\_hba.conf.new and /data/postgres\_upgrade/postgresql.conf.new to new postgres's configuration with the necessary changes:

cp -v /data/pgsql/9.6/data/pg_hba.conf{,-orig}
cp -v /data/pgsql/9.6/data/postgresql.conf{,-orig}

cp /data/postgres_upgrade/pg_hba.conf.new /data/pgsql/9.6/data/pg_hba.conf
cp /data/postgres_upgrade/postgresql.conf.new /data/pgsql/9.6/data/postgresql.conf

Adjust settings for the new version of the database (some settings may be missing now and some may have become obsolete).

If you are using postgres certificate and key. Copy it from old postgres and set correct permissions.

cp /data/postgres_upgrade/server.crt /data/pgsql/9.6/data/	
cp /data/postgres_upgrade/server.key /data/pgsql/9.6/data/
chown postgres:postgres /data/pgsql/9.6/data/server.crt		
chown postgres:postgres /data/pgsql/9.6/data/server.key

Start and check postgres 9.6

Start postgresql-9.6.service:

systemctl start postgresql-9.6.service	
systemctl enable postgresql-9.6.service	
systemctl status postgresql-9.6.service	

Check that you can connect to the database.

su - postgres	
/usr/pgsql-9.6/bin/psql -p 7000	
#print all databases
\l	
#Go to the czechidm DB
\c czechidm	
#Print all roles and check that you can read from database.
select * from idm_role;
#than exit
\q
# also try connect as app user who will use the database
psql -h localhost -U czechidm

Set default port of new postgres to 5432 (the port the 9.2 postgres originally ran on)

In file /etc/systemd/system/postgresql-9.6.service set:

 Environment=PGPORT=5432

Restart postgres and check that it's running on port 5432:

systemctl daemon-reload	
systemctl restart postgresql-9.6	
systemctl status postgresql-9.6	

netstat -nlpt | grep 7000
#expected output: nothing

netstat -nlpt | grep 5432	
#expected output: 
#tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1815/postmaster

Then reboot system and check if postgres and other services started after reboot:

reboot	
systemctl status postgresql-9.6

# the old postgresql should remain stopped
systemctl status postgresql

Start IdM

Start IdM or other application that uses postgres and check if all data are correct.

Backup database on postgres 9.6

Create "after-upgrade" backup of migrated databases.

cd /data/postgres_backup/backup_postgres96
su - postgres
/usr/pgsql-9.6/bin/pg_dump --compress=9 --create --dbname=czechidm > czechidm_backup.sql.gz
/usr/pgsql-9.6/bin/pg_dump --compress=9 --create --dbname=postgres > postgres_backup.sql.gz

Uninstall postgres 9.2

Stop applications using postgres - just to be sure:

systemctl stop tomcat
systemctl disable tomcat

Uninstall old postgres and move old data directory.

mv /data/pgsql/9.2 /data/postgres_upgrade/
yum remove postgresql.x86_64
yum remove postgresql-libs.x86_64

Check that new postgres starts up correctly after the uninstallation of the old one is complete. Just to be sure.

systemctl stop postgresql-9.6	
systemctl start postgresql-9.6	
systemctl status postgresql-9.6

If new postgres started correctly remove the backup of the old data directory.

rm -rf /data/postgres_upgrade/9.2

Finally, create symlinks to new postgres binaries (use list of symlink the yum complained about when you were installing new postgres version):

ln -s /etc/alternatives/pgsql-psql /usr/bin/psql	
ln -s /etc/alternatives/pgsql-clusterdb /usr/bin/clusterdb	
ln -s /etc/alternatives/pgsql-createdb /usr/bin/createdb	
ln -s /etc/alternatives/pgsql-createlang /usr/bin/createlang	
ln -s /etc/alternatives/pgsql-createuser /usr/bin/createuser	
ln -s /etc/alternatives/pgsql-dropdb /usr/bin/dropdb	
ln -s /etc/alternatives/pgsql-droplang /usr/bin/droplang	
ln -s /etc/alternatives/pgsql-dropuser /usr/bin/dropuser	
ln -s /etc/alternatives/pgsql-pg_basebackup /usr/bin/pg_basebackup	
ln -s /etc/alternatives/pgsql-pg_dump /usr/bin/pg_dump	
ln -s /etc/alternatives/pgsql-pg_dumpall /usr/bin/pg_dumpall	
ln -s /etc/alternatives/pgsql-pg_restore /usr/bin/pg_restore	
ln -s /etc/alternatives/pgsql-reindexdb /usr/bin/reindexdb	
ln -s /etc/alternatives/pgsql-vacuumdb /usr/bin/vacuumdb	

Start IdM or your application and check if it started correctly.

The upgrade is complete.

Post upgrade notes and checks

After upgrade, you should check that automatic backup of database are executing properly. If they are using pg_dump there shouldn't be any issue.

If you monitor that postgres is running implemented with systemctl status …, note the change of unit name from postgresql.service to postgresql-9.6.service. You can either change your monitoring or change the unit name.

Recovery guide

Automatic upgrade fails or new PostgreSQL installation gets broken

In case the pg\_upgrade fails or the upgrade process corrupts data, you can try to import the new database from the backup. PostgreSQL backups made on older versions of RDBMS can be imported to its newer versions. Such recovery is also usable when your new PostreSQL installation (already containing data) gets corrupted. You can then use old or new backup, depending on which you already have at hand and which you trust it contains correct data.

If you do not have such backups and have to return back to old PostgreSQL installation, follow the steps below:

Turn off the application which is using postgres. Under user postgres, drop all damaged databases on new postgres:

su - postgres
/usr/pgsql-9.6/bin/psql -p 7000

drop database [DB name]
#when you are done exit with \q

Then in /data/postgres\_upgrade/backup\_postgres92, decompress all backups you need to restore and restore them to new postgres.

cd /data/postgres_upgrade/backup_postgres92
gunzip czechidm_backup.sql.gz
/usr/pgsql-9.6/bin/psql -p 7000 -d postgres -f /data/postgres_upgrade/backup_postgres92/czechidm_backup.sql
# ... and similarly for other database dumps ...

Then refer back to run_upgrade section of this document and continue as if the pg\_upgrade command completed successfully.

Recovery to old postgres

When upgrade fails and you don't have time to try it again use this guide to revert to old postgres.

Turn off the application which using postgres (if it is already running). Then set default port on postgres. If you postgres is running on that port already - skip this step:

In file /etc/systemd/system/postgresql.service set:

 Environment=PGPORT=5432

Restart postgres and check that it's running on port 5432:

systemctl daemon-reload	
systemctl restart postgresql.service
systemctl status postgresql.service
netstat -nlpt | grep 6000
#expected output: nothing
netstat -nlpt | grep 5432	
#expected output: 
#tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1815/postmaster

Start your application and check if all data is correct.