Message-ID: <1226986724.492254e42cda7@legacywebmail.telus.net>
Date: Mon, 17 Nov 2008 21:38:44 -0800
From: Mischa Sandberg <mischa_sandberg@telus.net>
To: pgsql-admin@postgresql.org
Subject: Re: Reliably determining whether the server came up
References: <20081116212002.7DFE91E81690@mx2.hub.org>
In-Reply-To: <20081116212002.7DFE91E81690@mx2.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
User-Agent: Internet Messaging Program (IMP) 3.1-cvs

Comment below:

Joshua D. Drake wrote:
> On Sat, 2008-11-15 at 12:29 -0800, Mischa Sandberg wrote:
>> Quoting "H. Hall" <hhall1001@reedyriver.com>:
>>> Mischa Sandberg wrote:
>>>       
>>>> I've been trying to work out a reliable script to determine, 
>>>> after pg_ctl start, that the server is done attempting
>>>> to come up, and that it has either succeeded OR FAILED.
>>>> This is for several hundred unattended appliance-type servers,
>>>> currently on PG 8.0 but soon to be on 8.3
>>>>         
>>> Why don't you try to create a connection to a db on each server?
>> Thanks, but that only tells me if the server is up at the time of trying
>> to connect.
>
> Actually it doesn't. If you are using any standard library to connect if
> the server is not ready to accept connections, it will tell you when you
> connect. If the server failed to come up, you won't get a connection at
> all, if you try to connect and you are able to connect but not initiate
> a session and appropriate response will be sent.
> Joshua D. Drake
Exactly. :-)
Also, once you take a look at your solution code a light bulb may go 
off. Hey! This code could also be used to test the health of my db 
servers during production!  If I just execute it in a timer thread . . .
Hmmm.
--cheers, HH
-- 
H. Hall
ReedyRiver Group LLC
www.reedyriver.com

Well, I'll look further at it. I originally did start with

   while pg_ctl status && ! psql -l; do nothing; done

The cases I've had to catch include:

- startup so slow that postmaster.pid has still not been created when
the first pg_ctl status exits, returning 'no server'.

- a pg_xlog drive going sour (some low-end hardware is, well, crap),
  so pg_ctl status says server is up but connects get
  'FATAL:  the database system is shutting down' forever.

... and I'm guessing that other server failure states will produce
other messages (with FATAL not always meaning real fatality).
-- 
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.