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.
Risks and considerations
When you upgrade the PostgreSQL database where the IdM repository is located, you should take care of a few things.
- CzechIdM application must be stopped during whole PostgreSQL upgrade.
- Upgrading the database may potentially damage your data.
- It is vital to have backups of database before upgrade.
- It is vital to have a procedure for confirming data was not corrupted during the DB upgrade.
- We advise to make another backup right after the upgrade, after you verify that data is OK.
Preparation and prerequisites
Before you start the upgrade, check that you have all necessary thing prepared:
- Check that you have enough space on the disk.
- Space for backup that is made with
pg\_dump. Can be located on any volume. - Enough space to hold raw copy of databases (basically at least
du -sh /data/pgsql/9.2space). The free space should be located/available for the/data/pgsql/9.6directory.
- Check you have rollback plan for situation when upgrade doesn't go as planned.
- Create those temporary directories for upgrade (we usually have one mountpoint
/datathat is big enough to conatins everything we need or create during the upgrade).mkdir /data/postgres_upgrade mkdir /data/postgres_upgrade/backup_postgres92 mkdir /data/postgres_upgrade/backup_postgres96 chown postgres:root /data/postgres_upgrade/backup_postgres92 chown postgres:root /data/postgres_upgrade/backup_postgres96
- List all configuration options of old PostgreSQL that you use in
postgresql.confandpg\_hba.conf. Save this configs into/data/postgres\_upgrade/postgresql.conf.newand/data/postgres\_upgrade/pg\_hba.conf.new.
- If you are using certificate and key - backup them from old postgres.
cp /data/pgsql/9.2/data/server.crt /data/postgres_upgrade/ cp /data/pgsql/9.2/data/server.key /data/postgres_upgrade/
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/
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
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.
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.