postgresql problems after migration from hw to VM

[jwelch]

We are trying to migrate our Nagios XI server from hardware to a VM. We are keeping the hostname and IP address so I was hoping there would be no issues. However, after the migration, postgresql will not start. Could this be some sort of licensing issue?

[lmiltchev]

Do you see any errors when you try to start postgresql? Anything in the logs?

Run the following commands and show the output:

Code: Select all

service postgresql restart
service postgresql status
cat /var/lib/pgsql/pgstartup.log
cat /var/lib/pgsql/data/pg_log/postgresql-Tue.log

Can you run the vacuum on postgres?

Code: Select all

echo "vacuum;vacuum analyze;vacuum full;"|psql nagiosxi postgres

[jwelch]

Thanks, I forgot about the pg_log/ files. I suspect the db was corrupted on the VM:
From postgresql-Tue.log:
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 25/B51A2D58
LOG: record with zero length at 25/B5930DF0
LOG: redo done at 25/B5930670
LOG: last completed transaction was at log time 2015-08-11 11:10:27.097825-04
FATAL: xlog flush request 25/B97C0C20 is not satisfied --- flushed only to 25/B5930DF0
CONTEXT: writing block 0 of relation base/16385/2619_vm
LOG: startup process (PID 41537) exited with exit code 1
LOG: aborting startup due to startup process failure

We shutdown the postgres db on the hw box and did another rsync, but still getting the above.



service postgresql restart:
stop PASSED
start FAILED

service postgresql status:
postmaster is stopped

/var/lib/pgsql/pgstartup.log hasn't been modified since 2012:
-rw-------. 1 postgres postgres 1152 Aug 15 2012 pgstartup.log
cat pgstartup.log
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

/usr/bin/postgres -D /var/lib/pgsql/data
or
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

[jwelch]

the original hw box is still available until we are sure the VM will work, so next I'll do a backup from it and restore to the VM
and see if that fixes the db.

[jwelch]

doh! Looks like I can't do vacuum or restore until the db is running.

[tmcdonald]

Is that a subtle way of saying the issue has been resolved?

[jwelch]

No. I tried moving the data dir then doing a postgres initdb. That got me to the point where I can start postgres, but there is no nagiosxi database.
I was hoping an import would create the db, but that didn't work. I also can't do a pg_dump from the hw box and pump the sql file into the newdb since the nagiosxi db doesn't exist.
If I had the command to create the db, I might be able to populate it. I guess I could download nagios xi and look through the install to see if I can find the script that creates the postgres db. Do you happen to have something handy that I can use?

[ssax]

Are you trying to go from and to the same version of XI?

If so, have you tried just doing a backup and restore?

Code: Select all

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

Then on the new server:

Code: Select all

cd /usr/local/nagiosxi/scripts
./restore_xi.sh /path/to/your/backup_file.tar.gz

That should take care of everything.

[jwelch]

That was the 2nd thing I tried.
Restore to the original database failed since postgres won't start.
Restore after initdb failed since there is no nagiosxi db.

Trying to restore to original database (postgres fails to start):
Restoring PostgresQL databases...
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
Error restoring PostgresQL database 'nagiosxi' !

after initdb:
Restoring PostgresQL databases...
psql: FATAL: Ident authentication failed for user "nagiosxi"
Error restoring PostgresQL database 'nagiosxi' !

[jwelch]

progress...when I blew away the corrupted db, it installed the default pg_hba.conf which uses IDENT authentication.
I changed it to match my old config...and had to create a nagiosxi role...then had to allow logins for nagiosxi...trying restore again.