Upgrading PostgreSQL
Tags: opennms, postgres, upgrade
Published: 2019-10-08 19:08:30

---
title: Upgrading PostgreSQL
published: true
date: 2019-10-08 15:30:00 UTC
tags: opennms, postgresql, upgrade
canonical_url: https://opennms.discourse.group/t/upgrading-postgresql/772
---

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](https://wiki.opennms.org/wiki/Backup_and_Restore)._

## 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](https://www.postgresql.org/download/).  (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:

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

On Debian or Ubuntu, run:

```shell
$ 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](https://issues.opennms.org/browse/IPL-34) 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](https://github.com/opennms/iplike#building).  In most cases all you should need to do is something like this:

```shell
$ 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:

```shell
$ 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:

```shell
$ 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:

   ```shell
   $ pg_dropcluster 11 main
   ```

2. **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.

   ```shell
   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:

   ```shell
   $ 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
   ```

3. **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.

   ```shell
   $ 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:

   ```shell
   $ 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.

2. **Stop the Old Database**

   Now it's time to stop the existing database

   ```shell
   $ service postgresql stop
   ```

3. **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:

   ```shell
   $ 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
   ```

4. **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

   ```shell
   $ 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
   ```

5. **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:

   ```shell
   $ 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
   ```

6. **(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.

   ```shell
   $ /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$
   ```

7. **Undo Temporarily Patched `pg_ctl`**

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

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

8. **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:

   ```shell
   $ 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!