wraparound data loss in database "postgres"

[QS1]

NagiosXI 2.9
centos 5.4 (final) VM (32 Bit)

1) Yesterday I was unable to login to the NagiosXI Web API..error "DB Connect Error [nagiosxi]: Database connection failed"
- I was able to login to nagios Core and see that the system was still monitoring correctly.
- reloading a VM snapshot taken the night before looked to have corrected the issue and accessing the NagiosXI web interface was again possible

2) Today im geting the same error again "DB Connect Error [nagiosxi]: Database connection failed"
-I tried running an upgrade (from 2.9 to 2.9) and see postgres is an issue
[root@nagiosxi tmp]# cd /tmp/nagiosxi
[root@nagiosxi nagiosxi]# ./upgrade
OLD VERSION: 320
Saving old crontab to /tmp/nagios.crontab.orig.1...
Removing XI cron jobs...
Saving old crontab to /tmp/root.crontab.orig.1...
Removing XI cron jobs...
Archive: sourceguardian/ixed4.lin.x86-32.zip
inflating: /usr/lib/php/modules/ixed.5.1.lin
Sourceguardian extension found for PHP version 5.1
Sourceguardian extension already in php.ini
Copying over new XI directory...
Building latest perms binary...
Updating NagiosQL...
NAGIOSQL-POST
Patching NDOUtils...
PHP Warning: pg_pconnect(): Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres". in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-postgres64.inc.php on line 682
DB Connect Error [nagiosxi]: Database connection failed
ERROR CONNECTING TO DATABASES!
Updating sequences...
Nagios XI Postgres Database Sequence Information

OLD VALUES
--------------
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_commands_command_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_events_event_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_meta_meta_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_options_option_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_sysstat_sysstat_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_usermeta_usermeta_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_users_user_id_seq =

psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
./nagiosxi/basedir/tools/fix_postgres_sequences.sh: line 17: [: -lt: unary operator expected
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
./nagiosxi/basedir/tools/fix_postgres_sequences.sh: line 25: [: -lt: unary operator expected
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
./nagiosxi/basedir/tools/fix_postgres_sequences.sh: line 33: [: -lt: unary operator expected
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
./nagiosxi/basedir/tools/fix_postgres_sequences.sh: line 41: [: -lt: unary operator expected
NEW VALUES
--------------
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_commands_command_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_events_event_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_meta_meta_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_options_option_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_sysstat_sysstat_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_usermeta_usermeta_id_seq =
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
xi_users_user_id_seq =

Installing new PNP templates...
Updating init script...
Enabling large install tweaks...
Fixing config file permissions...
Fixing htpasswd permissions...
Checking group memberships...
Installing new XI templates...
PHP Warning: pg_pconnect(): Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres". in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-postgres64.inc.php on line 682
DB Connect Error [nagiosxi]: Database connection failed
ERROR CONNECTING TO DATABASES!
NAGIOSQL LOGIN FAILED!
[root@nagiosxi nagiosxi]#

-I tried re-installing postgresql and received the following message
[root@nagiosxi ~]# yum reinstall -y postgresql postgresql-devel
Loaded plugins: fastestmirror
Setting up Reinstall Process
Loading mirror speeds from cached hostfile
* addons: mirrors.finalasp.com
* base: mirror.ash.fastserv.com
* epel: ftp.linux.ncsu.edu
* extras: centos.someimage.com
* updates: mirror.wiredtree.com
No package postgresql available.
* Maybe you meant: postgresql
No package postgresql-devel available.
* Maybe you meant: postgresql-devel
Nothing to do
[root@nagiosxi ~]#

- I tried vacuming the postgresql and get
[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@nagiosxi ~]# psql nagiosxi nagiosxi
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"?
[root@nagiosxi ~]#

Any other suggestions on how to proceed?

[lmiltchev]

Just to rule disk space issues, can you run the following commands, and show us the output?

Code: Select all

df -h
df -i

[QS1]

My apologies..i meant to include that in the original post:
[root@nagiosxi ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
108G 65G 38G 64% /
/dev/sda1 99M 18M 76M 20% /boot
tmpfs 1.5G 0 1.5G 0% /dev/shm
You have new mail in /var/spool/mail/root
[root@nagiosxi ~]# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/mapper/VolGroup00-LogVol00
28999680 220942 28778738 1% /
/dev/sda1 26104 41 26063 1% /boot
tmpfs 219850 1 219849 1% /dev/shm
[root@nagiosxi ~]#

[abrist]

- I tried vacuming the postgresql and get
[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@nagiosxi ~]# psql nagiosxi nagiosxi
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"?
[root@nagiosxi ~]#

You cannot run psql if the service is stopped. Try:

Code: Select all

service postgresql start
psql nagiosxi nagiosxi
VACUUM;
VACUUM ANALYZE;
VACUUM FULL;
\q

You will see messages like the following when running the above commands:

Code: Select all

WARNING:  skipping "pg_authid" --- only table or database owner can vacuum it

This is normal. You may need to run the above commands more than once if the CPU usage from postmaster is extremely high.
Next, vacuum the tables as the postgres user.

Code: Select all

psql postgres postgres
VACUUM;
VACUUM ANALYZE;
VACUUM FULL;
\q

http://support.nagios.com/wiki/index.ph ... .22_in_log

[QS1]

I get the following messages when trying those commands

[root@nagiosxi ~]# service postgresql start
Starting postgresql service: [ OK ]
[root@nagiosxi ~]# psql nagiosxi nagiosxi
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".


[root@nagiosxi ~]# psql postgres postgres
psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".
You have new mail in /var/spool/mail/root
[root@nagiosxi ~]#

[lmiltchev]

psql: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"
HINT: Stop the postmaster and use a standalone backend to vacuum database "postgres".

I am not sure if this is going to help, but you can try:

Code: Select all

service postgresql stop
su postgres
postgres --single -D /var/lib/pgsql/data/
vacuum;
vacuum analyse;
vacuum full;

CTRL+D 

Note: You need to run this one to break out of single user mode

Code: Select all

exit
service postgresql start

[QS1]

I receive the following message when following those steps: "FATAL: --single requires a value"

[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@nagiosxi ~]# su postgres
bash-3.2$ postgres --single -D /var/lib/pgsql/data/
FATAL: --single requires a value
bash-3.2$

[abrist]

Try:

Code: Select all

su postgres
echo "VACUUM FULL;" | postgres --single nagiosxi -D /var/lib/pgsql/data/
exit
service postgresql start

[QS1]

I receive the same message when following those steps: "FATAL: --single requires a value"

[root@nagiosxi ~]# service postgresql stop
Stopping postgresql service: [ OK ]
[root@nagiosxi ~]# su postgres
bash-3.2$ echo "VACUUM FULL;" | postgres --single nagiosxi -D /var/lib/pgsql/data/
FATAL: --single requires a value
bash-3.2$

[sreinhardt]

You must be on Cent\RHEL 5, not an issue at all, try this instead.

Code: Select all

service postgresql stop
su postgres
postgres -D /var/lib/pgsql/data/ nagiosxi
vacuum;
vacuum analyse;
vacuum full;

CTRL+D