PQping command line tool

I was wondering recently if there was any command line tool that
utilized PQping() or PQpingParams(). I searched the code and couldn't
find anything and was wondering if there was any interest to have
something like this included? I wrote something for my purposes of
performing a health check that also supports nagios style status
output. It's probably convenient for scripting purposes as well. It's
not currently ready for submission to a commitfest, but if there was
an interest I would clean it up so that it would be.

On Tue, Oct 2, 2012 at 11:01:36PM -0400, Phil Sorber wrote:
> I was wondering recently if there was any command line tool that
> utilized PQping() or PQpingParams(). I searched the code and couldn't
> find anything and was wondering if there was any interest to have
> something like this included? I wrote something for my purposes of
> performing a health check that also supports nagios style status
> output. It's probably convenient for scripting purposes as well. It's
> not currently ready for submission to a commitfest, but if there was
> an interest I would clean it up so that it would be.

I don't see any tool using PQping except pg_ctl. Perhaps we should
modify "pg_ctl status" to use PQping. Right now is only checks the
postmaster.pid file, and checks to see that the pid is a running
postmaster. What it currently doesn't do is to check if the server is
accepting connections with PQping(), like we do for "pg_ctl -w start".

Comments?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Wed, Oct 3, 2012 at 11:35 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Tue, Oct 2, 2012 at 11:01:36PM -0400, Phil Sorber wrote:
>> I was wondering recently if there was any command line tool that
>> utilized PQping() or PQpingParams(). I searched the code and couldn't
>> find anything and was wondering if there was any interest to have
>> something like this included? I wrote something for my purposes of
>> performing a health check that also supports nagios style status
>> output. It's probably convenient for scripting purposes as well. It's
>> not currently ready for submission to a commitfest, but if there was
>> an interest I would clean it up so that it would be.
>
> I don't see any tool using PQping except pg_ctl. Perhaps we should
> modify "pg_ctl status" to use PQping. Right now is only checks the
> postmaster.pid file, and checks to see that the pid is a running
> postmaster. What it currently doesn't do is to check if the server is
> accepting connections with PQping(), like we do for "pg_ctl -w start".
>
> Comments?

I was thinking that maybe this should be a new feature in an existing
tool, however I don't think pg_ctl would satisfy my use case as it's
normally bundled with the server. This would need to be something that
I could install just a client package. It's not a deal breaker, but it
makes things more complex.

How about adding it as an option to psql? That's not to say that I
think we shouldn't also add it to 'pg_ctl status'.

>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> I don't see any tool using PQping except pg_ctl. Perhaps we should
> modify "pg_ctl status" to use PQping. Right now is only checks the
> postmaster.pid file, and checks to see that the pid is a running
> postmaster. What it currently doesn't do is to check if the server is
> accepting connections with PQping(), like we do for "pg_ctl -w start".

The thing about pg_ctl is that it requires access to the data directory
(and still would, in the variant you propose). If we were going to do
something like what Phil suggests then I think it ought to be something
usable remotely.

regards, tom lane

2012/10/3 Phil Sorber <phil(at)omniti(dot)com>:
> On Wed, Oct 3, 2012 at 11:35 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> On Tue, Oct 2, 2012 at 11:01:36PM -0400, Phil Sorber wrote:
>>> I was wondering recently if there was any command line tool that
>>> utilized PQping() or PQpingParams(). I searched the code and couldn't
>>> find anything and was wondering if there was any interest to have
>>> something like this included? I wrote something for my purposes of
>>> performing a health check that also supports nagios style status
>>> output. It's probably convenient for scripting purposes as well. It's
>>> not currently ready for submission to a commitfest, but if there was
>>> an interest I would clean it up so that it would be.
>>
>> I don't see any tool using PQping except pg_ctl. Perhaps we should
>> modify "pg_ctl status" to use PQping. Right now is only checks the
>> postmaster.pid file, and checks to see that the pid is a running
>> postmaster. What it currently doesn't do is to check if the server is
>> accepting connections with PQping(), like we do for "pg_ctl -w start".
>>
>> Comments?
>
> I was thinking that maybe this should be a new feature in an existing
> tool, however I don't think pg_ctl would satisfy my use case as it's
> normally bundled with the server. This would need to be something that
> I could install just a client package. It's not a deal breaker, but it
> makes things more complex.
>
> How about adding it as an option to psql? That's not to say that I
> think we shouldn't also add it to 'pg_ctl status'.

+1

Pavel
>
>>
>> --
>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>> EnterpriseDB http://enterprisedb.com
>>
>> + It's impossible for everything to be true. +
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Oct 3, 2012 at 11:42 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2012/10/3 Phil Sorber <phil(at)omniti(dot)com>:
>> On Wed, Oct 3, 2012 at 11:35 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>> On Tue, Oct 2, 2012 at 11:01:36PM -0400, Phil Sorber wrote:
>>>> I was wondering recently if there was any command line tool that
>>>> utilized PQping() or PQpingParams(). I searched the code and couldn't
>>>> find anything and was wondering if there was any interest to have
>>>> something like this included? I wrote something for my purposes of
>>>> performing a health check that also supports nagios style status
>>>> output. It's probably convenient for scripting purposes as well. It's
>>>> not currently ready for submission to a commitfest, but if there was
>>>> an interest I would clean it up so that it would be.
>>>
>>> I don't see any tool using PQping except pg_ctl. Perhaps we should
>>> modify "pg_ctl status" to use PQping. Right now is only checks the
>>> postmaster.pid file, and checks to see that the pid is a running
>>> postmaster. What it currently doesn't do is to check if the server is
>>> accepting connections with PQping(), like we do for "pg_ctl -w start".
>>>
>>> Comments?
>>
>> I was thinking that maybe this should be a new feature in an existing
>> tool, however I don't think pg_ctl would satisfy my use case as it's
>> normally bundled with the server. This would need to be something that
>> I could install just a client package. It's not a deal breaker, but it
>> makes things more complex.
>>
>> How about adding it as an option to psql? That's not to say that I
>> think we shouldn't also add it to 'pg_ctl status'.

I was looking at the code and originally I was using return code to
signify what the status was and some text output when quiet wasn't
set, but psql has it's own set of established return codes. How does
everyone feel about using different return codes when psql is in the
PQping mode?

Also was just printing out terse text forms of the enums. OK,
NO_RESPONSE, etc. I was thinking they could be used in shell scripts
that way, but we could do that with return codes as well. Would people
like to see something more human friendly and descriptive?

Also -C, --check was available and I went with that. Most of the other
stuff I could think of already had the short option taken.

>
> +1
>
> Pavel
>>
>>>
>>> --
>>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>>> EnterpriseDB http://enterprisedb.com
>>>
>>> + It's impossible for everything to be true. +
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers

Phil Sorber <phil(at)omniti(dot)com> writes:
> How about adding it as an option to psql? That's not to say that I
> think we shouldn't also add it to 'pg_ctl status'.

> I was looking at the code and originally I was using return code to
> signify what the status was and some text output when quiet wasn't
> set, but psql has it's own set of established return codes. How does
> everyone feel about using different return codes when psql is in the
> PQping mode?

Personally, I think adding this to psql has nothing to recommend it:
it would be shoehorning an unrelated behavior in among what are already
too many constraints.

If we're going to do it at all, it should be a stand-alone tool.
If it's not worth that much work, it's not worth doing.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> I was wondering recently if there was any command line tool that
> utilized PQping() or PQpingParams(). I searched the code and couldn't
> find anything and was wondering if there was any interest to have
> something like this included? I wrote something for my purposes of
> performing a health check that also supports nagios style status
> output. It's probably convenient for scripting purposes as well.

I'm not sure how useful this information would be. Most health
checks (Nagios or otherwise) really only care if things are
working all the up to point A or not, where point A is usually
a simple query such as "SELECT 1". Knowing various failure states
as returned by PQping* does not seem to fit into such tools -
any failure needs to be handled manually.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201210041146
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlBtukQACgkQvJuQZxSWSsiCbACePHFhTefoQnLwVuvIONH0JcSD
jq8AoIPusD88fX1rBcse5IreaADH7wkZ
=IRgc
-----END PGP SIGNATURE-----

On 10/4/12 11:34 AM, Greg Sabino Mullane wrote:
>> I was wondering recently if there was any command line tool that
>> >utilized PQping() or PQpingParams(). I searched the code and couldn't
>> >find anything and was wondering if there was any interest to have
>> >something like this included? I wrote something for my purposes of
>> >performing a health check that also supports nagios style status
>> >output. It's probably convenient for scripting purposes as well.
> I'm not sure how useful this information would be. Most health
> checks (Nagios or otherwise) really only care if things are
> working all the up to point A or not, where point A is usually
> a simple query such as "SELECT 1". Knowing various failure states
> as returned by PQping* does not seem to fit into such tools -
> any failure needs to be handled manually.

For whatever reason our Nagios setup telnets to the port to see if it's open, which means we get a ton of messages in the log about authentication errors or some such. It'd be useful to us to have a utility that could cleanly validate the server was up and communicating, without having to actually login.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Jim Nasby pointed out:

> It'd be useful to us to have a utility that could cleanly validate
> the server was up and communicating, without having to actually login.

Well sure, but wouldn't it be even more useful to validate at the
same time that logins are working? :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201210101310
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlB1rDsACgkQvJuQZxSWSsibngCg61f1ldN2wZOT4FHOFvbiIfO6
3EIAoJyFfd4T2NXv/jH5zGD9pSypykXi
=WP+s
-----END PGP SIGNATURE-----

On Wed, Oct 10, 2012 at 6:11 PM, Greg Sabino Mullane <greg(at)turnstep(dot)com> wrote:
> Jim Nasby pointed out:
>
>> It'd be useful to us to have a utility that could cleanly validate
>> the server was up and communicating, without having to actually login.
>
> Well sure, but wouldn't it be even more useful to validate at the
> same time that logins are working? :)

Not necessarily. To test if logins are working your tool needs access
to the credentials of a user you want to test. That makes the tool
less useful to someone who wants to run it on a machine or from a
process that shouldn't have access to those credentials.

Of course it's not a problem to have it as an option. But I'm just
agreeing with Jim that it can be useful to have a tool that is capable
of doing less.

Fwiw the current architecture of the postmaster is designed to *not*
start up a backend until after the the initial exchange is done. That
protects against denial of service attacks that open lots of TCP
connections without having any credentials. But it means that just
making a TCP connection is verifying less of the system.

--
greg