Thread: Shell script to determine if PostgreSQL is accepting connections?

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.

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