Use managed PostgresDB Instance after with Katello 3.16 and later

Hello,

I was thinking about migrating our Foreman instance to some cloud provider to decouple it from the infrastructure it is managing. Also, this would reduce the time and skills needed to maintain the database itself.

But as I’ve just noticed, Katello 3.16 introduced a custom Postgres extension evr which makes all managed databases from common cloud providers (Azure DB for Postgres, AWS RDS, Google CloudSQL for Postgres, …) basically unusable as they do not allow the installation of custom extensions.

What are the plans regarding custom extensions for the future? As far as I understand, the evr extension currently only ships custom types and functions which could be created on the database manually.

Does one of you have any idea on how the extension could be enabled/installed anyway?

Greetings,
Manuel

I think @Justin_Sherrill or @iballou are likely best to give some thoughts here.

Hi @laugmanuel,

It is possible to install the postgresql-evr functions and triggers without the actual evr extension, but it would be a bit hacky for a production install of Katello. You would need to declare each of the types and functions in this file via psql some time after the database is created but before the migrations and seeding are run. As a result, you would also need to have the foreman-installer skip the database creation/migration/seeding steps and run them yourself (this I haven’t tried, but might be possible through foreman-installer arguements). Additionally, the 20200213184848_create_evr_type.rb katello database migration would need to be edited to remove the enable_extension "evr" line.

Perhaps there is an easier way, but I am not seeing it at the moment. It would be worthwhile for us to look into cleanly supporting managed databases like these for the postgresql-evr extension. I will raise it as a Katello issue.

Also, if you do have shell access on the database server, but you can’t install RPMs, you could try installing the postgresql-evr extension from source https://github.com/Katello/postgresql-evr. Might be a stretch though, I haven’t worked with many cloud managed DBs.

Sorry for the late reply - I’ve been busy working on other stuff.

I think your suggestion should work but it’s far from ideal because a user would need to manually patch the Foreman code and modify the installer options.
Shell access is, as far as I know, not possible on cloud instances.

I’m just wondering what the reason behind the extension is, as it’s just a collection of custom functions? Wouldn’t it be possible to add those functions by regular db migrations?

Hi @laugmanuel ,

During implementation of the custom yum evr types, we hit an issue where the Foreman database schema dump was missing the custom evr types since we use :ruby as our Active Record schema dump type. The only solution (that we saw at least) for having the types show up in the dump and keep the custom type definitions in the migration would be to switch to :sql. Creating the postgresql-evr extension was a preferrable solution to changing the schema type to :sql.

We also knew in the future that postgresql-debversion was coming to enable deb package version sorting as well. In fact, it’s expected to arrive in 3.18.0 Feature #29768: Use debversion postgresql-extension - Katello - Foreman