Thread: please help--database just locks up.

please help--database just locks up.

From

"Mona Gamboa"

Date:

17 February 2003, 20:14:07

we are running linux and postgres7.3.2 in a production environment.

the database just 'locks ups.' if i try to get into psql X [where X is my database or template 0 or template 1] it just sits there.

the log files don't say anything......there's no indication as to what is happening.

if i look at top it says 100% idle, and the postmaster is supposedly still running, as i see it when i do a ps auuwx| grep ^postgres....

can anybody please help me figure out how to find out what's happening?

thank you so much,

Mona Gamboa

Re: please help--database just locks up.

From

"Mona Gamboa" (by way of Ed L.

Date:

17 February 2003, 21:49:05

ok...here's the output.[kinda nasty to read]

uname -a
Linux aus-db-1.loopone.com 2.4.9-e.3enterprise #1 SMP Fri May 3
 16:35:33 EDT 2002 i686 unknown

[postgres@aus-db-1 postgres]$ grep ^log_ $PGDATA/postgresql.conf
log_connections = true    #changed this whole block to true -- by
 Mona G. log_pid = true
log_statement = true
log_duration = true
log_timestamp = true
log_min_error_statement = error         # Values in order of
 increasing severity:

[postgres@aus-db-1 postgres]$ cat $PGDATA/postmaster.pid
2411
/home/db-pri/postgres/pgsql/DBTRANS
  5432001     98306
[postgres@aus-db-1 postgres]$ ps augxwww | egrep
 'postgres|postmaster|PID' USER       PID %CPU %MEM   VSZ  RSS TTY
   STAT START   TIME COMMAND postgres  2398  0.0  0.9 211248 14200 ?
     S    02:38   0:29 /usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2407  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2408  0.0  0.9 211248 14200 ?      S    02:38   0:01
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2409  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2410  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2411  0.0  0.1 24380 2256 ?        S    02:38   0:00
/home/db-pri/PostgreSQL/bin/postmaster
postgres  2412  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2413  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2414  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2415  0.0  0.9 211248 14200 ?      S    02:38   0:00
/usr/local/jdk1.4.1/bin/java
 -Dlog.base.name=/home/db-pri/postgres/logs/db-p ri_dbtrans
 -Dsend.email=false -Dsave.errors=true -Dsave.queries=false -Dmess
 age.threshold=300 com.loopone.logrotator.LogRotator
postgres  2416  0.0  0.2 25336 3216 ?        S    02:38   0:01
 postgres: stats buffer process
postgres  2417  0.0  0.1 24508 2352 ?        S    02:38   0:01
 postgres: stats collector process
postgres 11095  0.0  0.0  2508 1332 pts/1    S    13:46   0:00 -bash
postgres 13028  0.0  0.1  2856 1712 pts/1    S    15:35   0:00 ssh
 aus-db-1 postgres 13032  0.0  0.0  2516 1340 pts/4    S    15:35
 0:00 -bash postgres 13964  0.0  0.2 25144 4108 ?        S    16:24
 0:00 postgres: postgres dbtrans 10.1.1.130 idle
postgres 13969  0.0  0.2 25144 4104 ?        S    16:24   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 13972  0.0  0.2 25144 4104 ?        S    16:24   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 13974  0.0  0.2 25124 4092 ?        S    16:24   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 13984  0.0  0.2 25144 4112 ?        S    16:24   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 14069  0.0  0.2 25124 4092 ?        S    16:28   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 14070  0.0  0.2 25124 4092 ?        S    16:28   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 14072  0.0  0.2 25124 4092 ?        S    16:29   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 14073  0.0  0.2 25124 4092 ?        S    16:29   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 14077  0.0  0.2 25124 4092 ?        S    16:29   0:00
 postgres: postgres dbtrans 10.1.1.130 idle
postgres 14478  0.0  0.0  2520 1336 pts/3    S    17:08   0:00 -bash
postgres 14665  0.0  0.0  2508 1328 pts/2    S    17:23   0:00 -bash
postgres 14717  0.0  0.0  2844  912 pts/2    R    17:24   0:00 ps
 augxwww postgres 14718  0.0  0.0  1724  596 pts/2    S    17:24
 0:00 egrep postgres|postmaster|PID
[postgres@aus-db-1 postgres]$ psql -d template1 -c "select version()"
                           version
-------------------------------------------------------------
 PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

[postgres@aus-db-1 postgres]$ uptime
  5:25pm  up 14:52,  4 users,  load average: 0.02, 0.03, 0.00
[postgres@aus-db-1 postgres]$ df $PGDATA
Filesystem           1k-blocks      Used Available Use% Mounted on
/dev/sdb6             15124868   2930408  11426156  21%
/home/db-pri/postgres

thanks, Mona



----- Original Message -----
From: "Ed L." <pggeneral@bluepolka.net>
To: "Mona Gamboa" <mona@loopone.com>
Sent: Monday, February 17, 2003 4:00 PM
Subject: Re: [ADMIN] please help--database just locks up.


Can you post the output of these commands?

uname -a
export PGDATA=/path/to/your/data
grep ^log_ $PGDATA/postgresql.conf
cat $PGDATA/postmaster.pid
ps augxwww | egrep 'postgres|postmaster|PID'
psql -d template1 -c "select version()"
uptime
df $PGDATA

Regards,
Ed

On Monday February 17 2003 3:16, you wrote:
> we are running linux and postgres7.3.2 in a production environment.
>
> the database just 'locks ups.'  if i try to get into psql X [where
> X is my database or template 0 or template 1]  it just sits there.
> the log files don't  say anything......there's no indication as to
> what is happening.
>
> if i look at top it says 100% idle, and the postmaster is
> supposedly still running, as i see it when i do a ps auuwx| grep
> ^postgres....
>
> can anybody please help me figure out how to find out what's
> happening?
>
> thank you so much,
>
> Mona Gamboa

Re: please help--database just locks up.

From

"Ed L."

Date:

17 February 2003, 23:25:12

On Monday February 17 2003 4:31, Mona Gamboa wrote:
> ok...here's the output.[kinda nasty to read]

Nothing obvious to me.  If it is reproduceable, you might try turning
on some of the lock tracing directives in postgresql.conf and/or
increasing the logging level.  You might also try attaching to the
hung process in gdb to see where it is hanging, and post the
backtrace and see if anyone recognizes anything, e.g.

    gdb <PATH-TO-PSQL> <PID>
    gdb> bt

Ed

Re: please help--database just locks up.

From

Ed Loehr

Date:

18 February 2003, 15:23:50

Can you post the output of these commands?

uname -a
export PGDATA=/path/to/your/data
grep ^log_ $PGDATA/postgresql.conf
cat $PGDATA/postmaster.pid
ps augxwww | egrep 'postgres|postmaster|PID'
psql -d template1 -c "select version()"
uptime
df $PGDATA

Regards,
Ed


On Monday February 17 2003 3:16, Mona Gamboa wrote:
> we are running linux and postgres7.3.2 in a production environment.
>
> the database just 'locks ups.'  if i try to get into psql X [where
> X is my database or template 0 or template 1]  it just sits there.
> the log files don't  say anything......there's no indication as to
> what is happening.
>
> if i look at top it says 100% idle, and the postmaster is
> supposedly still running, as i see it when i do a ps auuwx| grep
> ^postgres....
>
> can anybody please help me figure out how to find out what's
> happening?
>
> thank you so much,
>
> Mona Gamboa

Re: please help--database just locks up.

From

"Ed L."

Date:

18 February 2003, 15:23:56

Is it reproduceable after a restart?  Can you dump the production
data, reload on a test cluster, and reproduce it with any
consistency?

Can you post the output of these commands?

uname -a
export PGDATA=/path/to/your/data
egrep '^log_|lock' $PGDATA/postgresql.conf
cat $PGDATA/postmaster.pid
ps augxwww | egrep 'postgres|postmaster|PID'
psql -d template1 -c "select version()"
uptime
df $PGDATA

Regards,
Ed

On Monday February 17 2003 3:16, Mona Gamboa wrote:
> we are running linux and postgres7.3.2 in a production environment.
>
> the database just 'locks ups.'  if i try to get into psql X [where
> X is my database or template 0 or template 1]  it just sits there.
> the log files don't  say anything......there's no indication as to
> what is happening.
>
> if i look at top it says 100% idle, and the postmaster is
> supposedly still running, as i see it when i do a ps auuwx| grep
> ^postgres....
>
> can anybody please help me figure out how to find out what's
> happening?
>
> thank you so much,
>
> Mona Gamboa