Please help -- Postgres stopped responding ....

On 26/02/2011 15:04, savio rodriges wrote:

Hello Team,

I am new to Postgresql. Postgresql suddenly stoped responding. Below
is what I checked.

[root@server]$/etc/init.d/postgres status dead but pid file exists

[root@server]$ uptime 20:33:26 up 63 days, 22:57, 4 users, load
average: 1.73, 2.26, 2.43

My questions are,

1. Which postgresql database logfile needs to checked for information
on why postgres stoped responding?

The latest one. :-)

Seriously, check in postgresql.conf where the logs are set to go, and
look there for the most recent file.

2. What else besides logfile can be checked ?

What happens when you try to restart it? - Again, check the logs.

Maybe look in syslog to see if anything else happened at that time?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Hello Raymond,

FYI, attach is "ERROR REPORTING AND LOGGING" section from postgres.conf.

Following information is from /var/log

-bash-3.2$ cd /var/log

-bash-3.2$ ls -l mess*
-rw------- 1 root root 242 Feb 25 23:14 messages
-rw------- 1 root root 110 Feb 16 22:11 messages.1
-rw------- 1 root root 344 Feb 11 12:14 messages.2
-rw------- 1 root root 110 Jan 31 19:36 messages.3
-rw------- 1 root root 110 Jan 25 19:50 messages.4

[root@erp log]# cat messages.4
Jan 23 04:02:35 erp syslogd 1.4.1: restart.
Jan 25 19:50:38 erp auditd[2660]: Audit daemon rotating log files

[root@erp log]# cat messages
Feb 20 04:02:39 erp syslogd 1.4.1: restart.
Feb 21 01:54:24 erp auditd[2660]: Audit daemon rotating log files
Feb 22 10:00:01 erp auditd[2660]: Audit daemon rotating log files
Feb 25 23:14:29 erp auditd[2660]: Audit daemon rotating log files

Following is postgres log information,

-bash-3.2$ pwd
/var/lib/pgsql/data/pg_log

-bash-3.2$ ls -ltr
total 354000
-rw------- 1 postgres postgres 53001301 Feb 20 23:59 postgresql-Sun.log
-rw------- 1 postgres postgres 47584965 Feb 21 23:59 postgresql-Mon.log
-rw------- 1 postgres postgres 39886690 Feb 22 23:59 postgresql-Tue.log
-rw------- 1 postgres postgres 45764569 Feb 23 23:59 postgresql-Wed.log
-rw------- 1 postgres postgres 47464819 Feb 24 23:59 postgresql-Thu.log
-rw------- 1 postgres postgres 39972476 Feb 25 23:59 postgresql-Fri.log
-rw------- 1 postgres postgres 88413622 Feb 26 21:20 postgresql-Sat.log

-bash-3.2$ cat postgresql-Sat.log

LOG: duration: 0.074 ms
LOG: duration: 0.518 ms
LOG: duration: 0.041 ms
LOG: duration: 0.321 ms
LOG: duration: 0.022 ms

As you can see, there is not much information available for debuging purpose.

Please let me know how to configure "ERROR REPORTING AND LOGGING" section of postgres.conf to get all necessary postgres information in logfile for debuging/administration purpose.

Thanks for your help.

Savio

--- On Sat, 2/26/11, Raymond O'Donnell <rod@iol.ie> wrote:

On 26/02/2011 16:00, savio rodriges wrote:

Please let me know how to configure "ERROR REPORTING AND LOGGING"
section of postgres.conf to get all necessary postgres information in
logfile for debuging/administration purpose.

I think you need to look at log_min_messages and log_error_verbosity
anyway.... Full details here:

http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 02/26/11 7:04 AM, savio rodriges wrote:

Hello Team,

I am new to Postgresql. Postgresql suddenly stoped responding. Below is what I checked.

[root@server]$/etc/init.d/postgres status
dead but pid file exists

that probably means the server aborted for some reason without cleaning
up. this could happen if the system reset or power cycled without
being shutdown properly, or if the server crashed.

as others said, check the log file. if thats RHEL/CentOS/Fedora, odds
are its in /var/lib/pgsql/data/pg_log/postgresql-${DATE}_${SEQUENCE}.log
... look at the latest file. if you're running an older RHEL/etc stock
build of postgres, its possible its /var/lib/pgsql/server.log

to restart postgres, you'll need to delete the 'pid' file, likely
/var/lib/pgsql/data/postmaster.pid ... probably a good idea to verify
there aren't any postmaster processes still running detached,

$ ps -FU postgres
postgres 2707 1 0 38026 5696 1 Jan05 ? 00:18:50
/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 3921 2707 0 3281 1060 0 Jan05 ? 00:00:19
postgres: logger process
postgres 4524 2707 0 38058 112828 2 Jan05 ? 00:01:24
postgres: writer process
postgres 4525 2707 0 38060 1304 1 Jan05 ? 00:00:05
postgres: wal writer process
postgres 4526 2707 0 38353 2352 1 Jan05 ? 00:55:39
postgres: autovacuum launcher process
postgres 4527 2707 0 3569 1648 0 Jan05 ? 02:35:08
postgres: stats collector process

(showing a normally running system)