Upgrade PostgreSQL to v13

CentOS 7 and RHEL 7 Guide to upgrade from PostgreSQL 9.5 to PostgreSQL 13

Older installations of Polarity Server will likely be running version 9.5 of PostgreSQL. As v9.5 has reached end of life, we recommend upgrading to v4.x supports Postgres database servers above v9.5. As PostgreSQL v9.5 has reached end of life, we recommend upgrading to PostgreSQL 13 which is the default install on newer releases of Polarity Server.

This guide will step you through updating an existing PostgreSQL 9.5 database server located on the Polarity Server to the latest version of PostgreSQL 13.

Before You Begin

1. Ensure you have taken a full backup of the Polarity Server database before you begin.

2. Install the latest Polarity Server v4.x RPMs which support PostgreSQL 9.5+

3. Ensure you are running either CentOS 7 or RHEL 7

Users running RHEL 8 or Oracle Linux 8 should already be running PostgreSQL 13

Upgrade Steps

Stop the Polarity Server and PostgreSQL 9.5 services:

systemctl stop polarityd
systemctl stop postgresql-9.5

Install the latest PostgreSQL v13 packages. Note, this assumes you have already installed the PGDG repo that downloads and installs PostgreSQL yum packages directly from https://yum.postgresql.org. If you have not, or need to update it please run:

yum install https://download.postgresql.org/pub/repos/yum/ \
reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y

After the PostgreSQL repo is updated you can install the PostgreSQL 13:

yum install postgresql13 postgresql13-server ip4r13 postgresql13-libs \
postgresql13-contrib -y

As the postgres user, initialize the PostgreSQL 13 server.

sudo su - postgres -c "/usr/pgsql-13/bin/initdb -D /var/lib/pgsql/13/data"

Next, check to make sure the upgrade is compatible.

sudo su - postgres -c "time /usr/pgsql-13/bin/pg_upgrade \
--old-bindir /usr/pgsql-9.5/bin \
--new-bindir /usr/pgsql-13/bin \
--old-datadir /var/lib/pgsql/9.5/data \
--new-datadir /var/lib/pgsql/13/data --link --check"

If the output from above shows *Clusters are compatible* then you can proceed to the next step which is to run the upgrade:

sudo su - postgres -c "time /usr/pgsql-13/bin/pg_upgrade \
--old-bindir /usr/pgsql-9.5/bin \
--new-bindir /usr/pgsql-13/bin \
--old-datadir /var/lib/pgsql/9.5/data \
--new-datadir /var/lib/pgsql/13/data --link"

You may see a message saying you must update your extensions. The message will look like this:

Your installation contains extensions that should be updated with the ALTER EXTENSION command. The file update_extensions.sql when executed by psql by the database superuser will update these extensions.

After starting the PostgreSQL 13 database we will update the extensions.

Start the PostgreSQL 13 database service, restart the Polarity Server service, and disable and the PostgreSQL 9.5 database server:

systemctl enable postgresql-13
systemctl start postgresql-13
systemctl start polarityd
systemctl disable postgresql-9.5

If you need to update your extensions (see message from above) then run the following command now that the PostgreSQL 13 database is running:

sudo su - postgres -c "psql -f /var/lib/pgsql/update_extensions.sql"

Next you will need to re-create the user stats materialized view:

sudo su - postgres -c \
'psql -d breach -c "REFRESH MATERIALIZED VIEW polarity.user_stats;"'

Once the database is successfully updated, remove the PostgreSQL 9.5 packages:

yum remove postgresql95 -y

Finally, confirm the Polarity Server and PostgreSQL 13 database server are running:

systemctl status polarityd
systemctl status postgresql-13

You should see the new postgresql-13 service running and active.