Upgrading the OpenNMS postgres database from 9.2 to 11

I’m trying to upgrade the OpenNMS postgres database on my sandpit sever from 9.2.24 as distributed with Oracle Linux 7.7 to v11.5, the latest stable from the postgres site using the instructions from the postgresql site https://www.postgresql.org/docs/11/pgupgrade.html

There was a minor issue with the pg_upgrade script caused by the linux distro provider backporting a change to the name of the “unix_socket_directory” parameter which broke the script as it was expecting a 9.2 binary to accept 9.2 parameters not 9.3+ parameters, for which I found the answer by Zeitgeister on this thread at dba stack exchange: https://dba.stackexchange.com/questions/50135/pg-upgrade-unrecognized-configuration-parameter-unix-socket-directory that gave a short script to fix the issue:

   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
   echo >> /usr/bin/pg_ctl
   cat /usr/bin/pg_ctl
   chmod +x /usr/bin/pg_ctl
   su - postgres -c '/usr/pgsql-11/bin/pg_upgrade --old-datadir "/var/lib/pgsql/data"  --new-datadir "/var/lib/pgsql/11/data" --old-bindir "/usr/bin" --new-bindir "/usr/pgsql-11/bin"'
   mv -f /usr/bin/pg_ctl-orig /usr/bin/pg_ctl

with this change, the postgres db upgrade proceeded smoothly

so I started the postgres 11.5 database, and then proceeded to run install -dis, but I recieve the following error:

java.lang.reflect.InvocationTargetException
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.opennms.bootstrap.Bootstrap$4.run(Bootstrap.java:527)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.opennms.core.schema.MigrationException: an error occurred getting the version from the database
	at org.opennms.core.schema.Migrator.getDatabaseVersion(Migrator.java:159)
	at org.opennms.core.schema.Migrator.validateDatabaseVersion(Migrator.java:187)
	at org.opennms.install.Installer.install(Installer.java:246)
	at org.opennms.install.Installer.main(Installer.java:995)
	... 6 more
Caused by: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "opennms"
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:514)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:454)
	at org.postgresql.Driver.connect(Driver.java:256)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at org.opennms.core.db.install.SimpleDataSource.getConnection(SimpleDataSource.java:113)
	at org.opennms.core.schema.Migrator.getDatabaseVersion(Migrator.java:147)
	... 9 more
	Suppressed: org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "opennms"
		at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:514)
		at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
		at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:201)
		... 17 more

If I stop the 11.5 database, and restart the 9.2.24 database, the install -dis fails at the database version check.

Where do I need to look to solve this issue?
Does someone have any good info on upgrading the OpenNMS postgres database from 9.2.24?

Regards,
John

ah it was a missing entry in the pg_hba.conf

I just needed to copy the pg_hba.conf from the 9.2.24 install across to the 11.5 install, and restarted and it is all good now.

2 Likes