Migrating Foreman database from MariaDB (MySQL) to PostgreSQL

Support
1

Dear Foreman users group,

After finally upgrading to Puppet 4 and the corresponding PuppetDB we
thought, it would be nice to have just one database engine.

Our Setup:

  RHEL 7.4

  Apache 2.4.6

  tfm-rubygem-passenger 4.0.18

  MariaDB-server 5.5.57

  PostgreSQL Server 9.6

  Foreman 1.15.3

  Puppetserver 2.7.2

  Puppet-Agent 1.10.5

  PuppetDB 4.4.0

Installed Plugins

  Foreman-tasks 0.9.4 

  Foreman_bootdisk 9.0.0

  Foreman_column_view 0.3.0

  Foreman_dhcp_browser 0.0.7

  Foreman_hooks 0.3.14

  Foreman_host_rundeck 0.0.2

  Foreman_remote_execution 1.3.3

Foreman-tasks and remote_execution are installed, but not really used. So
far we had no success to remove them.

Foreman got installed ~3 years ago. Ever since we updated the application
and operating system.

The migration mentioned in the Foreman manual (
Foreman :: Manual) seems to
be the only way so far to keep the data (Foreman is used a lot for ENC).

The db:migrate jobs are running without error, but the db:convert:prod2dev
job always fails on the dynflow_coordinator_records table…

sudo -u foreman foreman-rake db:convert:prod2dev

Converting architectures…2 records converted in 0.178646509 seconds

Converting architectures_operatingsystems…19 records converted in
0.088987529 seconds

Converting audits…24564 records converted in 56.453044237 seconds

Converting auth_sources…3 records converted in 0.072683469 seconds

Converting bookmarks…21 records converted in 0.07774297 seconds

Converting cached_user_roles…19 records converted in 0.084811898 seconds

Converting cached_usergroup_members…0 records converted in 0.026931147
seconds

Converting compute_attributes…0 records converted in 0.027880435 seconds

Converting compute_profiles…3 records converted in 0.04463056 seconds

Converting compute_resources…0 records converted in 0.029843908 seconds

Converting config_group_classes…2 records converted in 0.038978174 seconds

Converting config_groups…1 records converted in 0.032271469 seconds

Converting domains…1 records converted in 0.039578378 seconds

Converting dynflow_coordinator_records…rake aborted!

NoMethodError: undefined method `fetch_value' for nil:NilClass

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/attribute_methods/read.rb:93:in
`_read_attribute'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/attribute_methods.rb:50:in
`__temp__36c6163737'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/core.rb:302:in
`init_with'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/persistence.rb:69:in
`instantiate'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/querying.rb:50:in
`block (2 levels) in find_by_sql'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/result.rb:51:in
`block in each'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/result.rb:51:in
`each'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/result.rb:51:in
`each'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/querying.rb:50:in
`map'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/querying.rb:50:in
`block in find_by_sql'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activesupport-4.2.5.1/lib/active_support/notifications/instrumenter.rb:20:in
`instrument'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/querying.rb:49:in
`find_by_sql'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation.rb:639:in
`exec_queries'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation.rb:515:in
`load'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation.rb:243:in
`to_a'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/relation/delegation.rb:46:in
`each'

/usr/share/foreman/lib/tasks/convert.rake:125:in `block (5 levels) in <top
(required)>'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in
`block in transaction'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/transaction.rb:184:in
`within_new_transaction'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/connection_adapters/abstract/database_statements.rb:213:in
`transaction'

/opt/rh/sclo-ror42/root/usr/share/gems/gems/activerecord-4.2.5.1/lib/active_record/transactions.rb:220:in
`transaction'

/usr/share/foreman/lib/tasks/convert.rake:124:in `block (4 levels) in <top
(required)>'

/usr/share/foreman/lib/tasks/convert.rake:83:in `each'

/usr/share/foreman/lib/tasks/convert.rake:83:in `block (3 levels) in <top
(required)>'

Tasks: TOP => db:convert:prod2dev

(See full trace by running task with --trace)

After this, also the db:migrate job fails on the PostgreSQL database.

Is my approach okay? Anything I need to tweak?

Any help would be appreciated…!!

Thanks,

Adrian

2

A bit of a stab in the dark here … :slight_smile:

If I recall correctly, the prod2dev task isn't great at creating every
table in the source db, but rather only the ones it thinks it needs.
However it then tries to copy every table, which can lead to errors when
it tries to copy into the target db. Try comparing the two dbs after it's
failed, and then see which tables are missing (i'm betting they'll be
plugin related…). If that looks realistic, you could create those tables
yourself and run the migrate again…

Tedious, I know. If that works, do log a bug about it in the issue tracker
:wink:

3

Hi Greg

Thank you for your answer!

I was trying to create the missing tables in the PostgreSQL database
before. There was a little success, but then there where other problems -
still with the prod2dev job.

On the way of trying to find another way to get the data in PostgreSQL I
was trying it with a test installation of a new Foreman instance without
the plugins we have in our production instance. The prod2dev job was
working… So your bet about the plugins seems to be right.

I think that it would be the best if I do a fresh install using
foreman-install (before it was a RPM based installation) directly on
PostgreSQL. So I would be able to get rid of unused plugins and have a good
gut feeling about the installation. Lucky me there are only about 160 hosts
and 900 smartclass overrides to move manually… So we can trust the
foreman installation in the future.

4

Fair enough! If you've a spare minute, it's probably worth logging the
bug about plugins not being represented in prod2dev…

http://projects.theforeman.org/projects/foreman/issues/new

Cheers
Greg