Page 1 of 2
Remove postgres
Posted: Mon Mar 07, 2016 3:38 pm
by 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)
Re: Remove postgres
Posted: Mon Mar 07, 2016 5:06 pm
by 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/
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
Re: Remove postgres
Posted: Tue Mar 15, 2016 10:49 am
by 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.
Re: Remove postgres
Posted: Tue Mar 15, 2016 4:25 pm
by 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.
Re: Remove postgres
Posted: Tue Mar 15, 2016 8:58 pm
by 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?
Re: Remove postgres
Posted: Tue Mar 15, 2016 9:37 pm
by 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.
Re: Remove postgres
Posted: Wed Mar 16, 2016 7:39 am
by eloyd
Thanks, @box. We'll test this out today.
Re: Remove postgres
Posted: Wed Mar 16, 2016 11:44 am
by rkennedy
@jkinning / @eloyd - let us know if either of you have any further questions.
Re: Remove postgres
Posted: Thu Mar 17, 2016 2:25 pm
by 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.
Re: Remove postgres
Posted: Thu Mar 17, 2016 2:31 pm
by 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.