Page 1 of 1
Postgresql to MySQL migration
Posted: Tue Mar 22, 2016 5:03 am
by rajasegar
Is the Postgresql to MySQL migration ready?
We need to migrate to a new server and all the Notes plugin data is in the postgresql DB.
If we install a new instance, and do a backup restore of the DB, will the Notes plugin tables be migration over to MySQL?
Please advice thanks.
Re: Postgresql to MySQL migration
Posted: Tue Mar 22, 2016 10:21 am
by lmiltchev
If we install a new instance, and do a backup restore of the DB, will the Notes plugin tables be migration over to MySQL?
No, not really. The script will start up postgres process, and your object notes will be in posgress (not migrated to mysql).
If you had a completely new XI (not restored/upgraded), I believe the notes would be stored in the "xi_options" table of the "nagiosxi" db in mysql.
Re: Postgresql to MySQL migration
Posted: Tue Mar 22, 2016 7:48 pm
by rajasegar
lmiltchev wrote:If we install a new instance, and do a backup restore of the DB, will the Notes plugin tables be migration over to MySQL?
No, not really. The script will start up postgres process, and your object notes will be in posgress (not migrated to mysql).
If you had a completely new XI (not restored/upgraded), I believe the notes would be stored in the "xi_options" table of the "nagiosxi" db in mysql.
We are installing fresh XI instance and then restoring the DB backups.
Will the Notes tables be migrated to MySQL? Please confirm.
Re: Postgresql to MySQL migration
Posted: Wed Mar 23, 2016 9:36 am
by lmiltchev
We are installing fresh XI instance and then restoring the DB backups.
Will the Notes tables be migrated to MySQL? Please confirm.
The short answer - no, it won't. The long answer - we need more info.
What is the Nagios XI version that you are upgrading from/to? Restoring from a backup doesn't make any sense, unless it is done on the same version of XI.
For example, your old system is Nagios XI 2014R2.7. You spin a new Nagios XI 5.2.5 server, and try to restore from an old backup (from 2014R2.7). Bad idea. Your restore may fail, and even if it succeeds, you will be taken back to 2014R2.7... Then you will need to run the upgrade to fix the issue. If you really need to move to a new server, do the following:
1. Install the same version of XI on the new server (as the old one).
2. Backup your old server, and move the backup to the new one
3. Restore from the backup
4. Upgrade to the latest
In this case, you will still be using postgres. You notes will be stored in the postgres db (as before), and you won't lose your data.
Re: Postgresql to MySQL migration
Posted: Wed Mar 23, 2016 8:37 pm
by rajasegar
lmiltchev wrote:We are installing fresh XI instance and then restoring the DB backups.
Will the Notes tables be migrated to MySQL? Please confirm.
The short answer - no, it won't. The long answer - we need more info.
What is the Nagios XI version that you are upgrading from/to? Restoring from a backup doesn't make any sense, unless it is done on the same version of XI.
For example, your old system is Nagios XI 2014R2.7. You spin a new Nagios XI 5.2.5 server, and try to restore from an old backup (from 2014R2.7). Bad idea. Your restore may fail, and even if it succeeds, you will be taken back to 2014R2.7... Then you will need to run the upgrade to fix the issue. If you really need to move to a new server, do the following:
1. Install the same version of XI on the new server (as the old one).
2. Backup your old server, and move the backup to the new one
3. Restore from the backup
4. Upgrade to the latest
In this case, you will still be using postgres. You notes will be stored in the postgres db (as before), and you won't lose your data.
1) We are using the same version 5.2.5 between the new and old server
2) It is using different OS, RHEL vs CentOS
3) We cant restore from backup the whole OS.
Actually we just need the backup and restore for the notes, the rest we can create again from the config files
Re: Postgresql to MySQL migration
Posted: Thu Mar 24, 2016 12:41 pm
by lmiltchev
Actually we just need the backup and restore for the notes, the rest we can create again from the config files
If you imported config files manually, you wouldn't be able to import object notes. You best bet would be to do the following:
1. Backup your old XI instance:
Code: Select all
cd /usr/local/nagiosxi/scripts/
./backup_xi.sh
2. Move your backup to the new server
3. Restore from the backup you just transferred:
Code: Select all
cd /usr/local/nagiosxi/scripts/
./restore_xi.sh </full/path/to/backupfile.tar.gz>
This way, the object notes will be transferred along with other important items, i.e. performance data.
Re: Postgresql to MySQL migration
Posted: Thu Mar 24, 2016 8:17 pm
by rajasegar
lmiltchev wrote:Actually we just need the backup and restore for the notes, the rest we can create again from the config files
If you imported config files manually, you wouldn't be able to import object notes. You best bet would be to do the following:
1. Backup your old XI instance:
Code: Select all
cd /usr/local/nagiosxi/scripts/
./backup_xi.sh
2. Move your backup to the new server
3. Restore from the backup you just transferred:
Code: Select all
cd /usr/local/nagiosxi/scripts/
./restore_xi.sh </full/path/to/backupfile.tar.gz>
This way, the object notes will be transferred along with other important items, i.e. performance data.
Ok. Thanks. Will try it out and update.
Re: Postgresql to MySQL migration
Posted: Fri Mar 25, 2016 9:04 am
by lmiltchev
Ok. Thanks. Will try it out and update.
Let us know how it went. We will keep the thread open.
Re: Postgresql to MySQL migration
Posted: Tue Mar 29, 2016 12:44 am
by rajasegar
It has been a big hassle trying to use the restore script to restore to another server.
When restoring in the new, it is trying to restore to the original server. Luckily I did not have the hostnames defined yet.
Anyway there seems to be a bug in the restore_xi.sh script
Code: Select all
# Remove nagiosxi db from mysql if postgres is used instead
mysql -h "$cfg__db_info__nagiosql__dbserver" -u root --password=$mysqlpass < "DROP TABLE IF EXISTS nagiosxi;"
DROP TABLE can drop DB? Shouldn't it be DROP DATABASE or DROP SCHEMA?
Even if the SQL is corrected, the command above wont work. Please fix this.
Code: Select all
[root@MYUCBPNAGIAPP01 scripts]# mysql -h localhost -u root --password=nagiosxi < "DROP SCHEMA IF EXISTS nagiosxi;"
-bash: DROP SCHEMA IF EXISTS nagiosxi;: No such file or directory
Thanks
Re: Postgresql to MySQL migration
Posted: Tue Mar 29, 2016 2:36 pm
by lmiltchev
DROP TABLE can drop DB? Shouldn't it be DROP DATABASE or DROP SCHEMA?
Even if the SQL is corrected, the command above wont work. Please fix this.
I believe you are correct. Thank you for bring this to our attention!