Remove postgres

[jkinning]

I started running Nagios XI during the 2014 release which used both postgres and MySQL. I was hearing there was a method to migrate everything over to MySQL and remove postgres but I cannot find the procedure or scripts in doing so. I am currently running Nagios XI 5.2.5.

Here is what I am seeing but not sure if any of this is actually still being used.

Code: Select all

echo '\d' | psql nagiosxi nagiosxi
                     List of relations
 Schema |            Name             |   Type   |  Owner
--------+-----------------------------+----------+----------
 public | if_command_id_seq           | sequence | nagiosxi
 public | if_meta_id_seq              | sequence | nagiosxi
 public | if_option_id_seq            | sequence | nagiosxi
 public | if_sysstat_id_seq           | sequence | nagiosxi
 public | if_user_id_seq              | sequence | nagiosxi
 public | if_usermeta_id_seq          | sequence | nagiosxi
 public | xi_auditlog                 | table    | nagiosxi
 public | xi_auditlog_auditlog_id_seq | sequence | nagiosxi
 public | xi_commands                 | table    | nagiosxi
 public | xi_commands_command_id_seq  | sequence | nagiosxi
 public | xi_events                   | table    | nagiosxi
 public | xi_events_event_id_seq      | sequence | nagiosxi
 public | xi_incidents                | table    | nagiosxi
 public | xi_incidents_id_seq         | sequence | nagiosxi
 public | xi_meta                     | table    | nagiosxi
 public | xi_meta_meta_id_seq         | sequence | nagiosxi
 public | xi_options                  | table    | nagiosxi
 public | xi_options_option_id_seq    | sequence | nagiosxi
 public | xi_sysstat                  | table    | nagiosxi
 public | xi_sysstat_sysstat_id_seq   | sequence | nagiosxi
 public | xi_usermeta                 | table    | nagiosxi
 public | xi_usermeta_usermeta_id_seq | sequence | nagiosxi
 public | xi_users                    | table    | nagiosxi
 public | xi_users_user_id_seq        | sequence | nagiosxi
(24 rows)

[lmiltchev]

We don't have such a script yet. I was playing around last week, trying to convert postgres to mysq on one of my test boxes. Here's what I tried.

OLD (PSQL) XI SERVER:

Code: Select all

cd /tmp
wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2
bzip2 -d pg2mysql-1.9.tar.bz2 && tar xvf pg2mysql-1.9.tar
cd pg2mysql-1.9
pg_dump -U nagiosxi -E UTF8 --disable-dollar-quoting --inserts --data-only --format p -f nagiosxi.sql && sed -i '/^\(SET \|SELECT \)/d' nagiosxi.sql
php pg2mysql_cli.php nagiosxi.sql nagiosxinew.sql

scp nagiosxinew.sql root@remote_server:/tmp/

NEW (MySQL) XI SERVER:

Code: Select all

cd /tmp
mysqldump -d -uroot -pnagiosxi --add-drop-table nagiosxi > nagiosxiorig.sql
mysql -pnagiosxi nagiosxi < nagiosxiorig.sql && mysql -pnagiosxi nagiosxi < /tmp/nagiosxinew.sql

Important: This worked for me, that there is no guarantee that it will work for you. I have a very small test VM. This is just an initial attempt to start working on the pg2mysql conversion. It is not an official solution. Our developers haven't even looked at it yet. So, if you decide to use this - use it at your own risk. Try it in a test environment. Don't use it in production!

[eloyd]

So, aside from unsupported conversion, is there a way to migrate from pre-Nagios 5 installs that have been upgraded to Nagios 5+ to an install that was done with current Nagios 5+? We have a customer that wants to do this, but I'd rather not re-invent the wheel if we don't have to.

[hsmith]

Eric, I believe that should work just fine now. The script handles pgsql, it didn't in older XI5 releases, but it should take care of everything now. I would lab it first, I can do it tomorrow if I don't run out of time.

[eloyd]

@h, Pre-New Years, we had a 2014 system upgraded to 5 that we did a backup of, and tried to restore to a new 5 system. Did not go well. Is this the procedure you're referring to, or are you saying we need to manually update databases as @l has done?

[Box293]

As of XI 5.2.4, the restore script was fixed so that it would correctly detect that the restore is coming from an XI system that uses postrgres and makes sure that postgres is properly initiated and then continues to restore the old backup into postgres.

I would upgrade to at least 5.2.4 on the old system first before attempting to restore it to a fresh XI 5.x server.

XI 5.2.4 was not released until February.

[eloyd]

Thanks, @box. We'll test this out today.

[rkennedy]

@jkinning / @eloyd - let us know if either of you have any further questions.

[jkinning]

Will do.

For now you can close this as I am not sure when I will get a chance to validate your workaround on our test Nagios XI system.

[eloyd]

Would rather you didn't close it, since I hijacked it and haven't had a chance to test yet. But if you want, I can always open a new one.