I have a functioning and configured Icinga deployment running against MariaDB. I’d like to swap out the database backend to Postgres and am looking for some guidance - a pointer to a guide for doing the migration would be GREAT!
I currently have the following databases on Maria which I am assuming would all need to move:
Based on your screenshot, I would assume you are having four databases on MariaDB:
My advice would be to migrate each component to PostgreSQL independently. This gives you more time and allows you to debug if something goes south.
Unless you really need the Icinga 2 IDO, this would be a good point to stop using it and only use Icinga DB. By doing so, you have just reduced the amount of databases to migrate to three.
The Icinga DB daemon synchronizes the state from Icinga 2 to the relational database. So the only data which may need manual migration is the history.
In this case, consider if you need the history data, e.g., for SLA calculation or if you like to reminisce about the wonderful downtime once at four in the morning.
Nevertheless, start by importing the Icinga DB PostgreSQL schema. If you decide to ignore the history, you are done. Otherwise, take a look at all tables named with a _history suffix and manually migrate the data from MariaDB to PostgreSQL. For doing so, I would try dumping the data via the mysql command line utility, maybe as CSV, and try to import it via psgql. Of course, this is untested, but in general this should work.
I don’t know enough about the director to give you a good answer. Start by taking a look at the schema files or wait for someone who knows better.
Whereof one cannot speak, thereof one must be silent.
Fortunately, the Icinga Web 2 schema is quite short. Thus, you can try the same generic export, generic import trick I advised for the Icinga DB history above. Otherwise, if you are having just a few users, consider recreating these manually.
Hope, this will help you to get you started.
Thanks for your reply and advice on migrating one component at a time.
On the Incinga 2 IDO front, how would I know if I am using it/need it? I am assuming this datbase is for user consumption and not application consumption – IE the web UI and other Icinga components don’t use this. Am I correct?
Depends. Do you have an IdoMySqlConnection object configured in your Icinga 2? Are you using the monitoring module in Icinga Web 2? If you have Icinga DB running and you are using Icinga DB Web, you may not need it anymore.
Maybe try disabling the monitoring module in Icinga Web and verify that everything works as expected.
But what do you mean with this database being only for user consumption?
I did an icingacli module list and it came back with this:
MODULE VERSION STATE DESCRIPTION
director 1.11.3 enabled Director - Config tool for Icinga 2
incubator 0.22.0 enabled Incubator provides bleeding-edge libraries
monitoring 2.12.2 enabled Icinga monitoring module
I’m confused by what the difference is between IcingaDBWeb and IcingaWeb2.
If i disable monitoring then the web interface no longer shows me any monitoring data
But what do you mean with this database being only for user consumption?
This comment was with respect to the Icinga 2 IDO. Based on the description in the documentation, it sounded like Icinga 2 IDO is some sort of data export store that is intened to be used outside of Icinga for analysis. It was not clear if Icinga itself uses the data in this store for something.
icinga has two possibilities in storing historic data (without considering timeseriesdb like influx):
you are using the deprecated ido feature.
In order to view the historic data in a nice web interface there are two icingaweb2 modules:
and IcingaWeb2 is the software that can provides the useraccess to the icingaweb modules like monitoring or director
Apart from that:
The migration tool needs to access both the IDO and the Icinga DB databases. Please specify the connection details in the corresponding
idoandicingadbsections of the configuration.Both the IDO and Icinga DB support MySQL and PostgreSQL. You can migrate from and to both types, including from one type to the other.
So migration right to icingadb will be less painfull.
Apart from that:
for the database for icingaweb2 with users and settings like I totally aggree with:
For the director:
Best Regards
Nicolas
First off, I want to thank everyone on this thread for thier assistance! You folks have really been a huge help as a I familiarize myself with the inner workings of Icinga and try to modernize my installation and migrate the data to Postgres.
I am happy to report that I have migrated my current IDO MariaDB configuration to a Postgres IcingaDB deployment. Once I read (and worked through) the documentation it really was pretty straight forward. I did not migrate history (I tried, but the icingadb-migrate command would just hang on “connecting to databases” even though i verified the configuration for source and tagret were correct. I did not see any command line optoons to get additional information out of icingadb-migrate and there was never a cache file created – so i just gave up. Really, i never looked at the history anyway.
Below are my notes which may help someone else in the future:
Migrating from MariaDB Icinga IDO to Postgres IcingaDB
Validate that you either have Redis installed or install the Icingadb-redis package. In my case, I already had Redis installed and also the Icingadb-redis. There was no need for both, so I did a sudo apt-get purge icingadb-redisTheicinadb-redis` package uses a custom port (6380) for Redis, so I had to make sure that my installation configuration was adjusted accordingly to use the default Redis port of 6379.
Next, see if icingadb feature is enabled:
icinga2 feature list
In my case, it was not so I did a:
sudo icinga2 feature enable icingadb
sudo systemctl restart icinga2
I then followed these steps to setup and configure Icinga on Debian.
After enabling the icingadb feature, I had to go into the Icinga 2 Web interface, click on the Settings sprocket and select icingadb and the refresh its enabled status to turn it on.
Within the Icinga 2 Web Interface, I configured a resource for IcingaDB as follows:
I thine configured the IcingaDB module
And then… poof! everything just appeared in the web interfaxce!
Note: There may have been a service restart here; i can’t remember.
After poking around and validating that everything looked good I decided it was time to bid farwell to IDO. I performed the following cleanup steps:
sudo icinga2 feature disable ido-mysql
sudo systemctl stop icinga2
sudo apt-get purge icinga2-ido-mysql
Then finally, in Incinga Web, I went to go to Application and Resources and deleted the Icinga IDO resource.
I have successful gone though and migrated my Icinga 2 Web database to Postgres. As recommeneded in this thread, the easiest way was to really just recreate it by re-running through the setup process. Below are the steps I used. I’m also postying my LDAP configuration files since they have some filters others might find handy. I didn’t see a way to edit these files in the setup wizard.
Migrating the Icinga 2 Web Database
See if the setup module is enabled
icinga2 feature list
If not, enable it:
sudo icinga2 feature enable setup
Generate a setup token
icingacli setup token create
Create the the Postgess user and an empty database:
su -l postgres
createuser -P icingaweb
createdb -E UTF8 --locale en_US.UTF-8 -T template0 -O icingaweb icingaweb
In Icinga 2 Web interface, click on the Settings sprocket and select icingadb and the refresh its enabled status to turn it on, then in an Incognito window, navigate to http:///setup
When prompted, put in your token and walk through the Setup Wizard prompts.
After installation I had to reconifugure the director resource as it was missing.
I also tweaked the authentication fog files as folllows:
/etc/icingaweb2/authentication.ini
[icingaweb2]
filter = "!(objectClass=computer)"
backend = "msldap"
base_dn = "OU=People,DC=mycompany,DC=org"
domain = "mycompany.org"
resource = "icingaweb_ldap"
user_name_attribute = userPrincipalName
/etc/icingaweb2/groups.ini
[icingaweb2]
backend = "msldap"
resource = "icingaweb_ldap"
user_backend = "icingaweb2"
base_dn = "OU=Icinga,OU=Groups,DC=nycompany,DC=org"
nested_group_search=1
Here is a handy link to the Icinga Web 2 Authentciation documnetation.
Thanks for documenting the steps you have taken. Doing so helps future users, thus, thanks 
If now everything works for you, feel free to mark this thread as resolved.
great work,
just one thing, I would recommend not deleting the montioring module, just disable it. It is still part of icingaweb2 package and some modules may use some classes of the monitoring module.
Yeah, I learned that the hard way. Icinga Web was complaining about the monitoring module missing even though I had it disabled. I removed the delete from my post so others may avoid my pitfall.
This weekend’s task was to see if I could migrate Icinga Director from MariaDB to Postgres. I did a test deployment and things seem to be working ok!
Icinga Director
su -l postgres
psql -q -c "CREATE DATABASE icingadirector WITH ENCODING 'UTF8';"
psql icingadirector -q -c "CREATE USER icingadirector WITH PASSWORD 'password'; GRANT ALL PRIVILEGES ON DATABASE icingadirector TO icingadirector ; CREATE EXTENSION pgcrypto;"
Go into Icinga Web 2 and add a new Application Resource for Icinga Director
In Incinga Web 2, navigate to your Iconga Director module and set it to the new Resource. Let it create your the Director tables but DO NOT run Kickstart.
Logon to MariaDB and record all of yoru Icinga Director database tables that have data.
Create a director.load config file that will be used by pgloader as follows:
LOAD DATABASE
FROM mysql://icingadirector:xxxxxxxx@localhost/icingadirector
INTO postgresql://icingadirector:xxxxxxxx@localhost/icingadirector
WITH data only
SET search_path to 'icingadirector'
INCLUDING ONLY TABLE NAMES MATCHING 'yyyyyy'
ALTER SCHEMA 'icingadirector' RENAME TO 'public'
;
Temporarily change the database owner of the icingadirector database to the icingadirector account
Iteratively run pgloader specifying one table at a time by updating the director.load file’
Here is the order I used:
| Table Name | Sequence |
|---|---|
| icinga_apiuser | 1 |
| icinga_command | 2 |
| icinga_command_argument | 3 |
| director_datafield | 4 |
| icinga_command_field | 6 |
| icinga_command_inheritance | 7 |
| icinga_command_var | 8 |
| icinga_zone | 9 |
| icinga_endpoint | 10 |
| icinga_host | 11 |
| icinga_hostgroup | 12 |
| icinga_hostgroup_host | 13 |
| icinga_hostgroup_host_resolved | 14 |
| icinga_host_inheritance | 15 |
| icinga_notification | 16 |
| icinga_notification_inheritance | 17 |
| icinga_notification_states_set | 18 |
| icinga_notification_types_set | 19 |
| icinga_notification_usergroup | 20 |
| icinga_service_set | 21 |
| icinga_service | 22 |
| icinga_servicegroup | 23 |
| icinga_servicegroup_service | 24 |
| icinga_servicegroup_service_resolved | 25 |
| icinga_service_field | 26 |
| icinga_service_inheritance | 27 |
| icinga_service_var | 28 |
| icinga_user | 29 |
| icinga_usergroup | 30 |
| icinga_usergroup_user | 31 |
| icinga_user_inheritance | 32 |
| icinga_user_states_set | 33 |
| icinga_user_types_set | 34 |
| director_setting | 35 |
| director_generated_file | 36 |
| director_daemon_info | 37 |
| director_datafield_setting | 38 |
| director_activity_log | 39 |
| director_deployment_log | 40 |
| director_generated_config | 41 |
| director_generated_config_file | 42 |
Validate that each table’s rows are loaded without error; you may get some type conversion warnings, but I think that is OK.
Temporarily change the database owner of the icingadirector database back to the postgres user (or whoever was the original owner)
Go back into Incinga Web 2 and refresh the Iirector page. Look at your hosts, services, etc. All of your prior deployment logs shoud be there too.
NOTE:
I had some difficulty using the vbersion of pgloader that was installed by package ``3.6.10~devel". try as i might, I could not get it to connect to my Postgres instance. I pulled down the code and compiled my own version that i used following the instructions on the pgloader site. The steps below are for Debian, which is what I am running:
$ apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
$ git clone https://github.com/dimitri/pgloader /path/to/pgloader
$ cd /path/to/pgloader
$ make save
$ ./build/bin/pgloader --help
Thanks for sharing.
I have moved this topic to the “Tutorials and Guides” section and added some tags.
This should hopefully make it easier to find and not “drown” in the Icinga2 Section
@golaat Thanks you and good job in documenting the migration steps. Allow me to ask a few questions please.
What made you to switch to Postgres from MariaDB, maybe for some sort of performance gain?
Thank you
My primary driver was that I was using Postgres for almost everything else except for Icinga. I wanted to consolidate on a single database platform, and I was running both MariaDB and Postgres on the same VM. Beming abale to move Icinga to Postgres allowed me to remove the MariaDB install and basically improve performance by have a smaller VM Memory / CPU demand.
Digging this up and posting an update to this guides "Icinga Director” part
I needed to modify the order in whiche the tables are migrated, otherwise some things were missing (notifications or services for example).
The following steps worked for me:
NOTE: no migration of the historical data of the import or sync rules!
Activity log and deployment log will be migrated.
CREATE USER icingadirector WITH PASSWORD 'password';
CREATE DATABASE icingadirector WITH ENCODING 'UTF8' OWNER icingadirector;
GRANT ALL PRIVILEGES ON DATABASE icingadirector to icingadirector;
GRANT ALL ON SCHEMA public TO icingadirector;
Create Icinga Web resource
Add the created database as resource in Icinga Web. Then configure it for usage by the Icinga Director module and create the database schema 
After that change the resource back to your MySQL DB for now, to have a working Director.
# vim director_template.load
LOAD DATABASE
FROM mysql://<mysql director db user>:<password>@<your mysql db host>/<mysql director database name>
INTO postgresql://icingadirector:<password>@<your psql db host>/icingadirector
WITH data only, prefetch rows = 100
SET search_path to 'icingadirector'
INCLUDING ONLY TABLE NAMES MATCHING '@TABLE@'
ALTER SCHEMA '<mysql director database name>' RENAME TO 'public';
the prefetch rows = 100 was necessary for me, otherwise the pgloader would crash on bigger tables, like the activity or deployment log.
Then create a file with the tables you want to migrate
HINT: I ran the following on my MySQL DB to get the tables and their approx. size. I omitted tables with 0 rows.
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = 'icingadirector' order by table_rows;
# vim tables.txt
icinga_apiuser
director_datafield_category
director_datafield
director_datafield_setting
director_datalist
director_datalist_entry
icinga_command
icinga_command_argument
icinga_command_field
icinga_command_inheritance
icinga_command_var
icinga_zone
icinga_endpoint
icinga_host
icinga_hostgroup
icinga_hostgroup_host
icinga_hostgroup_host_resolved
icinga_host_field
icinga_host_inheritance
icinga_host_var
icinga_timeperiod
icinga_timeperiod_range
icinga_user
icinga_usergroup
icinga_usergroup_user
icinga_user_field
icinga_user_inheritance
icinga_user_states_set
icinga_user_types_set
icinga_user_var
icinga_notification
icinga_notification_field
icinga_notification_inheritance
icinga_notification_states_set
icinga_notification_types_set
icinga_notification_user
icinga_notification_usergroup
icinga_notification_var
icinga_service_set
icinga_service
icinga_servicegroup
icinga_servicegroup_service
icinga_servicegroup_service_resolved
icinga_service_field
icinga_service_inheritance
icinga_service_var
icinga_host_service_blacklist
icinga_dependency
icinga_dependency_inheritance
icinga_dependency_states_set
icinga_scheduled_downtime
icinga_scheduled_downtime_range
director_setting
import_source
import_source_setting
import_row_modifier
import_row_modifier_setting
sync_rule
sync_property
sync_run
import_run
director_activity_log
director_generated_file
director_generated_config
director_generated_config_file
director_deployment_log
branched_icinga_host
director_basket
director_basket_content
director_basket_snapshot
director_branch
director_branch_activity
director_daemon_info
director_job
director_job_setting
I then used a small wrapper script that went through the entries one by one (top to bottom), created a separate load file based on the template and then ran the pgloader for the migration.
# vim run_pgloader.sh
#!/bin/bash
TEMPLATE="director_template.load"
while read TABLE; do
echo "=== Processing table: $TABLE ==="
LOADFILE="load_$TABLE.load"
# Create load file by replacing @TABLE@ placeholder
sed "s/@TABLE@/$TABLE/" "$TEMPLATE" > "$LOADFILE"
# Run pgloader
pgloader "$LOADFILE"
echo "=== Finished: $TABLE ==="
echo
done < tables.txt
After the migration has finished you can change the datasource of the Director to your psql resource.
In case you have configured jobs (automatic import/sync/deploy) these will run after a short time.