Error on restore of Foreman config backup created on EL8 server to a new EL9 server

Support
1

Problem:

pg_dump created on Foreman server by foreman-maintain backup running on el8 will not import into postgresql on el9 server.

Expected outcome:

Backup of Foreman configuration created by foreman-maintain on the EL8 server will as restore via foreman-maintain on the EL9 server after the backup is transferred to the EL9 server via rsync.

Foreman and Proxy versions:

Source: Foreman server 3.12.1
Target: Foreman server 3.12.1

Foreman and Proxy plugin versions:

Source:

foreman-tasks 9.2.3
foreman_ansible 14.2.1
foreman_puppet 7.0.0
foreman_remote_execution 13.2.5
katello 4.14.1

Target:

foreman-tasks 9.2.3
foreman_ansible 14.2.1
foreman_puppe 7.0.0
foreman_remote_execution 13.2.5
katello 4.14.1

Distribution and version:

Source: AlmaLinux 8.10
Target: AlmaLinux 9.5

Other relevant data:
Dedicated postgresql servers: Source AlmaLinux 8.10, Target AlmaLinux 9.5
Postgresql 13.18

The backup created can be restored to another EL8 server with Foreman without any issues. When the restore is run on the EL9 server, the following error is noted and then lots of other errors afterwards that are related to missing tables.

40988 2024-12-09 14:25:54.145 PST [3191491]: [3-1] user=pulp, app=psql, host=XXXXXXXXXXXXXXXXXX(49960), xid=0, db=pulpcore, tag=idle, state=42601,       start=2024-12-09 14:25:54 PST ERROR:  syntax error at or near "string_agg" at character 1
40989 2024-12-09 14:25:54.145 PST [3191491]: [4-1] user=pulp, app=psql, host=XXXXXXXXXXXXXXXXXX(49960), xid=0, db=pulpcore, tag=idle, state=42601,       start=2024-12-09 14:25:54 PST STATEMENT:  string_agg                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
40990
40991          drop table if exists "django_migrations" cascade;
40992 2024-12-09 14:25:55.439 PST [3191491]: [5-1] user=pulp, app=psql, host=XXXXXXXXXXXXXXXXXX(49960), xid=0, db=pulpcore, tag=idle, state=42601,       start=2024-12-09 14:25:54 PST ERROR:  syntax error at or near "1" at character 2

Similar errors in the postgresql log are observed for the restore of the foreman and candlepin databases.

2

Hi,

just so that I understand your setup correctly:

  • You have Foreman 3.12.1 running on Alma 8.10 with an external DB server, also running Alma 8.10
  • You want to move this to a Foreman running on Alma 9.5 with an external DB server, also running Alma 9.5
  • The restore works fine when you try it on a new Alma 8.10 pair of machines, but not on 9.5
  • The backup was created using foreman-maintain backup

Can you share the following please?

  • How exactly you restore the DB on the new DB host.
  • The output of rpm -qa |grep postgresql of the working (8.10) and broken (9.5) systems
3

I found the issue. The hstore extension on the postgresql server was enable in the pg_catalog schema instead of the public schema.

I dropped the extension from the databases and re-enabled it with the command

CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;

This was probably due to the extension not being enabled on the databases prior to the foreman services install.

I will check this when I spin up the replacement environment in the other datacenter where I keep a standby environment for DRE migrations.

1 Like