Upgrade 2.0.2/3.15.3 -> 2.1.2/3.16.0 - db:migrate fails loading evr extension when using external PostgreSQL database for Foreman and Candlepin

reenberg · September 16, 2020, 1:34pm

Problem:
Foreman installer fails during upgrade.

Expected outcome:
Smoth installation and db migration.

Foreman and Proxy versions:
Foreman 2.1…2-1

Foreman and Proxy plugin versions:
Katello 3.16.0-1

Distribution and version:
CentOS 7.8

Other relevant data:

Our Foreman installation is using an external PostgreSQL 10 database, which created some hickups during the upgrade to Katello 3.15 with the introduction of Pulp3 and the “enforcement” of the installer to use a local PosgreSQL 12 database (se notes from ekohl)

My issue seems partial related to Katello 3.16 upgrade fails (foreman-rake db:migrate). I was missing the rh-postgresql12-postgresql-evr package, however the db:migrate process tries to load the evr extension from a PostgreSQL 10 path (which matches my external DB version), instead of the local PostgreSQL 12.

/Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Excec[foreman-rake-db:migrate]/returns: PG::UndefinedFile: ERROR: could not open extension control file "/opt/rh/rh-postgresql10/root/root/usr/share/pgsql/extension/evr.control": No such file or directory

It is entirely correct, that no such file exists, however after manual installation of the above evr rpm, it now exists under the PostgreSQL 12 folder. However the foreman-installer insists on trying to use the PostgreSQL 10 directory, and I’m tempted to suggest that it gets the version 10 from my remote database? However I’m not 100% sure how to proof this, as that database can’t be upgraded easily due to other dependencies.

I thought i would be able to cheat, and create a local symlink of

/opt/rh/rh-postgresql10 -> /opt/rh/rh-postgresql12

but no cigar.

ekohl · September 16, 2020, 1:53pm

The postgresql-evr extension is only built and shipped for PostgreSQL 12 using Red Hat SCL (on EL7). If you’re using a different server version, you are responsible for making sure the extension is available.

I’d recommend to first upgrade to PG 12 and then perform the Foreman/Katello upgrade.

To be clear: Foreman/Katello itself can still use an external PostgreSQL. You need to install the evr extension on your DB server. This may not be documented, but there is at least one PR open for that:

reenberg · September 16, 2020, 1:53pm

Ah it seems that it is actually trying to load the evr extension on my remote PostgreSQL 10 database server. Ill try and see if I can fix the extension there

reenberg · September 16, 2020, 2:01pm

I thought that the EVR extension was only needed for Pulp3? Currently Pulp runs on its own local PostgreSQL 12 database, and Foreman / Candlepin uses the remote PostgreSQL 10 database. However the migration script seems to try and load the extension on the remote database server as well, which seem unnecessary unless the Candlepin part also needs this evr extension?

It seems like a big oversight, that none of the release notes for Katello 3.15 or 3.16 mentions that support for PostgreSQL 10 is being dropped, and that an upgrade to PostgreSQL 12 should have been done prior to starting the 3.16 upgrade?

ekohl · September 16, 2020, 2:43pm

It looks like it’s shipped in our pulpcore repo, but katello itself also depends on it. here’s the migration that enables the extension.

github.com

Katello/katello/blob/master/db/migrate/20200213184848_create_evr_type.rb

require 'fx'

class CreateEvrType < ActiveRecord::Migration[5.2]
  def up
    unless connection.adapter_name.downcase.include?('sqlite')

      enable_extension "evr"

      add_column :katello_rpms, :evr, :evr_t
      add_column :katello_installed_packages, :evr, :evr_t

      create_trigger :evr_insert_trigger_katello_rpms, on: :katello_rpms
      create_trigger :evr_update_trigger_katello_rpms, on: :katello_rpms
      create_trigger :evr_insert_trigger_katello_installed_packages, on: :katello_installed_packages
      create_trigger :evr_update_trigger_katello_installed_packages, on: :katello_installed_packages

      execute <<-SQL
        update katello_rpms SET evr = (ROW(coalesce(epoch::numeric,0),
                                           rpmver_array(coalesce(version,'empty'))::evr_array_item[],
                                           rpmver_array(coalesce(release,'empty'))::evr_array_item[])::evr_t);

This file has been truncated. show original

Technically I think PG 10 still works if you take care of installing the extension. I recommended upgrading since it’s probably easier to use our pre-built package than to maintain it yourself.

reenberg · September 16, 2020, 3:38pm

Fair enough. Then I guess it makes sense that it requires the evr extension. The katello_rpm is a table in the “foreman” database.

I can confirm that it seems to work perfectly fine on PostgreSQL 10, when manually installed.

But this just highlight the importance of having a note in the release notes, highlighting this evr extension, especially when using a remote database where the Foreman installer can’t do the upgrade for you.

I guess the safest way forward is to migrate the database back to the Foreman server. Then environment isn’t that big. The remote database just had the convenience of being taken care of and backed up without me having to do anything speciel.

ekohl · September 16, 2020, 3:46pm

With 2.1 you should be able to have everything external and it’s supposed to be a supported scenario.

Perhaps @katello can take a look at how this should be documented, because I agree that should be done.

John_Mitsch · September 17, 2020, 12:23am

@Justin_Sherrill @iballou @jjeffers @sajha Any thoughts on what documentation changes are needed here?

reenberg · September 17, 2020, 9:09pm

If I may be so bold an make a suggestion, that would have helped me.
I hope I have my facts straight below.

The Katello 3.15 release notes

Should have a note about the default/supported database being change to rh-postgresql12, from 9.2. So external databases should be upgraded.
It should be noted that this version enforces a local installation of rh-postgresql12 for Pulp3. This is especially important for people using external databases. Notably because the installer might fail if you have the CentOS 7 postgresql 9.2 package installed (see Upgrade 1.24.2 > 2.0.1 unable to install and migrate to rh-postgresql 12 on CentOS 7.8)

The Foreman 2.0 release notes

Under the consolidation notes of PostgreSQL, it should also be noted that the default supported PostgreSQL version is bumped to 12 from 9.2.

The Katello 3.16 release notes

Under the features, it should be noted that the installer now supports options for remote Pulp3 PostgreSQL database. Perhaps a note about this being beta if that is the case? (the options is only shown by the --full-help option).
- A link to info about how to use the options and what they are would be nice
- A link to info about how to correctly migrate from local to remote would also be nice.
Under notes it should be stated that a new PostgreSQL extension is being introduced, and that this is only being packed for the supported version of PostgreSQL at any point in time.
- A note to remote databases about this needing to be installed manually prior to upgrading.
- If currently using both local and remote database, then the evr extenion needs to be present in both installation as both Katello and Foreman uses the extesion

The Foreman 2.1 release notes

It should be stated that a new PostgreSQL extension is being introduced, and that this is only being packed for the supported version of PostgreSQL at any point in time.
- I’m actually unsure if this is true, if you are running a pure Foreman installation?

ekohl · October 22, 2020, 12:11pm

Looks like I missed this comment, but it’s a good one. @jjeffers did start a PR for the docs:

This is not true. It is really Katello that adds the dependency so a vanilla Foreman doesn’t require it.