Upgrading PostgreSQL

Since OpenNMS Horizon 25 has a new requirement of PostgreSQL 10 or higher, it's likely many of you are needing to look into how to go about upgrading it. Here is a short tutorial on what you need to do.

NOTE: This tutorial assumes you did not already have PostgreSQL 11 installed and in use for other things on your system. If you wish to migrate your OpenNMS database from an older cluster to a newer existing cluster already containing data, you will likely need to back up your OpenNMS database, upgrade PostgreSQL, and then restore. Some basic instructions on how to do so are in the OpenNMS Wiki.

Getting the Latest PostgreSQL from the PostgreSQL Global Development Group (PGDG)

While most distributions provide some version of PostgreSQL, the best way to get the latest version is to use PostgreSQL's official package repository.

To configure the PGDG repository, all you should need to do is follow the link to your distribution under the "Binary packages" section of PostgreSQL's download page. (On RedHat/CentOS, you'll configure the Yum repository, and on Debian/Ubuntu you'll configure their Apt repository.)

Install PostgreSQL 11

While some limited OpenNMS testing has been done with PostgreSQL 12, it is newly released at the time of this writing (Early October, 2019), so for now I would recommend installing 11. If you're feeling brave, or reading this much later, feel free to swap "11" out for "12". :)

On RedHat or CentOS, run:

$ sudo yum install postgresql11-server
$ sudo postgresql-11 initdb

On Debian or Ubuntu, run:

$ sudo apt install postgresql-11

Configure PostgreSQL 11

Don't start it yet, but before you do, you'll want to make sure any configuration you need is transferred over to PostgreSQL 11. That means setting up pg_hba.conf to match your authentication configuration, making sure PostgreSQL is listening on the port you would like, etc.

A Bit About IPLIKE

For historical reasons, the IPLIKE binary we provide on RedHat/CentOS systems is only built to work against the PostgreSQL version provided by the system. There is an issue for upgrading the packages to be co-installable and I hope to get to it before Meridian 2019 is out, but in the meantime you will most likely need to build and it from source if you are on a RedHat-based system. (Don't worry about installing it into your database, I'll cover that later.)

NOTE: if you never used the iplike binary version and just used the PL/PgSQL version that OpenNMS installs during configuration, you can skip the IPLIKE steps.

To do so, follow the instructions on the IPLIKE page. In most cases all you should need to do is something like this:

$ yum -y install postgresql11-devel
$ cd /tmp
$ curl -L -O https://sourceforge.net/projects/opennms/files/IPLIKE/stable-2.1/iplike-2.1.3.tar.gz
$ tar -xzf iplike-2.1.3.tar.gz
$ cd iplike-2.1.3
$ ./configure --with-pgsql=/usr/pgsql-11/bin/pg_config --prefix=/usr/pgsql-11
$ make
$ make install

On Debian and Ubuntu, however, the OpenNMS Apt repository should already have a package that matches your database version. Just install iplike-pgsqlXX (where XX matches your PostgreSQL version), like so:

$ apt install iplike-pgsql11

Upgrading on Debian or Ubuntu Using pg_upgradecluster

The PostgreSQL packages on Debian and Ubuntu come with tools for managing and running different versions simultaneously with a feature called "clusters" which makes it easy to migrate your data.

In this example we're migrating from PostgreSQL 9.4 to the new 11 version that we just installed. You can see the current status of your installed PostgreSQL versions using the pg_lsclusters command:

$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
11  main    5433 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

As you can see, our existing database is running on port 5432, and the newly-installed PostgreSQL 11 was automatically set up to use port 5433, but is not started.

1. Delete the Existing PostgreSQL 11 Cluster

When you installed postgresql-11 above, it already created a default cluster (called "main"). Delete it, so we can instead migrate the old one:

   $ pg_dropcluster 11 main

1. Migrate the Old Cluster

Next, migrate the cluster using the pg_upgradecluster command. I'll explicitly tell it to migrate to version 11 (with the -v 11 argument) just to be sure, but by default it should upgrade to the latest available version.

   pg_upgradecluster -v 11 9.4 main

Because the new database doesn't have IPLIKE configured yet, you will see some errors like this:

   ERROR:  incompatible library "/usr/lib/postgresql/9.4/lib/iplike.so": version mismatch
   DETAIL:  Server is version 11, library is version 9.4.
   ERROR:  function public.iplike(text, text) does not exist

The upgrade will work anyway, just the IPLIKE function will be missing. When it's done, you'll see the new cluster has been created and given the original cluster's port:

   $ pg_lsclusters
   Ver Cluster Port Status Owner    Data directory               Log file
   9.4 main    5433 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
   11  main    5432 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

1. Start the New Cluster and Reinstall IPLIKE

Now we can launch the new cluster and install IPLIKE into it.

Note that if you have configured username/password authentication you may need to add additional options to the install_iplike-11.sh command.

   $ pg_ctlcluster 11 main start
   $ install_iplike-11.sh
   CREATE FUNCTION
   $ psql -U opennms -c '\sf iplike'
   CREATE OR REPLACE FUNCTION public.iplike(i_ipaddress text, i_rule text)
    RETURNS boolean
    LANGUAGE c
    STRICT
   AS '/usr/lib/postgresql/11/lib/iplike.so', $function$iplike$function$

That final psql command will show you the iplike function. If it's pointing to iplike.so like this example, then everything worked! You can continue your OpenNMS upgrade.

Upgrading on RHEL or CentOS Using pg_upgrade

In this example we're upgrading from the PostgreSQL 9.2 that comes with CentOS (or RHEL) 7 to PostgreSQL 11.

1. Remove IPLIKE From the Old Database

Because the library path for the iplike.so binary will be different between the old and new databases (and because they are binary-incompatible), you will need to temporarily remove the iplike function from the old database. To do so, you can use the drop function command:

   $ psql -U opennms -c 'drop function iplike ( text, text );'

If you decide to roll back to your old database, you can re-add it with the /usr/local/sbin/install-iplike.sh script.

1. Stop the Old Database

Now it's time to stop the existing database

   $ service postgresql stop

1. Work Around a RedHat Patch (CentOS 7 Default PostgreSQL Only)

The PostgreSQL 9.2 that RedHat/CentOS provide is patched to backport a feature from PostgreSQL 9.3 that allows specifying multiple socket directories. Unfortunately, the upstream pg_upgrade command doesn’t know to take this into account.

The workaround is to temporarily patch the old pg_ctl file to fix the arguments pg_upgrade passes on startup:

   $ mv /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 a+rx /usr/bin/pg_ctl

1. Test the Migration

Now, you can run the migration in dry-run "check" mode (-c) to see if there are any issues. The options are as follows:

• -b the directory containing the "from" database's PostgreSQL binaries
• -B the directory containing the "to" database's PostgreSQL binaries
• -d the directory containing the "from" database's PostgreSQL data
• -D the directory containing the "to" database's PostgreSQL data
• -r retain log files after success
• -v verbose output

   $ cd /tmp
   $ sudo -u postgres /usr/pgsql-11/bin/pg_upgrade \
     -b /usr/bin \
     -B /usr/pgsql-11/bin \
     -d /var/lib/pgsql/data \
     -D /var/lib/pgsql/11/data \
     -r \
     -v \
     -c

1. Run the Migration

If all looks good, you'll see *Clusters are compatible* near the end of the output. Run the pg_upgrade command again, this time without the -c argument:

   $ sudo -u postgres /usr/pgsql-11/bin/pg_upgrade \
     -b /usr/bin \
     -B /usr/pgsql-11/bin \
     -d /var/lib/pgsql/data \
     -D /var/lib/pgsql/11/data \
     -r \
     -v

1. (Re)install IPLIKE Into the New Database

Now it's time to (re)install IPLIKE. If you followed the instructions above, you should have an install_iplike.sh in your /usr/pgsql-11/sbin directory.

Note that if you have configured username/password authentication you may need to add additional options to the install_iplike.sh command.

   $ /usr/pgsql-11/sbin/install_iplike.sh
   CREATE FUNCTION
   $ psql -U opennms -c '\sf iplike'
   CREATE OR REPLACE FUNCTION public.iplike(i_ipaddress text, i_rule text)
    RETURNS boolean
    LANGUAGE c
    STRICT
   AS '/usr/pgsql-11/lib/iplike.so', $function$iplike$function$

1. Undo Temporarily Patched pg_ctl

Put the original pg_ctl back now that you’re done upgrading.

   $ mv /usr/bin/pg_ctl-orig /usr/bin/pg_ctl

1. Uninstall the Old PostgreSQL or Update Startup

If everything moved over correctly -- and you aren't using it for anything else -- you can uninstall the old postgresql-server package.

If you do not remove the old PostgreSQL, you may need to override the systemd configuration used to start OpenNMS so that it waits for the correct PostgreSQL.

To do so, run:

   $ sudo systemctl edit opennms

You can then override After: by pasting in the following:

   [Unit]
   After=postgresql-11.service network.target network-online.target

This will make sure that OpenNMS waits until PostgreSQL 11 is started, rather than only waiting for the originally installed system PostgreSQL service.

All Done!