psql hangs when trying to access a database - Mailing list pgsql-general

Running into an interesting problem when trying to do a psql from the
commandline.

My infrastructure:
Intel Architecture
Redhat 7.1
PostgreSQL 7.0.3 (from stock RPM's of the 7.1 distribution)
320MB RAM

Kernel shared memory increases:
# Up the maximum shared memory values
kernel.shmall = 207618048
kernel.shmmax = 207618048

pg_ctl startup in /etc/rc.d/init.d/postgresql:
su -l postgres -c "/usr/bin/pg_ctl  -D $PGDATA -p /usr/bin/postmaster -o
'-d 5 -i -B 24576' start >/tmp/postgres.log 2>&1" < /dev/null

Database Environment
5 databases on this one server.  The one in question has about 5 tables
with 60,000 rows per table average.  The entire directory under $PGDATA
is only about 160MB


At the command line I type 'psql datawarehouse'.  I expect to see the
banner from the psql command but in this case, nothing.  The system
seems to just hang.  Running a top shows that my process is taking up
99% of the User CPU but according to iostat, there is no activity on the
harddrive.

Here's what the 'ps -awx|grep post' command shows me:
 3073 pts/1    S      0:00 /bin/sh /usr/bin/pg_ctl -D /db/pgsql -p
/usr/bin/postmaster -o -d 5 -i -B 24576 start
 3075 pts/1    S      0:00 /usr/bin/postmaster -d 5 -i -B 24576
 3085 pts/1    R      0:11 /usr/bin/postgres localhost mnickel
datawarehouse startup

generally I would expect the /usr/bin/postgres line to eventually change
to 'idle' instead of startup...

Also, pg_dump and vacuumdb do not work.  The postmaster happily spawns
off another /usr/bin/postgres, but the state never switches out of
'idle' for pg_dump or vacuumdb.

Upon inspection of the /tmp/postgres.log file, (included at the end),  I
see that the processing is hanging right after the 'InitPostgres'.
Normally there is a bunch of other "stuff" that is there when normal
entry into the psql command completes...

I have no problem using psql on any of the other 5 databases on this
server.  Everything runs smoothly.  This is the only database that is
giving us grief.  This is the first problem that we've come across in
almost a year of PostgreSQL utilization.  I'm totally baffled... :)

Thanks so much for looking at this, as I'm getting the "see, I told you
that open source/freeware stuff wasn't any good" pressure.  :)

Mark Nickel

Here is the logfile in question:

postmaster successfully started up.
FindExec: found "/usr/bin/postgres" using argv[0]
binding ShmemCreate(key=52e2c1, size=205273088)
011105.20:22:27.451  [3076] DEBUG:  Data Base System is starting up at
Mon Nov  5 20:22:27 2001
011105.20:22:27.451  [3076] DEBUG:  Data Base System was shut down at
Mon Nov  5 20:22:13 2001
011105.20:22:27.453  [3076] DEBUG:  Data Base System is in production
state at Mon Nov  5 20:22:27 2001
011105.20:22:27.453  [3076] proc_exit(0)
011105.20:22:27.454  [3076] shmem_exit(0)
011105.20:22:27.454  [3076] exit(0)
/usr/bin/postmaster: reaping dead processes...
/usr/bin/postmaster: ServerLoop:  handling reading 5
/usr/bin/postmaster: ServerLoop:  handling reading 5
/usr/bin/postmaster: ServerLoop:  handling writing 5
/usr/bin/postmaster: BackendStartup: environ dump:
-----------------------------------------
 PWD=/var/lib/pgsql
 HOSTNAME=topaz.dunsirn.com
 ODBCINI=/etc/odbc.ini
 PVM_RSH=/usr/bin/rsh
 QTDIR=/usr/lib/qt-2.3.0
 LESSOPEN=|/usr/bin/lesspipe.sh %s
 XPVM_ROOT=/usr/share/pvm3/xpvm
 KDEDIR=/usr
 USER=postgres

LS_COLORS=no=00:fi=00:di=01;34:ln=01;36:pi=40;33:so=01;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=01;32:*.cmd=01;32:*.exe=01;32:*.com=01;32:*.btm=01;32:*.bat=01;32:*.sh=01;32:*.csh=01;32:*.tar=01;31:*.tgz=01;31:*.arj=01;31:*.taz=01;31:*.lzh=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.gz=01;31:*.bz2=01;31:*.bz=01;31:*.tz=01;31:*.rpm=01;31:*.cpio=01;31:*.jpg=01;35:*.gif=01;35:*.bmp=01;35:*.xbm=01;35:*.xpm=01;35:*.png=01;35:*.tif=01;35:

 MACHTYPE=i386-redhat-linux-gnu
 MAIL=/var/spool/mail/postgres
 INPUTRC=/etc/inputrc
 LANG=en_US
 LOGNAME=postgres
 SHLVL=2
 MQSERVER=OPAL.AMBER/TCP/opal(1414)
 SHELL=/bin/bash
 PGLIB=/usr/lib/pgsql
 HOSTTYPE=i386
 OSTYPE=linux-gnu
 HISTSIZE=1000
 LAMHELPFILE=/etc/lam/lam-helpfile
 PVM_ROOT=/usr/share/pvm3
 TERM=xterm
 HOME=/var/lib/pgsql
 SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
 PGDATA=/db/pgsql
 PATH=/usr/kerberos/bin:/bin:/usr/bin:/usr/local/bin:/usr/bin/X11:/usr/X11R6/bin

 _=/usr/bin/postmaster
 POSTPORT=5432
 POSTID=2147483646
 IPC_KEY=5432000
 PG_USER=mnickel
-----------------------------------------
/usr/bin/postmaster: BackendStartup: pid 3085 user mnickel db
datawarehouse socket 5
/usr/bin/postmaster child[3085]: starting with (/usr/bin/postgres -d5 -B
24576 -v131072 -p datawarehouse )
FindExec: found "/usr/bin/postgres" using argv[0]
011105.20:22:46.186  [3085] started: host=localhost user=mnickel
database=datawarehouse
011105.20:22:46.187  [3085] InitPostgres
011105.20:27:45.645  [3075] pmdie 15
Smart Shutdown request at Mon Nov  5 20:27:45 2001
/usr/bin/pg_ctl: line 1:  3075 Killed
/usr/bin/postmaster -d 5 -i -B 24576