Database connection failed DB Connect Error

[cwireless]

Hi, In the GUI for NagiosXI we are getting this message:
DB Connect Error [nagiosxi]: Database connection failed DB Connect Error [nagiosxi]: Database connection failed

We followed the steps in the support page[root@nagioscwp ~]# echo $PATH
/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
[root@nagioscwp ~]# service httpd restart
Stopping httpd: [ OK ]
Starting httpd: [ OK ]
[root@nagioscwp ~]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
[root@nagioscwp ~]# pwd

This is what we see at log - Web Server.
nagiosxi/includes/components/xicore/status.php?show=services&host=CWP_VM_LIBWS2
[Fri Mar 22 08:09:22 2013] [error] [client 172.18.92.33] PHP Warning: pg_pconnect() [<a href='function.pg-pconnect'>function.pg-pconnect</a>]: Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"\nHINT: 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, referer: http://nagioscwp.cwpanama.com/nagiosxi/ ... &dest=auto#
[Fri Mar 22 08:09:22 2013] [error] [client 172.18.92.33] PHP Warning: pg_pconnect() [<a href='function.pg-pconnect'>function.pg-pconnect</a>]: Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"\nHINT: 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, referer: http://nagioscwp.cwpanama.com/nagiosxi/ ... &dest=auto#
[Fri Mar 22 08:09:22 2013] [error] [client 172.18.92.33] PHP Warning: pg_pconnect() [<a href='function.pg-pconnect'>function.pg-pconnect</a>]: Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"\nHINT: 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, referer: http://nagioscwp.cwpanama.com/nagiosxi/ ... &dest=auto#
[Fri Mar 22 08:09:22 2013] [error] [client 172.18.92.33] PHP Warning: pg_pconnect() [<a href='function.pg-pconnect'>function.pg-pconnect</a>]: Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "postgres"\nHINT: 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, referer: http://nagioscwp.cwpanama.com/nagiosxi/ ... &dest=auto#
[root@nagioscwp httpd]#

[cwireless]

we tried to vacuum and now it shows this message

[Fri Mar 22 09:43:48 2013] [error] [client 172.18.93.106] PHP Warning: pg_pconnect() [<a href='function.pg-pconnect'>function.pg-pconnect</a>]: Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "nagiosxi"\nHINT: Stop the postmaster and use a standalone backend to vacuum database "nagiosxi". in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-postgres64.inc.php on line 682, referer: http://nagioscwp.cwpanama.com/nagiosxi/ ... w=services
[Fri Mar 22 09:43:48 2013] [error] [client 172.18.93.106] PHP Warning: pg_pconnect() [<a href='function.pg-pconnect'>function.pg-pconnect</a>]: Unable to connect to PostgreSQL server: FATAL: database is not accepting commands to avoid wraparound data loss in database "nagiosxi"\nHINT: Stop the postmaster and use a standalone backend to vacuum database "nagiosxi". in /usr/local/nagiosxi/html/db/adodb/drivers/adodb-postgres64.inc.php on line 682, referer: http://nagioscwp.cwpanama.com/nagiosxi/ ... w=services

[mguthrie]

Lets start simple and try:

service postgresql restart

If that doesn't work, try vacuuming both the nagiosxi tables as well as the postgresql internal tables.
http://support.nagios.com/wiki/index.ph ... .22_in_log

If that doesn't work, we'll look at reinstalling the postgresql packages. On rare occasions after a hard power-off or if the disk fills up, I've had to help users reinstall postgresql afterwards.

[cwireless]

Hi mguthrie .

it seems we are going to need a postgres package reinstalation, could you please let us know the steps we need to follow.



Below is one of the message that we are receiving.
d> VACUUM;
WARNING: database "nagiosxi" must be vacuumed within 999754 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
WARNING: database "nagiosxi" must be vacuumed within 999753 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: xlog flush request 1E/27D701AC is not satisfied --- flushed only to 1E/279E3A08
CONTEXT: writing block 595 of relation 1663/16385/2619
backend> vaccu^H
WARNING: database "nagiosxi" must be vacuumed within 999752 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "vaccu" at character 1
backend>
WARNING: database "nagiosxi" must be vacuumed within 999751 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
backend> vacuum -f;
WARNING: database "nagiosxi" must be vacuumed within 999750 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "-" at character 8
backend> vacuum full -f
WARNING: database "nagiosxi" must be vacuumed within 999749 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "-" at character 13
backend> vacuum -help
WARNING: database "nagiosxi" must be vacuumed within 999748 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "-" at character 8
backend> reindex -d nagiosxi
WARNING: database "nagiosxi" must be vacuumed within 999747 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "-" at character 9
backend> REINDEX -d nagiosxi
WARNING: database "nagiosxi" must be vacuumed within 999746 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "-" at character 9
backend> REINDEX
WARNING: database "nagiosxi" must be vacuumed within 999745 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at end of input at character 9
backend> REINDEX;
WARNING: database "nagiosxi" must be vacuumed within 999744 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near ";" at character 8
backend> vacuum verbose
WARNING: database "nagiosxi" must be vacuumed within 999743 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
WARNING: database "nagiosxi" must be vacuumed within 999742 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: xlog flush request 1E/27D70100 is not satisfied --- flushed only to 1E/279E3A08
CONTEXT: writing block 596 of relation 1663/16385/2619
backend> pwd
WARNING: database "nagiosxi" must be vacuumed within 999741 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "pwd" at character 1
backend> exit
WARNING: database "nagiosxi" must be vacuumed within 999740 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "exit" at character 1
backend> \q
WARNING: database "nagiosxi" must be vacuumed within 999739 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "nagiosxi".
ERROR: syntax error at or near "\" at character 1

[mguthrie]

Lets start with these and see if that takes care of it.

Code: Select all

yum reinstall -y postgresql postgresql-devel

[cwireless]

[root@nagioscwp data]# yum reinstall -y postgresql postgresql-devel
Setting up Reinstall Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql.i386 0:8.1.23-6.el5_8 set to be updated
---> Package postgresql.i386 0:8.1.23-6.el5_8 set to be erased
---> Package postgresql-devel.i386 0:8.1.23-6.el5_8 set to be updated
---> Package postgresql-devel.i386 0:8.1.23-6.el5_8 set to be erased
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================
Package Arch Version Repository Size
====================================================================================================
Installing:
postgresql i386 8.1.23-6.el5_8 base 2.9 M
postgresql-devel i386 8.1.23-6.el5_8 base 1.2 M
Removing:
postgresql i386 8.1.23-6.el5_8 installed 11 M
postgresql-devel i386 8.1.23-6.el5_8 installed 3.6 M

Transaction Summary
====================================================================================================
Install 2 Package(s)
Update 0 Package(s)
Remove 2 Package(s)

Total download size: 4.0 M
Downloading Packages:
(1/2): postgresql-devel-8.1.23-6.el5_8.i386.rpm | 1.2 MB 00:00
(2/2): postgresql-8.1.23-6.el5_8.i386.rpm | 2.9 MB 00:00
----------------------------------------------------------------------------------------------------
Total 5.3 MB/s | 4.0 MB 00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Erasing : postgresql 1/4
Installing : postgresql 1/4
Erasing : postgresql-devel 2/4
Installing : postgresql-devel 2/4

Removed:
postgresql.i386 0:8.1.23-6.el5_8 postgresql-devel.i386 0:8.1.23-6.el5_8

Installed:
postgresql.i386 0:8.1.23-6.el5_8 postgresql-devel.i386 0:8.1.23-6.el5_8

Complete!
[root@nagioscwp data]#

we have restarted service nagios restart and the same

[lmiltchev]

We will have to do some digging on this. We will get back to you as soon as we find a possible solution to your problem. Thanks!

[cwireless]

Hi there,

just to let you know that we have recovered the system by following this link and restored a backup done last week.

http://www.postgresql.org/message-id/86 ... MANTEC.COM
<http://www.postgresql.org/message-id/86 ... MANTEC.COM>


Our local directory is /var/lib/pgsql/data'

[root@nagioscwp data]# su postgres -c 'pg_resetxlog -f -x 0x9A00000 -m 0x10000 -O 0x10000 -l 0x1,0xB67,0x58 /var/lib/pgsql/data'
Transaction log reset
[root@nagioscwp data]#

After that we killed all postgres processes in the OS and restarted the service; then we restored a backup
/usr/local/nagiosxi/scripts/restore_xi.sh /store/backups/nagiosxi/1363398618.tar.gz


When we check the /var/log/httpd/error_log we can see these messages :

WARNING: database "postgres" must be vacuumed within 10433163 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 10031874 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 10281903 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
[Thu Mar 21 09:35:50 2013] [error] [client 172.18.92.61] PHP Notice: Undefined offset: 0 in /usr/l
ocal/nagiosxi/html/includes/components/ccm/classes/Form_class.php on line 586, referer: http://nagio
scwp.cwpanama.com/nagiosxi//includes/components/ccm/index.php
[Thu Mar 21 09:35:50 2013] [error] [client 172.18.92.61] PHP Notice: Undefined offset: 0 in /usr/l
ocal/nagiosxi/html/includes/components/ccm/classes/Form_class.php on line 586, referer: http://nagio
scwp.cwpanama.com/nagiosxi//includes/components/ccm/index.php
WARNING: database "postgres" must be vacuumed within 8482441 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 10050021 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 9979469 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 8545351 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 8583338 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 9924494 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 9854399 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 9836226 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".
WARNING: database "postgres" must be vacuumed within 9827413 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "postgres".

[cwireless]

thanks .. after we restored the last backup, it works fine.

[lmiltchev]

I'm glad to hear that! I am locking the topic.