Postgresql to Mysql migration methodology?

[SteveBeauchemin]

Is there a Manual Process defined for moving the nagiosxi database from Postgresql to Mysql.

I am setting up a 'Clean' Red Hat 7 system and will install the most recent version of Nagios XI there. Then I need to transition data from my older system the new setup. I currently have an offloaded Database on a dedicated system and will retain that on the new setup.

The only step I am unsure about is how to get the old PG data to the new Mariadb.

Is there a manual process defined that will accomplish this?

I have many Users defined, and many Dashboards in use. If someone could please provide the steps I need to take I would appreciate it. I see that this is not a new topic in the forum and that the Developers have been aware and working on this for maybe more than a year? I do not need automation for this, nor do I want to have to create users and dashboards from scratch. Please provide the information available at this time. Nothing is broken. I just need to know how to proceed properly and be successful.

Thanks

Steve B

[avandemore]

There is currently no migration path which removes the PosgreSQL dependency. If you want such a system, you would have to manually migrate the data or use the api where available.

Please see here for the official documentation on the migration process:

https://assets.nagios.com/downloads/nag ... ios-XI.pdf

[SteveBeauchemin]

Is there a guide as to how I can manually move the data from Postgresql on one host to MariaDB on a new host?

Please advise.

Steve B

[avandemore]

There is no guide, nor it is it supported in any way. Make sure you have a backup. I can give you some information to attempt this, but if something goes wrong this is out of scope so you'll be on your own.

First on your MySQL/MariaDB instance you'll need this stuff:

Code: Select all

# Create "nagiosxi" default database schema

CREATE DATABASE `nagiosxi`;
CREATE USER 'nagiosxi'@'localhost' IDENTIFIED BY 'n@gweb';
GRANT ALL ON nagiosxi.* TO 'nagiosxi'@'localhost';
USE `nagiosxi`;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_auditlog` (
    `auditlog_id` int auto_increment,
    `log_time` timestamp,
    `source` text,
    `user` varchar(200),
    `type` int,
    `message` text,
    `ip_address` varchar(45),
    `details` text,
    primary key (`auditlog_id`),
    index using btree (`log_time`),
    index using btree (`user`),
    index using btree (`type`),
    index using btree (`ip_address`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_commands` (
    `command_id` int auto_increment,
    `group_id` int default 0,
    `submitter_id` int default 0,
    `beneficiary_id` int default 0,
    `command` int NOT NULL,
    `submission_time` timestamp not null,
    `event_time` timestamp not null,
    `frequency_type` int default 0,
    `frequency_units` int default 0,
    `frequency_interval` int default 0,
    `processing_time` timestamp,
    `status_code` int default 0,
    `result_code` int default 0,
    `command_data` text,
    `result` text,
    primary key (`command_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_events` (
    `event_id` int auto_increment,
    `event_time` timestamp,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `status_code` smallint default 0 not null,
    `processing_time` timestamp,
    primary key (`event_id`),
    index using btree (`event_source`),
    index using btree (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_meta` (
    `meta_id` int auto_increment,
    `metatype_id` int default 0,
    `metaobj_id` int default 0,
    `keyname` varchar(128) not null,
    `keyvalue` text,
    primary key (`meta_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_options` (
    `option_id` int auto_increment,
    `name` varchar(128) not null,
    `value` text,
    primary key (`option_id`),
    index using btree (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_sysstat` (
    `sysstat_id` int auto_increment,
    `metric` varchar(128) not null,
    `value` text,
    `update_time` timestamp not null,
    primary key (`sysstat_id`),
    index using btree (`metric`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_usermeta` (
    `usermeta_id` int auto_increment,
    `user_id` int not null,
    `keyname` varchar(255) not null,
    `keyvalue` longtext,
    `autoload` smallint default 0,
    primary key (`usermeta_id`),
    index using btree (`autoload`),
    constraint `user_unique_key` unique (`user_id`, `keyname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_users` (
    `user_id` int auto_increment,
    `username` varchar(64) not null,
    `password` varchar(128) not null,
    `name` varchar(100),
    `email` varchar(128) not null,
    `backend_ticket` varchar(128),
    `enabled` smallint default 1,
    primary key (`user_id`),
    unique(`username`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` timestamp,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;USE `nagiosxi`;

ALTER TABLE xi_usermeta MODIFY keyvalue LONGTEXT;

ALTER TABLE xi_users ADD COLUMN api_key varchar(128) NULL;
ALTER TABLE xi_users ADD COLUMN api_enabled smallint DEFAULT 0 NOT NULL;

UPDATE xi_users SET api_enabled = 1, api_key = backend_ticket;

# Account security features
ALTER TABLE xi_users ADD COLUMN login_attempts smallint(6) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_attempt int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_password_change int(10) DEFAULT 0 NOT NULL;

# Security information
ALTER TABLE xi_users ADD COLUMN last_login int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited int(11) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN last_edited_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_by int(10) DEFAULT 0 NOT NULL;
ALTER TABLE xi_users ADD COLUMN created_time int(11) DEFAULT 0 NOT NULL;

# Event Queue table for more efficient Global Event Handlers
CREATE TABLE IF NOT EXISTS `nagiosxi`.`xi_eventqueue` (
    `eventqueue_id` int auto_increment,
    `event_time` int,
    `event_source` smallint,
    `event_type` smallint default 0 not null,
    `event_meta` text,
    primary key (`eventqueue_id`),
    unique(`eventqueue_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

You can use something like this:

• http://mysqlworkbench.org/2012/11/how-t ... on-wizard/
• Or use a dump of the Postgres nagiosxi db with some converter, there are more than one. Restore the conversion to the newly created MySQL nagiosxi db.

Then you'll need to update /usr/local/nagiosxi/html/config.inc.php to point to the correct nagiosxi db.

You also may wish to hold off for a more official supported process, we are looking into this.

[SteveBeauchemin]

Thank you.

That information helped me better understand.

I have a clean install of Nagios XI that is not using PostgreSQL.
I have an old system that I need to migrate.
If I use MySQL Workbench I can read the old PG contents and write the new MySQL.

If I use the Doc - Offloading MySQL to Remote Server - and take the nagiosxi piece out and substitute the above process, I should be good.

Thanks again. Feel free to close this now..

Steve B

[dwhitfield]

We did end up writing a guide yesterday at https://support.nagios.com/kb/article.php?id=560.

We'll leave this thread open so that if you run into any issues, we can keep them associated with this thread and update the guide accordingly. Thanks!

[SteveBeauchemin]

Unfortunately, the process detailed does not work for me. The PG Dump works fine and provides a large file of data. It runs in the blink of an eye.

The conversion fails.

Code: Select all

php pg2mysql_cli.php nagiosxi.psql nagiosxi.mysql myisam
Filesize: 38.5M
PHP Notice:  Undefined offset: 24 in /usr/local/src/Testing/PG-2-MySQL/pg2mysql-master/pg2mysql.inc.php on line 336
Reading    progress:  70%   position:   27.0M   line:     90000   sql chunk:        17  mem usage:   43M

Completed!     99389 lines          17 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 MyISAM
(Linux 2.6.32-642.6.2.el6.x86_64)nagios@crulnxicore01:/usr/local/src/Testing/PG-2-MySQL/pg2mysql-master
!> ll
total 39432
drwxr-xr-x 2 nagios users     4096 Dec  7 11:45 .
drwxr-xr-x 3 nagios users     4096 Dec  7 11:44 ..
-rw-r--r-- 1 nagios users    15145 Feb 21  2015 gpl.txt
-rw-r--r-- 1 nagios users     2751 Dec  7 11:45 nagiosxi.mysql
-rw-r--r-- 1 nagios users 40318590 Dec  7 11:44 nagiosxi.psql
-rw-r--r-- 1 nagios users     1534 Feb 21  2015 pg2mysql_cli.php
-rw-r--r-- 1 nagios users    13189 Feb 21  2015 pg2mysql.inc.php
-rw-r--r-- 1 nagios users     2370 Feb 21  2015 pg2mysql.php
-rw-r--r-- 1 nagios users     2985 Feb 21  2015 README

The result is a very small file that barely starts to setup the table data, and has no actual content.

That is fine with me though. Once I understood that it is a simple move of data from location A to location B and no other changes of any importance, I took a slightly different path. The basics are the same. The MySQL Workbench takes it in small steps, lets you verify and make changes if needed. just keep clicking 'next' until it is completed. I felt very comfortable with the results. I would prefer linux command line stuff, but I had no success there.

These are the steps I took - with great success. I use MySQL Workbench all the time anyway, so adding an ODBC connector was simple.

My notes follow: Test Test Test... always

1. Set PostgreSQL to allow remote connections
This is specifically to allow access for the system that will run the convert process

On the system running PostgreSQL
sudo vi /var/lib/pgsql/data/pg_hba.conf
Add the line
host all all [your IP here]/23 trust

sudo vi /var/lib/pgsql/data/postgresql.conf
Find configuration line that read as follows:
listen_addresses='localhost'
Change it to
listen_addresses='*'

Then restart PostgreSQL
As the nagios user, run
sudo /usr/local/nagiosxi/scripts/manage_services.sh restart postgresql
Or as root pick one...
/etc/init.d/postgresql restart
service postgresql restart
systemctl restart postgresql

2. Install PostgreSQL ODBC Driver on your workstation
Set it up in the ODBC Control Panel and add the host, user, pass, port, etc.
Make sure it tests okay.

Acquire the driver from here - Note newest versions are at the bottom.
http://www.postgresql.org/ftp/odbc/versions/msi/

“First you download ODBC driver, then you installed it. After that go to START->Program->Administrative tools then you select Data Source ODBC then you double click on the same after that you select PostgreSQL 30 then you select configure then you provide proper details such as db name user Id host name password of the same database in this way you will configure your DSN connection.
Then you go on next tab system DSN then you select ADD tab then select postgreSQL_ANSI_64X ODBC after you that you have created PostgreSQL ODBC connection.” or something like that… Use your own judgement.

Use these resources as a guide to setup the driver
https://odbc.postgresql.org/
http://stackoverflow.com/questions/6796 ... on-windows
Once the ODBC driver tests says Okay, proceed.
If not okay, back to step 1 and verify your work, do not forget the PG restart.

3. Use MySQL Workbench Data Migration tool
http://dba.stackexchange.com/questions/ ... l-to-mysql

“Use MySQL Workbench
The following steps are based on MySQL Workbench 6.1
Source & Target
Open MySQL Workbench and start the Migration Wizard Database > Migration Wizard... Set up connection to your source database (PostgreSQL) Setup connection to your target database (MySQL) Select the schemas to migrate
Object Migration
Select the source objects to migrate Review and manual editing proposed migration Target creation options Create schemas Create target results
Data Migration
Data transfer setup Bulk data transfer” - or however it works for you.

4. If this process worked for you then do it for real.
Stop Nagios, database, ndo2db, etc.
Run step 3 again to transfer the data to the real system / Database.
Tell Nagios to use MySQL now
sed -i 's/pgsql/mysql/' /usr/local/nagiosxi/html/config.inc.php
Start Nagios, database, ndo2db, etc.

I was going to post this eventually, but I felt that it should be put out there now as I did try your process without success. In case other folks get your method to work, that is great. If not, then they have an additional method to try. But I understand the methodology now and to me that makes all the difference.

Thanks

Steve B

[dwhitfield]

Thanks so much for the detail! Is it ok if I lock the thread now?

[SteveBeauchemin]

Sure, from where I sit you can close this.

Thanks

Steve B