While Foreman supports both MySQL and Postgresql as a database management system, Katello requires Postgresql. If you want to add the Katello plugin on an existing Foreman setup with MySQL, you need to migrate your database. But there are other reasons why you might want to switch your database management system. This blog post describes what steps are required to migrate your data.
While this blog post describes the required steps to migrate from MySQL to Postgresql, it can easily be adapted to migrate from Postgresql to MySQL.
First of all, a word of caution: This is not for the faint-hearted. And even fearless sysadmins should take backups before starting with this task. There is no guarantee, this will work. These steps have been tested with Foreman 1.15 but should work with other versions as well.
Postgresql and MySQL are both battle-tested open source database management systems. They have specific advantages and disadvantages. The most important reason to prefer one over the other is the available know-how in your organization. If you have a dedicated MySQL team that can provide a database for you, then you should by all means prefer that. If you do not have anybody who can provide you with a database on either MySQL or Postgresql, I’d tend to the latter as Postgresql seems a little more robust.
This blog post assumes that you already have the source and target databases set up. Make sure to setup the Postgresql database with UTF-8 enabled.
First of all, you need to install support for either Postgresql or MySQL to Foreman. One of them will be missing.
yum install -y foreman-postgresql adds support for Postgresql,
yum install -y foreman-mysql2 does the same for MySQL.
Next, stop all your foreman instances and make sure, they stay stopped for good. On RedHat systems, you need to stop the
httpd and (if applicable)
foreman-tasks service. Make sure that neither Puppet nor a monitoring system automatically starts these services again.
You can issue the MySQL command
SHOW FULL PROCESSLIST; to show you all active connections to your MySQL database. Make sure, there are none.
If you are using the foreman-tasks plugin, you need to patch the script we are using to migrate the data. If you are not sure, wether you use this plugin, check if you have an entry
Tasks in Foreman’s Monitor menu. If you are still not sure, it is safe to apply the patch in any case.
wget -O /tmp/convert-tasks-patch.diff https://gist.githubusercontent.com/timogoebel/cd29e5206678ca0e0d78222af70583ec/raw/bbe998624098c1c3e656fe2582d33b0d9f9285aa/0001-convert-skip-foreman-tasks-tables.patch yum install patch -y cd /usr/share/foreman patch -p1 < /tmp/convert-tasks-patch.diff
The foreman user that has access to your Postgresql database and is used for the conversion temporarily needs super user privileges.
## postgres@postgres:~ $ su - postgres ## postgres@postgres:~ $ psql psql (9.6.2) Type "help" for help. postgres=# ALTER USER foreman WITH SUPERUSER; ALTER ROLE postgres=#
To migrate the data from one database to the other, we will use a rake task. You might already be familiar to this task from upgrading Foreman to a newer version.
foreman-rake -T lists all available tasks. We will be using
foreman-rake db:convert:prod2dev to migrate the data.
Foreman internally uses a development framework called Rails. In Rails, you can have a specific configuration for e.g. a development and a production environment. Our prod2dev rake task assumes, that the source database is configured as the production database and the new target database is configured as the development database. All data in the development database will be overwritten.
You can configure the new database as Foreman’s new development database by adding a new connection in
It may look like this:
# The new target database development: adapter: postgresql host: postgres.example.com port: 5432 database: foreman username: foreman password: "foreman" pool: 25 # The old source database production: adapter: mysql2 host: mysql.example.com port: 3306 database: foreman username: foreman password: "foreman" pool: 25
The new database needs to be set up with all the tables required for Foreman to operate, so in order to prepare the new database to receive the actual data, we need to run all of Foreman’s database migrations.
forman-rake db:migrate RAILS_ENV=development
Next, we can finally migrate all the data. This will take a long time depending on the size of your database. It is highly recommended to run this in a screen or tmux session.
Postgresql uses sequences in order to store the next free primary key id for a table. These sequences do not get updated by the rake tasks and have to be adjusted manually. On MySQL, this is not needed as the concept of sequences is not known there.
The Postgresql Wiki has a script to repair the sequences. Copy the script and save it to a file, e.g.
sequences.sql. Then run it like this:
su - postgres vim sequences.sql # Paste the script and save the file psql foreman -Atq -f reset.sql -o temp psql -f temp --username postgres foreman rm temp
Then, make sure to resore the original permissions of your foreman database user. This can be done on Postgresql by issuing this SQL command.
ALTER ROLE "foreman" NOSUPERUSER;
To switch your Foreman instance to the new database, you need to change the production database configuration in
/etc/foreman/database.yml to point to the development installation we configured earlier. You can either do this manually or re-run foreman-installer with the new parameters for Postgresql.
Then you can finally start Foreman’s services again.
systemctl start httpd.service systemctl start foreman-tasks.service
This blog post showed, how you can change your database management system and migrate Foreman’s data. This can be one step of adding Katello on top of an existing Foreman setup or changing to a database management system you feel more comfortable with. We have noticed a small speed improvement with Postgresql over MySQL. But we changed from synchronous multi-master replication across datacenters to asynchronous replication, so the comparison is not fair. In addition, we did not tune the Postgresql cluster at all. Setting up a Postgresql cluster with synchronous replication was harder to do then a MySQL cluster with synchronous galera replication. The latter just works, the former requires you to take care of binary log files and does not offer an automatic failover. Although MySQL was easier to operate, Postgresql still feels more stable and robust in production.