Re: 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