Restore NagiosXI

[twigg0]

Hi all,
unfortunately one of my colleagues has made a mess with Core config manager and we had to restore Nagios.
We’re using automysqlbackup and autopostgresbackup to backup Nagios every day.
I had no problems to restore database NagiosQL and dabatabase Nagios using mysql console.

mysql –p –u root –h localhost nagios < …..file.sql
mysql –p –u root –h localhost nagiosql < ….fileql.sql


I received errors instead when I tried to restore postgresql, why??

Thanks in advance

twigg0

Code: Select all

________________________________________
[root@snagxi01 nagiosxi]# su - postgres
-bash-4.1$ psql nagiosxi < /BackupDP2/postgresql/daily/nagiosxi/nagiosxi_2013-03-08.Friday.sql
SET
SET
SET
SET
SET
SET
ERROR:  database "nagiosxi" already exists
ALTER DATABASE
You are now connected to database "nagiosxi".
SET
SET
SET
SET
SET
SET
SET
ERROR:  relation "if_command_id_seq" already exists
ALTER TABLE
setval
--------
     20
(1 row)

ERROR:  relation "if_meta_id_seq" already exists
ALTER TABLE
setval
--------
     16
(1 row)

ERROR:  relation "if_option_id_seq" already exists
ALTER TABLE
setval
--------
     25
(1 row)

ERROR:  relation "if_sysstat_id_seq" already exists
ALTER TABLE
setval
--------
     15
(1 row)

ERROR:  relation "if_user_id_seq" already exists
ALTER TABLE
setval
--------
     14
(1 row)

ERROR:  relation "if_usermeta_id_seq" already exists
ALTER TABLE
setval
--------
    142
(1 row)

SET
SET
ERROR:  relation "xi_auditlog" already exists
ALTER TABLE
ERROR:  relation "xi_auditlog_auditlog_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
setval
--------
  13251
(1 row)

ERROR:  relation "xi_commands_command_id_seq" already exists
ALTER TABLE
setval
--------
   6236
(1 row)

ERROR:  relation "xi_commands" already exists
ALTER TABLE
ERROR:  relation "xi_events_event_id_seq" already exists
ALTER TABLE
setval
--------
618876
(1 row)

ERROR:  relation "xi_events" already exists
ALTER TABLE
ERROR:  relation "xi_meta_meta_id_seq" already exists
ALTER TABLE
setval
--------
619395
(1 row)

ERROR:  relation "xi_meta" already exists
ALTER TABLE
ERROR:  relation "xi_options_option_id_seq" already exists
ALTER TABLE
setval
--------
    127
(1 row)

ERROR:  relation "xi_options" already exists
ALTER TABLE
ERROR:  relation "xi_sysstat_sysstat_id_seq" already exists
ALTER TABLE
setval
--------
     99
(1 row)

ERROR:  relation "xi_sysstat" already exists
ALTER TABLE
ERROR:  relation "xi_usermeta_usermeta_id_seq" already exists
ALTER TABLE
setval
--------
   2004
(1 row)

ERROR:  relation "xi_usermeta" already exists
ALTER TABLE
ERROR:  relation "xi_users_user_id_seq" already exists
ALTER TABLE
setval
--------
     93
(1 row)

ERROR:  relation "xi_users" already exists
ALTER TABLE
ALTER TABLE
ERROR:  duplicate key value violates unique constraint "xi_auditlog_pkey"
CONTEXT:  COPY xi_auditlog, line 1: "11953      2013-02-15 10:02:24     Nagios XI       vaglia  4       User reconfigured host 'vrctrbck01'     10.1.2.179"
ERROR:  duplicate key value violates unique constraint "xi_meta_pkey"
CONTEXT:  COPY xi_meta, line 1: "2299   4       0       windowsserver__sfiletq01__      a:5:{s:8:"hostname";s:9:"sfiletq01";s:7:"address";s:18:"sfiletq0..."
ERROR:  duplicate key value violates unique constraint "xi_options_pkey"
CONTEXT:  COPY xi_options, line 1: "1   nagiosql_username       nagiosxi"
ERROR:  duplicate key value violates unique constraint "xi_sysstat_pkey"
CONTEXT:  COPY xi_sysstat, line 1: "16  reportengine    a:1:{s:10:"last_check";i:1362726001;}   2013-03-08 08:00:01.947463"
ERROR:  duplicate key value violates unique constraint "xi_usermeta_pkey"
CONTEXT:  COPY xi_usermeta, line 1: "1358       18      enable_notifications    0       0"
ERROR:  duplicate key value violates unique constraint "xi_users_pkey"
CONTEXT:  COPY xi_users, line 1: "79    gsirm   5f4dcc3b5aa765d61d8327deb882cf99        Gruppo Sistemistico RM  [email protected]   n5e9v..."
ERROR:  multiple primary keys for table "xi_auditlog" are not allowed
ERROR:  multiple primary keys for table "xi_commands" are not allowed
ERROR:  multiple primary keys for table "xi_events" are not allowed
ERROR:  multiple primary keys for table "xi_meta" are not allowed
ERROR:  multiple primary keys for table "xi_options" are not allowed
ERROR:  multiple primary keys for table "xi_sysstat" are not allowed
ERROR:  multiple primary keys for table "xi_usermeta" are not allowed
ERROR:  relation "xi_usermeta_user_id_key" already exists
ERROR:  multiple primary keys for table "xi_users" are not allowed
ERROR:  relation "xi_users_username_key" already exists
ERROR:  relation "event_time" already exists
ERROR:  relation "xi_auditlog_ip_address" already exists
ERROR:  relation "xi_auditlog_log_time" already exists
ERROR:  relation "xi_auditlog_source" already exists
ERROR:  relation "xi_auditlog_type" already exists
ERROR:  relation "xi_auditlog_user" already exists
ERROR:  relation "xi_commands_event_time_idx" already exists
ERROR:  relation "xi_meta_keyname_idx" already exists
ERROR:  relation "xi_meta_metaobj_id_idx" already exists
ERROR:  relation "xi_meta_metatype_id_idx" already exists
ERROR:  relation "xi_options_name_idx" already exists
ERROR:  relation "xi_sysstat_metric_idx" already exists
ERROR:  relation "xi_usermeta_autoload_idx" already exists
REVOKE
REVOKE
GRANT
GRANT
-bash-4.1$

[abrist]

Just as a heads up, there is a backup script for XI located at:

Code: Select all

/usr/local/nagiosxi/scripts/backup_xi.sh

Did you also backup the nagiosxi database?

[twigg0]

i use only automysqlbackup and autopostgresbackup and restore with procedure on the pdf of nagios xi

* first restore nagios db
* second restore nagiosql db
* third restore postgres

[slansing]

For such things we recommend reverting to a previous snapshot if one is available rather than making a complete database backup and trying to restore it to the same Nagios XI server. You can access Configuration Snapshots and the revision tool at:

Configure > Core Config Manager > Configuration Snapshots.

OR: Admin > Configuration Snapshots.

And simply click the green arrow icon next to the latest good snapshot. Is this something that will work for you?

[twigg0]

yeah but i dont want to revert to some snapshot, i need to restore postgresql, someone could help me to find how can I restore postgreSQL?
No problem to restore MySQL
Please help me...

[abrist]

If you do not wish to use the supported backup and restore options, all I can do is refer you to the postgresql documentation:
http://www.postgresql.org/docs/8.1/static/backup.html