Shell script to determine if PostgreSQL is accepting connections?

On 10:44 Sun 18 Mar , Jeff Ross wrote:

Hi,

Is there a simple way to determine with a shell script if PostgreSQL is
ready to accept connections?

PostgreSQL is started and controlled by daemontools. This shell script
is called by the /etc/netstart script as a part of bringing up the
first interface. Unfortunately, it will never exit the loop.

#!/bin/sh
/bin/csh -cf '/command/svscanboot &'
until [ -S /tmp/.s.PGSQL.5432 ] ; do
/bin/echo "Waiting for PostgreSQL to start..."
/bin/sleep 1
done

Because this is happening at startup before I get a login prompt I'm not
sure I know how to figure out why it never exits the loop.

The purpose of this is that I'm running pglogd, a daemon that uses a
custom log format and a fifo to stuff Apache web logs into a table. The
pglogd daemon must start after PostgreSQL and before Apache. I can do
this in /etc/rc.local, but OpenBSD's /etc/rc wants to start Apache
before rc.local is parsed. Once I know that PostgreSQL is up and
accepting connections I can then start pglogd and then it's ready to go
when Apache starts.

As a side note, the original creator and maintainer of pglogd has EOLed
the project but it works well for me under 8.2.3. I intend to fix a few
of its problems (it does fine until the backend goes away ;-) and
re-release it.

Thanks,

Jeff Ross

P.S. Apologies to the owner of the list--I inadvertently sent this to
the wrong address first.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

variants on the following might be useful...

$ psql -U postgres -t -c "select pg_postmaster_start_time()" postgres`
2007-03-18 16:51:55.239342-04

so ...

start loop
response=`psql -U postgres -t -c "select pg_postmaster_start_time()" postgres`
or
response=`psql -U postgres -t -c "select now()" postgres`

if [ $? == 0 ]; then
parse/evaluate response to make determination of whether db is up
( or, base decision on evaluation of $? )
break loop if db is up or other criteria are met ( taking too
long, etc )
else
query failed, backend not ready, continue loop
fi
end loop