Thread: Bug? 'psql -l' in pg_ctl?
Er, did anybody notice trouble when starting Postgres if a password has
been set for user pgsql?
On my FreeBSD 5.0-20000528-CURRENT box, psql -l (from pg_ctl)
sucks down a big 28% cpu.
Here is the relevant section of the startup script:
start)
[ -d /usr/local/pgsql/lib ] && /sbin/ldconfig -m /usr/local/pgsql/lib
[ -x /usr/local/pgsql/bin/pg_ctl ] && {
su -l pgsql -c \
'exec /usr/local/pgsql/bin/pg_ctl -w start > /usr/local/pgsql/errlog
2>&1' &
echo -n ' pgsql'
}
;;
Postgres will start, but I still have that psql -l process cycling
waiting for a password...
errlog shows "DEBUG: Data Base System is in production state..."
Ah hah, I think I founnd the source of this and the file descriptor
problem (for those of you who are familiar with it):
in pg_ctl there is a loop to check if the postgres server is "accessible"
yet:
# wait for postmaster starting up
if [ "$wait" = 1 ];then
cnt=0
$ECHO_N "Waiting for postmaster starting up.."$ECHO_C
while :
do
if psql -l >/dev/null 2>&1
then
break;
else
$ECHO_N "."$ECHO_C
cnt=`expr $cnt + 1`
if [ $cnt -gt 60 ];then
$ECHO "$CMDNAME: postmaster does not start up"
exit 1
fi
sleep 1
fi
done
$ECHO "done."
fi
That damn loop has brought down two of my servers already.
It seems like a bloody bad idea to have such a thing in a startup script,
eh? Or am I maybe just not supposed to set a password for pgsql?
Either way, any suggestions?
gh
GH writes: > Either way, any suggestions? Personally, I don't trust pg_ctl farther than I can throw it. Consider not using it. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
On Sun, Nov 26, 2000 at 10:53:24PM +0100, some SMTP stream spewed forth: > GH writes: > > > Either way, any suggestions? > > Personally, I don't trust pg_ctl farther than I can throw it. Consider > not using it. Heh, I certainly have...but defaults that lock up servers? Erm, that's a Bad Thing(tm). gh > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ >
> Er, did anybody notice trouble when starting Postgres if a password has
> been set for user pgsql?
>
> On my FreeBSD 5.0-20000528-CURRENT box, psql -l (from pg_ctl)
> sucks down a big 28% cpu.
>
> Here is the relevant section of the startup script:
>
> start)
> [ -d /usr/local/pgsql/lib ] && /sbin/ldconfig -m /usr/local/pgsql/lib
> [ -x /usr/local/pgsql/bin/pg_ctl ] && {
> su -l pgsql -c \
> 'exec /usr/local/pgsql/bin/pg_ctl -w start > /usr/local/pgsql/errlog
> 2>&1' &
> echo -n ' pgsql'
> }
> ;;
>
> Postgres will start, but I still have that psql -l process cycling
> waiting for a password...
> errlog shows "DEBUG: Data Base System is in production state..."
>
> Ah hah, I think I founnd the source of this and the file descriptor
> problem (for those of you who are familiar with it):
psql tries to read a password from stdin that is not available at boot
time. In simple_prompt in common.c:
do
{
fgets(buf, 512, stdin);
} while (buf[strlen(buf) - 1] != '\n');
Here the return value of fgets() should be checked if fgtes() returns
EOF or not. Otherewise above goes into an infinite loop. Even this
gets fixed, psql would return a fail status and that would be not
suitable for current pg_ctl implementation.
Probably we really need here is a kind of ping tool for PostgreSQL,
instead of using psql.
You could directory invoke postmaster but problem is there is no
reliable way to detect if PostgreSQL up and running other than
trying to make an actual communication with backend...
--
Tatsuo Ishii
GH <grasshacker@over-yonder.net> writes:
> Ah hah, I think I founnd the source of this and the file descriptor
> problem (for those of you who are familiar with it):
This is a known bug in 7.0 psql --- it leaks backend connections when
looping on a Password: prompt. I thought we'd back-patched the fix
into 7.0.3, but a check of the CVS logs shows we didn't :-(.
pg_ctl is being pretty naive here in assuming that a parameterless
psql invocation will be able to connect, but the real misbehavior is
inside psql.
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
> Personally, I don't trust pg_ctl farther than I can throw it. Consider
> not using it.
That's a tad harsh, considering that the bug under discussion is
psql's not pg_ctl's ;-)
regards, tom lane
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Probably we really need here is a kind of ping tool for PostgreSQL,
> instead of using psql.
> You could directory invoke postmaster but problem is there is no
> reliable way to detect if PostgreSQL up and running other than
> trying to make an actual communication with backend...
I thought about watching for the postmaster.pid file to appear,
but that happens before the system is really up and running
--- the startup process isn't finished, and could still fail.
(Writing the pidfile later doesn't seem like a good answer to that,
since that'd weaken its main purpose of interlocking against
multiple postmaster startups.)
Trying to connect does seem to be the most reliable way to verify
that the postmaster is open for business.
(BTW, a short-term answer for grasshacker is not to use -w in his
pg_ctl start script ...)
regards, tom lane
On Sun, Nov 26, 2000 at 10:24:28PM -0500, some SMTP stream spewed forth: > Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > Probably we really need here is a kind of ping tool for PostgreSQL, *snip* > (BTW, a short-term answer for grasshacker is not to use -w in his > pg_ctl start script ...) Heh, I just went and commented out the whole if block. ;-) > > regards, tom lane
Tom Lane writes: > That's a tad harsh, considering that the bug under discussion is > psql's not pg_ctl's ;-) Well, mumble. Why does pg_ctl start the postmaster in the background without redirecting the input and output streams? Why does it say that the postmaster started successfully before it actually started? Why does the documentation say that -w waits for 60 seconds, when it's really only a count-to-60 loop? Is starting the postmaster and checking for the existence of the pid file in the next command really reliable? Why doesn't /etc/init.d/postgresql use it? As long as those questions remain, the only reliable way, in my mind, to start the postmaster is to start the postmaster. I would *like* to see pg_ctl become useful in terms of controlling log file names or piping the postmaster output to a rotatelog script, but as it stands it's just another layer of possible failure. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
> Tatsuo Ishii <t-ishii@sra.co.jp> writes: > > Probably we really need here is a kind of ping tool for PostgreSQL, > > instead of using psql. > > > You could directory invoke postmaster but problem is there is no > > reliable way to detect if PostgreSQL up and running other than > > trying to make an actual communication with backend... > > I thought about watching for the postmaster.pid file to appear, > but that happens before the system is really up and running > --- the startup process isn't finished, and could still fail. > (Writing the pidfile later doesn't seem like a good answer to that, > since that'd weaken its main purpose of interlocking against > multiple postmaster startups.) > > Trying to connect does seem to be the most reliable way to verify > that the postmaster is open for business. > > (BTW, a short-term answer for grasshacker is not to use -w in his > pg_ctl start script ...) Agreed. Do you think it's a good idea to invent a new command such as "pg_ping" or should we add a new option to psql instead? -- Tatsuo Ishii
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> Trying to connect does seem to be the most reliable way to verify
>> that the postmaster is open for business.
> Agreed.
> Do you think it's a good idea to invent a new command such as
> "pg_ping" or should we add a new option to psql instead?
I'd lean towards a pg_ping (Peter E., any comment here?)
Really we'd need to change the postmaster too, because what we need to
do is send a query "are you ready to accept connections?" that the
postmaster will answer without an authentication exchange. AFAIR this
is *not* immediately evident from the postmaster's current behavior ---
I think it will challenge you for a password even before the startup
subprocess is done.
Or we could invent a status file in $PGDATA that's separate from the
pid interlock file, and have pg_ctl look for that. But I think a
communication protocol might be cleaner.
regards, tom lane
Tom Lane writes: > I'd lean towards a pg_ping (Peter E., any comment here?) I could see this in the broad context of the nebulous, often asked for "admin tools", like querying the postmaster about what the backends are doing, etc. > Really we'd need to change the postmaster too, because what we need to > do is send a query "are you ready to accept connections?" that the > postmaster will answer without an authentication exchange. ISTM that the rejection of a client with authentication failure is a pretty good indicator that you're accepting connections. You only need to communicate it better. > Or we could invent a status file in $PGDATA that's separate from the > pid interlock file, and have pg_ctl look for that. I was thinking about increasing the link count on the pid file -- at least until yesterday... -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut <peter_e@gmx.net> writes:
>> Really we'd need to change the postmaster too, because what we need to
>> do is send a query "are you ready to accept connections?" that the
>> postmaster will answer without an authentication exchange.
> ISTM that the rejection of a client with authentication failure is a
> pretty good indicator that you're accepting connections.
Well, no, it means that the postmaster is alive. It doesn't mean the
database is open for business yet --- the startup subprocess might still
be running.
I've just tweaked the postmaster so that startup/shutdown state is
checked immediately upon receiving the startup-request packet, and if
there's a database-state reason for rejecting the connection, that
will happen before going through the authentication protocol. This
should make it easier to write a pg_ping.
regards, tom lane
> I'd lean towards a pg_ping (Peter E., any comment here?)
> Really we'd need to change the postmaster too, because what we need to
> do is send a query "are you ready to accept connections?" that the
> postmaster will answer without an authentication exchange. AFAIR this
> is *not* immediately evident from the postmaster's current behavior ---
> I think it will challenge you for a password even before the startup
> subprocess is done.
I fixed that today; if the database status is not open-for-business,
the postmaster will tell you so right away instead of making you go
through the authentication protocol first. So a pg_ping could be
written that just sends a connection request packet and sees what
comes back.
However, if we're running in TRUST or IDENT mode, it's possible that
that technique will lead to launching a backend to no purpose. So
maybe we ought to extend the postmaster protocol to have a "query
status" packet type. Thoughts?
regards, tom lane
* Tom Lane <tgl@sss.pgh.pa.us> [001129 20:22]: > > I'd lean towards a pg_ping (Peter E., any comment here?) > > > Really we'd need to change the postmaster too, because what we need to > > do is send a query "are you ready to accept connections?" that the > > postmaster will answer without an authentication exchange. AFAIR this > > is *not* immediately evident from the postmaster's current behavior --- > > I think it will challenge you for a password even before the startup > > subprocess is done. > > I fixed that today; if the database status is not open-for-business, > the postmaster will tell you so right away instead of making you go > through the authentication protocol first. So a pg_ping could be > written that just sends a connection request packet and sees what > comes back. > > However, if we're running in TRUST or IDENT mode, it's possible that > that technique will lead to launching a backend to no purpose. So > maybe we ought to extend the postmaster protocol to have a "query > status" packet type. Thoughts? I'd also like to see a protocol extension or some such to maybe collect SNMP or other statistical data that could be used later for tuning. If we do a protocol change, let's make it extensible.... LER > > regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
>I fixed that today; if the database status is not open-for-business, >the postmaster will tell you so right away instead of making you go >through the authentication protocol first. So a pg_ping could be >written that just sends a connection request packet and sees what >comes back. > >However, if we're running in TRUST or IDENT mode, it's possible that >that technique will lead to launching a backend to no purpose. So >maybe we ought to extend the postmaster protocol to have a "query >status" packet type. Thoughts? Would it be too much to just simply inclue an expect wrapper? You could even have it put in enough wrong auth answers so that the backend closes if you wanted. Rob Nelson rdnelson@co.centre.pa.us
Seems like this functionality belongs in pg_ctl. > > I'd lean towards a pg_ping (Peter E., any comment here?) > > > Really we'd need to change the postmaster too, because what we need to > > do is send a query "are you ready to accept connections?" that the > > postmaster will answer without an authentication exchange. AFAIR this > > is *not* immediately evident from the postmaster's current behavior --- > > I think it will challenge you for a password even before the startup > > subprocess is done. > > I fixed that today; if the database status is not open-for-business, > the postmaster will tell you so right away instead of making you go > through the authentication protocol first. So a pg_ping could be > written that just sends a connection request packet and sees what > comes back. > > However, if we're running in TRUST or IDENT mode, it's possible that > that technique will lead to launching a backend to no purpose. So > maybe we ought to extend the postmaster protocol to have a "query > status" packet type. Thoughts? > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026