Re: need a method to ping a running database

What about

down= \
`psql -d database -h server -c 'select 1;' 2>&1 | grep -c '^ERROR'`

?

Dima.

P.S. One thing to be cautious about is that if it has just been started,
you'll get something like 'ERROR: The database is starting up.', that
will get counted as an error (probably incorrectly)... So, the real
implementation has to be a little more elaborate then that to be able to
catch and handle that situation...

Travis Hume wrote:
> I need a shell scriptable method to determine if a postgresql database
> is running and accepting connections.
>
> I've written a script to start out postgresql database and I was using:
> "pg_ctl status"
>
> to determine if the database had been started. There are a couple of
> problems with this method. It seems that if you start postgresql and
> then issue a "kill -9" on the postmaster processes a .pid file is left
> behind (understandable), but then if you run "pg_ctl status" it will
> immediately report that the database is running and give you the pid
> number and whatever else. This is a bit misleading to say the least.
> To solve this problem I changed my db_ping() function to be more like
> "ps ax |grep -q postmaster && pg_ctl status". This works better, but
> there is still a problem.
>
> The .pid file is being written immediately, not when the database is
> actually up and accepting connections. This causes my java app to try
> and open the database and setup a connection pool before it should.
>
> I need a shell scriptable method to determine if the database is started
> and ready to accept connections.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org