Postgresql to MySQL migration

[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.

[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.

[rajasegar]

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.

[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.

[rajasegar]

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

[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.

[rajasegar]

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.

[lmiltchev]

Ok. Thanks. Will try it out and update.

Let us know how it went. We will keep the thread open.

[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

[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!