Home » Categories » Multiple Categories

Converting PostgreSQL to MySQL for Nagios XI

Overview

This document describes how to manually convert the existing nagiosxi database from PostgreSQL (the default database prior to Nagios XI 5) to MySQL/MariaDB (the default database since Nagios XI 5). The nagiosxi database is what contains all the Nagios XI interface settings like users, Dashboards and Views.

 

 

Target Audience

This document is intended for use by Nagios XI Administrators who wish to convert an existing PostgreSQL database to a MySQL database. The steps given in this article are in and of themselves rather basic, but ideally the Nagios XI Administrator performing the conversion should have a decent understanding of PostgreSQL and MySQL in order to troubleshoot any issues that may present themselves.

 

 

Take Backup

It is highly recommended that you take a backup of your Nagios XI sever before proceeding. The following documentation explains how to do this:

Documentation - Backing Up And Restoring Your Nagios XI System

 

 

Steps

The conversion has multiple steps:

  • Download The Converter

  • Dump Existing PostgreSQL Database

  • Convert The Database

  • Create The New MySQL Database

  • Import The Converted Database

  • Set MySQL User Permissions

  • Update The Nagios XI Configuration

  • Make Sure Everything Works

  • Decommission PostgreSQL

 

If something goes wrong - DON'T PANIC - there is a section for that at the bottom!

 

 

Establish Terminal Session

The following steps require you to establish a terminal session to your Nagios XI server as the root user.

 

 

Download Pg2MySQL Converter

Pg2MySQL is an open source (GPL) conversion utility written by james@lightbox.org. Download it from GitHub using the following commands:

cd /tmp
wget https://github.com/ChrisLundquist/pg2mysql/archive/master.zip
unzip master.zip
cd /tmp/pg2mysql-master/

 

 

Dump Existing NagiosXI PostgreSQL Database

You are going to remain in our current directory (should still be /tmp/pg2mysql-master ) to perform our PostgreSQL dump.

NOTE: If you've changed the PostgreSQL user from the default that was setup during the installation of Nagios XI, you'll have to adequately reflect that in the following command.

Execute the following command to dump the database:

pg_dump --no-acl --no-owner --format p --data-only -U nagiosxi nagiosxi > nagiosxi.psql

 

 

Convert The Database

Running the Pg2MySQL script is as simple as dumping the PostgreSQL database! The script takes the PostgreSQL dump file as the input file, and [attempts to] convert it to an equivalent MySQL dump into the output file. The output file will be called nagiosxi.mysql.

Nagios XI requires the database to be in MyISAM format, this will be defined in the command.

Execute the following command to convert the database:

php pg2mysql_cli.php nagiosxi.psql nagiosxi.mysql myisam

 

It should produce output similar to:

Completed!       337 lines          18 sql chunks

Notes:
 - No its not perfect
 - Yes it discards ALL stored procedures
 - Yes it discards ALL queries except for CREATE TABLE and INSERT INTO
 - Yes you can email us suggestsions: info[AT]lightbox.org
    - In emails, please include the Postgres code, and the expected MySQL code
 - If you're having problems creating your postgres dump, make sure you use "--format p --inserts"
 - Default output engine if not specified is InnoDB

 

 

Create The New MySQL Database

These steps will create a new MySQL database that will be used to import the converted database into. First you need to execute this command to download a file from Nagios Enterprises which simplifies creating the database:

wget https://assets.nagios.com/downloads/nagiosxi/scripts/nagiosxi_create_db.sql

 

The following command required the MySQL root password. The default password is nagiosxi, if you have changed the password then you will need to use that password instead. Execute the following command to create the database:

mysql -uroot -pnagiosxi < nagiosxi_create_db.sql

 

There will be no output when the command is successful.

 

 

Import The Converted Database

Now it's time to import the converted dump file to the newly created database.

First you need to make a change to the /etc/my.cnf file. You need to add the following line under the [mysqld] section:

max_allowed_packet=512M

 

This command will automatically make that change for you:

perl -l -n -i -e 'print $_; print "max_allowed_packet=512M" if(/\[mysqld\]/);' /etc/my.cnf

 

You need to restart MySQL/MariaDB after that change:

 

RHEL/CentOS 6.x

service mysqld restart

 

RHEL/CentOS 7.x

systemctl restart mariadb.service

 

To import the converted dump file to the newly created database execute the following command:

mysql -uroot -pnagiosxi nagiosxi --max_allowed_packet=512M --force < nagiosxi.mysql

 

There will be no output when the command is successful.

 

 

Set MySQL User Permissions

These two commands will create the user called nagiosxi with the password of n@gweb (the default password for an XI install) and apple the permissions, execute the following command:

mysql -u root -pnagiosxi -e "CREATE USER 'nagiosxi'@'localhost' IDENTIFIED BY 'n@gweb';"
mysql -u root -pnagiosxi -e "GRANT ALL ON nagiosxi.* TO 'nagiosxi'@'localhost'; FLUSH PRIVILEGES;"

 

There will be no output when the commands are successful.

 

 

Update the Nagios XI Configuration Files

You have to change the setting that tells Nagios XI how to communicate with the database., and we may need to remove a specific component.

All you need to do is change the dbtype the in the /usr/local/nagiosxi/html/config.inc.php file from pgsql to mysql  which can be done manually:

    "nagiosxi" => array(
        "dbtype" => 'mysql',
        "dbserver" => 'localhost',
        "user" => 'nagiosxi',
        "pwd" => 'n@gweb',
        "db" => 'nagiosxi',

 

The following commands will make the change for you (a backup of the file is also made):

cp -p /usr/local/nagiosxi/html/config.inc.php /usr/local/nagiosxi/html/config.inc.php.backup
sed -i 's/pgsql/mysql/' /usr/local/nagiosxi/html/config.inc.php

 

You will need to remove an old nagiosim component as well, execute the following command:

mv /usr/local/nagiosxi/html/includes/components/nagiosim ~/nagiosim.backup &>/dev/null

 

 

Make Sure Everything Works!

If everything went well so far, you can test it by stopping PostgreSQL for a moment and attempting to login to the Nagios XI server.

RHEL/CentOS 6.x

service postgresql stop

 

RHEL/CentOS 7.x

systemctl stop postgresql.service

 

Now open the Nagios XI web interface and attempt to login.

If you're able to login, then that means everything worked as planned and you can disable PostgreSQL.

 

RHEL/CentOS 6.x

chkconfig postgresql off

 

RHEL/CentOS 7.x

systemctl disable postgresql.service

 

It's also a good idea to reboot your Nagios XI server to be sure everything is working as expected.

 

 

Decommission PostgreSQL

If you would like to remove PostgreSQL from your server execute the following command:

yum remove -y postgresql

 

The following command will delete the old database:

rm -rf /var/lib/pgsql

 

 

What Happens if Something Goes Wrong!?

First and foremost: DON'T PANIC! You haven't deleted any PostgreSQL data.

You can roll back to using PostgreSQL and then contact Nagios XI Support Staff.

If you've stopped the PostgreSQL service:

service postgresql start || systemctl start postgresql

 

If you've already disabled the PostgreSQL service:

chkconfig postgresql on || systemctl enable postgresql

 

If you've already updated the config.inc.php file:

sudo cp -p /usr/local/nagiosxi/html/config.inc.php /usr/local/nagiosxi/html/config.inc.php.attempted
sudo cp -p /usr/local/nagiosxi/html/config.inc.php.backup /usr/local/nagiosxi/html/config.inc.php

 

 

After that, everything should be back exactly how it was before we ever attempted the conversion!

Now, you'll need to get in contact with the Nagios XI Support Staff. Make sure you're as descriptive as possible. Send the following files along with your request:

  • /usr/local/nagiosxi/html/config.inc.php.attempted

  • /usr/local/nagiosxi/html/config.inc.php

  • /tmp/pg2mysql-master/nagiosxi.psql

  • /tmp/pg2mysql-master/nagiosxi.mysql

 

 

All The Code In One Place

This is all of the code from all of the previous sections condensed into one block, so that you can copy and paste until your heart is content:

cd /tmp
wget https://github.com/ChrisLundquist/pg2mysql/archive/master.zip
unzip master.zip
cd /tmp/pg2mysql-master/
pg_dump --no-acl --no-owner --format p --data-only -U nagiosxi nagiosxi > nagiosxi.psql
php pg2mysql_cli.php nagiosxi.psql nagiosxi.mysql myisam
wget https://assets.nagios.com/downloads/nagiosxi/scripts/nagiosxi_create_db.sql
mysql -uroot -pnagiosxi < nagiosxi_create_db.sql
perl -l -n -i -e 'print $_; print "max_allowed_packet=512M" if(/\[mysqld\]/);' /etc/my.cnf
service mysqld restart || systemctl restart mariadb
mysql -uroot -pnagiosxi nagiosxi --max_allowed_packet=512M --force < nagiosxi.mysql
mysql -u root -pnagiosxi -e "CREATE USER 'nagiosxi'@'localhost' IDENTIFIED BY 'n@gweb';"
mysql -u root -pnagiosxi -e "GRANT ALL ON nagiosxi.* TO 'nagiosxi'@'localhost'; FLUSH PRIVILEGES;"
cp -p /usr/local/nagiosxi/html/config.inc.php /usr/local/nagiosxi/html/config.inc.php.backup
sed -i 's/pgsql/mysql/' /usr/local/nagiosxi/html/config.inc.php
mv /usr/local/nagiosxi/html/includes/components/nagiosim ~/nagiosim.backup &>/dev/null
service postgresql stop || systemctl stop postgresql
chkconfig postgresql off || systemctl disable postgresql

 

 

Final Thoughts

For any support related questions please visit the Nagios Support Forums at:

http://support.nagios.com/forum/

0 (0)
Article Rating (No Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Nagios XI - Removing All Historical Data
Viewed 917 times since Thu, Apr 12, 2018
Nagios XI - Integrating autoIT With Nagios
Viewed 590 times since Wed, Jan 27, 2016
Nagios XI - Custom Wizards, Components and Dashlets - NWC12
Viewed 743 times since Thu, Feb 4, 2016
Nagios XI - Enabling Memcached Support
Viewed 541 times since Wed, Jan 27, 2016
Nagios XI - Offloading NDO2DB To Remote Server
Viewed 407 times since Tue, Dec 18, 2018
Nagios XI - Database Optimization
Viewed 706 times since Thu, Jan 28, 2016
Nagios XI - License Maintenance Status Check
Viewed 1385 times since Thu, Feb 25, 2016
Configuring Your Server With A Static IP Address
Viewed 8680 times since Tue, Oct 11, 2016
Nagios XI - Checking System Status
Viewed 643 times since Wed, Jul 19, 2017
Nagios XI - Distributed Monitoring With NRDP
Viewed 699 times since Thu, Aug 17, 2017