Thread: Lightest way of checking if postgresql is running at the other end of an ssh tunnel?

Hi,

We have an ssh connection running from one server to our postgresql database on another server. Some times we
experiencethat the ssh tunnel does not work anymore and needs to be restarted, even though we use the autossh package.
Iwould like to write a script that “pings” postgresql on the specified port, to check if the connection goes through. I
havetried with netcat, but it does not really check if postgresql is in the other end of the tunnel, it only check if
thereis as service (the tunnel) listing on the port on the local machine. Is there another way of pinging the port, to
seeif postgresql is alive at the other end? If possible, I would like to NOT actually establishing a connection to
postgresqllike if i used psql -c “select 1;”, to avoid connection overhead. 

Any ideas?

On Wed, May 11, 2016 at 11:17:54AM +0200, Niels Kristian Schjødt wrote:

> We have an ssh connection running from one server to our
> postgresql database on another server. Some times we
> experience that the ssh tunnel does not work anymore and
> needs to be restarted, even though we use the autossh
> package. I would like to write a script that “pings”
> postgresql on the specified port, to check if the connection
> goes through. I have tried with netcat, but it does not
> really check if postgresql is in the other end of the tunnel,
> it only check if there is as service (the tunnel) listing on
> the port on the local machine. Is there another way of
> pinging the port, to see if postgresql is alive at the other
> end? If possible, I would like to NOT actually establishing a
> connection to postgresql like if i used psql -c “select 1;”,
> to avoid connection overhead.

This

    http://www.postgresql.org/docs/devel/static/libpq-connect.html

talks about ping functionality. Maybe you can use a tiny
custom piece of code ?

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

On 05/11/2016 11:41 AM, Karsten Hilbert wrote:
> On Wed, May 11, 2016 at 11:17:54AM +0200, Niels Kristian Schjødt wrote:
>
>> We have an ssh connection running from one server to our
>> postgresql database on another server. Some times we
>> experience that the ssh tunnel does not work anymore and
>> needs to be restarted, even though we use the autossh
>> package. I would like to write a script that “pings”
>> postgresql on the specified port, to check if the connection
>> goes through. I have tried with netcat, but it does not
>> really check if postgresql is in the other end of the tunnel,
>> it only check if there is as service (the tunnel) listing on
>> the port on the local machine. Is there another way of
>> pinging the port, to see if postgresql is alive at the other
>> end? If possible, I would like to NOT actually establishing a
>> connection to postgresql like if i used psql -c “select 1;”,
>> to avoid connection overhead.
>
> This
>
>     http://www.postgresql.org/docs/devel/static/libpq-connect.html
>
> talks about ping functionality. Maybe you can use a tiny
> custom piece of code ?

That tiny custom piece of code would be this:

http://www.postgresql.org/docs/current/static/app-pg-isready.html

--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

On Wed, May 11, 2016 at 02:28:47PM +0200, Vik Fearing wrote:

> >> We have an ssh connection running from one server to our
> >> postgresql database on another server. Some times we
> >> experience that the ssh tunnel does not work anymore and
> >> needs to be restarted, even though we use the autossh
> >> package. I would like to write a script that “pings”
> >> postgresql on the specified port, to check if the connection
> >> goes through. I have tried with netcat, but it does not
> >> really check if postgresql is in the other end of the tunnel,
> >> it only check if there is as service (the tunnel) listing on
> >> the port on the local machine. Is there another way of
> >> pinging the port, to see if postgresql is alive at the other
> >> end? If possible, I would like to NOT actually establishing a
> >> connection to postgresql like if i used psql -c “select 1;”,
> >> to avoid connection overhead.
> >
> > This
> >
> >     http://www.postgresql.org/docs/devel/static/libpq-connect.html
> >
> > talks about ping functionality. Maybe you can use a tiny
> > custom piece of code ?
>
> That tiny custom piece of code would be this:
>
> http://www.postgresql.org/docs/current/static/app-pg-isready.html

That's what I had in mind :-)

Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346