Security Server RHEL7 PostgreSQL 9 Upgrade as a prerequisite for X-Road 7.4

This document describes the steps required for upgrading a RHEL7 Security Server PostgreSQL v9.2 to v12. Please read carefully through the whole document before starting the upgrade process. It is assumed that the reader is familiar with the RHEL7 Linux distribution.

Preparations

Ensure that the X-Road software version is at least 7.2.0.
Recommended: Make sure that you have an up-to-date backup (or virtual machine snapshot) of the database.
Stop the Security Server:
- All running xroad services can be listed with sudo systemctl list-units "xroad-*".
- Stop the services with sudo systemctl stop "xroad-*".
Disable the X-Road software repository:
- All enabled repositories can be listed with sudo yum repolist.
- Check the X-Road repository ID from the repolist and use it to disable the repo.
  - For example, the repo ID of the NIIS X-Road repository is artifactory.niis.org_xroad-release-rpm_rhel_7_current.
- Disable the X-Road repository:
  - sudo yum-config-manager --disable <REPO_ID>
- Run sudo yum repolist to verify that the repo is disabled.
Optionally, take backup of the message log database. Without a backup, a failure in the upgrade process may cause loss of data.
- See Chapter 25. Backup and Restore
- Using pg_dump is a straightforward option, but can be problematic if the database is extremely large:
  sudo -iu postgres pg_dump -d messagelog -F c -f <dump_file>
- Reducing the size of the message log database dump: By default the message log database keeps 30 days of message records but one can (temporarily) change the retention time in order to reduce the number of records in the database, thus reducing the size of the database dump. Changing the setting has no immediate effect since by default the message log cleanup is run twice a day (noon and midnight). See Security Server User Guide for more information.
Optionally, take a backup of the archived message log files.
- Note that some of the logged messages might not have been archived yet and exist only in the database.
Optionally, take backup of the operational monitoring database (if xroad-opmonitoring is installed). Without a backup, a failure in the upgrade process may cause loss of data.
- See Chapter 25. Backup and Restore
  sudo -iu postgres pg_dump -d "op-monitor" -F c -f <dump_file>

Upgrading PostgreSQL 9 to PostgreSQL 12

# Stop existing postgreSQL
sudo systemctl stop postgresql

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum upgrade -y

# Workaround for Postgres 9.2 unix_socket_directory property deprecation
sudo mv /usr/bin/pg_ctl{,-orig}
sudo echo '#!/bin/bash' > /usr/bin/pg_ctl
sudo echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> \
     /usr/bin/pg_ctl
sudo chmod 777 /usr/bin/pg_ctl

# Install packages
sudo yum  -y install postgresql12 postgresql12-server postgresql12-contrib

# Init db
sudo su postgres
cd /tmp
/usr/pgsql-12/bin/initdb --no-locale --encoding=UTF8 -D /var/lib/pgsql/12/data/
# Execute update
/usr/pgsql-12/bin/pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/12/data/ --old-bindir /usr/bin/ --new-bindir /usr/pgsql-12/bin/
exit

# Disable old services
sudo systemctl disable postgresql
# Enable new services
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

# Remove old postgres instance
sudo yum remove postgresql
#to restore symlinks to postgres commands, reinstall postgres12 after uninstalling old version. This cannot be done sooner as pg_upgrade relies on old executables
sudo yum reinstall postgresql12

# Remove Postgres 9.2 workaround
 sudo mv -f /usr/bin/pg_ctl{-orig,}

Migrating clustered instances

Master node

Master node can be migrated in a similar way to non clustered setup.

Please update postgresql.conf for the database as described in this setup guide.

# Stop the instance if it is still running. 
# Note: server will be stopped/failing if main database is already migrated
sudo systemctl stop postgresql-serverconf

# Init db
sudo su postgres
cd /tmp
/usr/pgsql-12/bin/initdb --auth-local=peer --auth-host=md5 --locale=en_US.UTF-8 --encoding=UTF8 -D /var/lib/pgsql/12/serverconf/
# Execute upgrade
/usr/pgsql-12/bin/pg_upgrade --old-datadir /var/lib/pgsql/serverconf/ --new-datadir /var/lib/pgsql/12/serverconf/ --old-bindir /usr/bin/ --new-bindir /usr/pgsql-12/bin/
exit
# Update service configuration
sudo echo -e ".include /usr/lib/systemd/system/postgresql-12.service\n[Service]\nEnvironment=PGPORT=5433\nEnvironment=PGDATA=/var/lib/pgsql/12/serverconf" > /etc/systemd/system/postgresql-serverconf.service
sudo systemctl daemon-reload

# IMPORTANT: update postgres clust
sudo systemctl start postgresql-serverconf

Secondary nodes

Secondary nodes must be upgraded to the same PostgreSQL version as master and then initialized from scratch. Please follow the documentation for more details.

Enable the X-Road software repository

Use the X-Road repository ID to enable the repository:

Run sudo yum repolist to verify that the repo is enabled.

Start X-Road services

sudo systemctl start "xroad-*" --all

Verify that the security server services are running and the system is responding

* If the monitoring addon(s) are installed.

Troubleshooting

If recovery from a upgrade failure is not possible and there is a backup/snapshot available, one can restore it and start over.