RFC: Upgrade EL7 installs to PostgreSQL 12

Summary

The introduction of Pulp 3, which now uses Postgres, and upgrade of the Foreman web server stack to Rails 6 requires upgrade Postgres from the standard 9.2 version in base Enterprise Linux 7 to Postgres 10. This newer database version is provided via Software Collections (SCL). All RPM based installs will get Postgres 10 by default. Existing users will have their database upgrade automatically when using foreman-installer to upgrade between versions.

This change is targeted for release in 1.25.

Design

  • foreman-installer and puppet-foreman will be updated to configure Postgresql from SCL by default for new installs
  • support will be added to --upgrade flag in foreman-installer to upgrade existing Postgresql databases to new Postgresql 10 database
  • users of external databases will need to upgrade their database manually; docs will be provided to help with this scenario

Impacts

  • Backup and restore
  • Upgrade process
  • Documentation
  • Database performance and tuning guide
  • Compatibility of services that use Postgres
    • Candlepin
    • Foreman
    • Pulp 3
2 Likes

What are the implications for Debian-based (if any)?

Distro version PosgreSQL version
Debian Stretch 9.6
Debian Buster 11
Ubuntu Xenial 9.5
Ubuntu Bionic 10

All of these are still compatible with Rails 6. Considering Katello and friends aren’t shipped for Debian/Ubuntu I don’t expect this to be a major issue.
I would ask to keep this in mind though if someone wants to leverage advanced PostgreSQL functions in core with the drop of MySQL which also comes in 1.25.

1 Like

@ekohl On EL7, we’ve previously taken approaches for supplying SCL configuration through hiera and facts within the installer to establish which version to use keeping puppet-foreman’s database setup minimal. However, this is a case where we need to enforce a minimum PostgreSQL version due to the Rails 6 upgrade. How should we be handling the various concerns?

  • Minimum PostgreSQL version
  • Use of SCL on EL7 RPM based system as a default for EL7 vs. use of PostgreSQL repositories
  • Future looking use of modules on EL8 and having a default

Given today our minimal PostgreSQL version is 9.2, we’d be just bumping the “minimal” to 9.5 then, right? Sounds fair to me.
On the other hand, apt.postgresql.org exists and provides :ok_hand: PostgreSQL packages for Debian and Ubuntu (by the same people who maintain it in Debian). So we could ask users to move to minum 10 easily (IMHO).

Speaking of moving, how are PostgreSQL updates handled on Debian these days? The user runs pg_upgrade themself, right? There is no help/docs from Foreman?

Speaking of moving, how are PostgreSQL updates handled on Debian these days?

Like this:

I know how to update PostgreSQL clusters in Debian :wink:
I was wondering if there is anything from the Foreman side in terms of documentation/guidance, or the users are supposed to know everything them-self (which seems to be the case).

1 Like

A new change has been pushed to the foreman-installer that enables PostgreSQL upgrades to 12 for Foreman and Katello scenarios. This upgrade is from 9.2 (via base OS RPMs) to 12. For those running any sort of nightly that has been upgraded to from 9.2 to 10 and want to upgrade to 12 you can do this manually (but I would recommend deploying a new nightly).

systemctl stop rh-postgresql10-postgresql.service
scl enable rh-postgresql12 -- postgresql-setup --upgrade --upgrade-from=rh-postgresql10-postgresql

Now re-run the installer.

Don’t forget to install rh-postgresql12-postgresql-server before attempting the migration. If you have rh-postgresql10-postgresql-contrib installed, be sure to also install the 12 version of that before migrating.

We install the service as syspaths so systemctl stop postgresql.service is needed.

When using non-standard locales, have a look at our hook how to get those and set them.