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