--001485e8cb6ff269bc0485744859
Content-Type: text/plain; charset=UTF-8

Hi,

I have a number of PostgreSQL servers which I often access through ssh
tunnel with Pgadmin3. I would like to double check which one I have landed
on (if the tunnel is really configured the way I want). Is there a way to
query the hostname from the catalogs?

Thanks
Peter

--001485e8cb6ff269bc0485744859
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<meta http-equiv=3D"content-type" content=3D"text/html; charset=3Dutf-8"><s=
pan class=3D"Apple-style-span" style=3D"font-family: arial, sans-serif; fon=
t-size: 14px; border-collapse: collapse; ">Hi,<div><br></div><div>I have a =
number of PostgreSQL servers which I often access through ssh tunnel with P=
gadmin3. I would like to double check which one I have landed on (if the tu=
nnel is really configured the way I want). Is there a way to query the host=
name from the catalogs?</div>
<div><br></div><div>Thanks</div><div>Peter</div></span>

--001485e8cb6ff269bc0485744859--

On Fri, Apr 30, 2010 at 03:33:13PM +0200, P=E9ter Kov=E1cs wrote:
> Hi,
>
> I have a number of PostgreSQL servers which I often access through ssh
> tunnel with Pgadmin3. I would like to double check which one I have lan=
ded
> on (if the tunnel is really configured the way I want). Is there a way =
to
> query the hostname from the catalogs?

Hmm, that's a bit tricky, since I assume you're using a local db
connection inside the tunnel, so inet_server_addr() probably returns
null. If you're talking unix/linux machines, then /etc/hostname _should_
have the current hostname in it, so:

create temp table foo (t text);
copy foo from '/etc/hostname';
select * from foo;
drop table foo;

Should work.

Ross
--
Ross Reedstrom, Ph.D. reedstrm [at] rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint =3D F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BED=
E

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

"Ross J. Reedstrom" <reedstrm [at] rice.edu> wrote:

> If you're talking unix/linux machines, then /etc/hostname _should_
> have the current hostname in it

If not, check for /etc/HOSTNAME -- some distributions do that.

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--00148531a52b02479204857521c8
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Thank you, Ross!

inet_server_addr() returns the correct IP address in this case. I am not
sure why... The tunnel goes through at least one port-forwarding node, but =
I
am not sure this makes postgresql see the connection any less local.

Thanks
Peter

On Fri, Apr 30, 2010 at 4:14 PM, Ross J. Reedstrom <reedstrm [at] rice.edu>wrote=
:

> On Fri, Apr 30, 2010 at 03:33:13PM +0200, P=C3=A9ter Kov=C3=A1cs wrote:
> > Hi,
> >
> > I have a number of PostgreSQL servers which I often access through ssh
> > tunnel with Pgadmin3. I would like to double check which one I have
> landed
> > on (if the tunnel is really configured the way I want). Is there a way =
to
> > query the hostname from the catalogs?
>
> Hmm, that's a bit tricky, since I assume you're using a local db
> connection inside the tunnel, so inet_server_addr() probably returns
> null. If you're talking unix/linux machines, then /etc/hostname _should_
> have the current hostname in it, so:
>
> create temp table foo (t text);
> copy foo from '/etc/hostname';
> select * from foo;
> drop table foo;
>
> Should work.
>
> Ross
> --
> Ross Reedstrom, Ph.D. reedstrm [at] rice.edu
> Systems Engineer & Admin, Research Scientist phone: 713-348-6166
> The Connexions Project http://cnx.org fax: 713-348-3665
> Rice University MS-375, Houston, TX 77005
> GPG Key fingerprint =3D F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BED=
E
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--00148531a52b02479204857521c8
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<meta http-equiv=3D"content-type" content=3D"text/html; charset=3Dutf-8">Th=
ank you, Ross!<div><br></div><div><span class=3D"Apple-style-span" style=3D=
"font-family: arial, sans-serif; font-size: 14px; border-collapse: collapse=
; ">inet_server_addr() returns the correct IP address in this case. I am no=
t sure why... The tunnel goes through at least one port-forwarding node, bu=
t I am not sure this makes postgresql see the connection any less local.</s=
pan></div>
<div><span class=3D"Apple-style-span" style=3D"font-family: arial, sans-ser=
if; font-size: 14px; border-collapse: collapse; "><br></span></div><div><sp=
an class=3D"Apple-style-span" style=3D"font-family: arial, sans-serif; bord=
er-collapse: collapse; ">Thanks</span></div>
<div><font class=3D"Apple-style-span" face=3D"arial, sans-serif"><span clas=
s=3D"Apple-style-span" style=3D"border-collapse: collapse; ">Peter<br></spa=
n></font></div><br><div class=3D"gmail_quote">On Fri, Apr 30, 2010 at 4:14 =
PM, Ross J. Reedstrom <span dir=3D"ltr"><<a href=3D"mailto:reedstrm [at] rice=
..edu">reedstrm [at] rice.edu</a>></span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;">On Fri, Apr 30, 2010 at 03:33:13PM +0200, P=
=C3=A9ter Kov=C3=A1cs wrote:<br>
> Hi,<br>
><br>
> I have a number of PostgreSQL servers which I often access through ssh=
<br>
> tunnel with Pgadmin3. I would like to double check which one I have la=
nded<br>
> on (if the tunnel is really configured the way I want). Is there a way=
to<br>
> query the hostname from the catalogs?<br>
<br>
Hmm, that's a bit tricky, since I assume you're using a local db<br=
>
connection inside the tunnel, so inet_server_addr() probably returns<br>
null. If you're talking unix/linux machines, then /etc/hostname _should=
_<br>
have the current hostname in it, so:<br>
<br>
create temp table foo (t text);<br>
copy foo from '/etc/hostname';<br>
select * from foo;<br>
drop table foo;<br>
<br>
Should work.<br>
<br>
Ross<br>
--<br>
Ross Reedstrom, Ph.D. =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <a href=3D"mail=
to:reedstrm [at] rice.edu">reedstrm [at] rice.edu</a><br>
Systems Engineer & Admin, Research Scientist =C2=A0 =C2=A0 =C2=A0 =C2=
=A0phone: 713-348-6166<br>
The Connexions Project =C2=A0 =C2=A0 =C2=A0<a href=3D"http://cnx.org" targe=
t=3D"_blank">http://cnx.org</a> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0fa=
x: 713-348-3665<br>
Rice University MS-375, Houston, TX 77005<br>
GPG Key fingerprint =3D F023 82C8 9B0E 2CC6 0D8E =C2=A0F888 D3AE 810E 88F0 =
BEDE<br>
<font color=3D"#888888"><br>
--<br>
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin [at] postgresql=
..org">pgsql-admin [at] postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a><br>
</font></blockquote></div><br>

--00148531a52b02479204857521c8--

--- On Fri, 30/4/10, Ross J. Reedstrom <reedstrm [at] rice.edu> wrote:

> > Hi,
> >
> > I have a number of PostgreSQL servers which I often
> access through ssh
> > tunnel with Pgadmin3. I would like to double check
> which one I have landed
> > on (if the tunnel is really configured the way I
> want). Is there a way to
> > query the hostname from the catalogs?
>
> Hmm, that's a bit tricky, since I assume you're using a
> local db
> connection inside the tunnel, so inet_server_addr()
> probably returns
> null. If you're talking unix/linux machines, then
> /etc/hostname _should_
> have the current hostname in it, so:
>
> create temp table foo (t text);
> copy foo from '/etc/hostname';
> select * from foo;
> drop table foo;
>
> Should work.
>

Or you could do something like:

CREATE OR REPLACE FUNCTION hostname()
RETURNS text AS
$BODY$
$host =3D `hostname`;
return $host;
$BODY$
LANGUAGE 'plperlu';






--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Yes, nice and simple.

I just did this in C and it works ok!

hostname.c
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

#include <unistd.h>
#include "postgres.h"
#include "utils/elog.h"
#include "utils/palloc.h"
#include "storage/bufpage.h"
#define MAX_HOST_SIZE 200

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(hostname);
Datum hostname(PG_FUNCTION_ARGS);

Datum
hostname(PG_FUNCTION_ARGS)
{
int len;
char buf[MAX_HOST_SIZE + 1];
text *result;

gethostname(buf,MAX_HOST_SIZE);
len =3D strlen(buf);
//elog(NOTICE, "hostname=3D%s\n",buf);
result=3D(text *)palloc(len + VARHDRSZ);
SET_VARSIZE(result, len + VARHDRSZ);
memcpy(VARDATA(result),buf,strlen(buf));
PG_RETURN_POINTER(result);
}


Makefile
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
MODULE_big =3D hostname
OBJS =3D hostname.o

ifdef USE_PGXS
PG_CONFIG =3D pg_config
PGXS :=3D $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir =3D /var/lib/pgsql/src/Ccode/hostname
top_builddir =3D /usr/local/src/postgresql-8.3.3
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif


hostname.sql
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D
CREATE OR REPLACE FUNCTION hostname() RETURNS text
AS '$libdir/hostname', 'hostname'
LANGUAGE c IMMUTABLE STRICT;


=CE=A3=CF=84=CE=B9=CF=82 Friday 30 April 2010 17:43:49 =CE=BF/=CE=B7 Glyn A=
still =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> --- On Fri, 30/4/10, Ross J. Reedstrom <reedstrm [at] rice.edu> wrote:
>
> > > Hi,
> > >
> > > I have a number of PostgreSQL servers which I often
> > access through ssh
> > > tunnel with Pgadmin3. I would like to double check
> > which one I have landed
> > > on (if the tunnel is really configured the way I
> > want). Is there a way to
> > > query the hostname from the catalogs?
> >
> > Hmm, that's a bit tricky, since I assume you're using a
> > local db
> > connection inside the tunnel, so inet_server_addr()
> > probably returns
> > null. If you're talking unix/linux machines, then
> > /etc/hostname _should_
> > have the current hostname in it, so:
> >
> > create temp table foo (t text);
> > copy foo from '/etc/hostname';
> > select * from foo;
> > drop table foo;
> >
> > Should work.
> >
>
> Or you could do something like:
>
> CREATE OR REPLACE FUNCTION hostname()
> RETURNS text AS
> $BODY$
> $host =3D `hostname`;
> return $host;
> $BODY$
> LANGUAGE 'plperlu';
>
>
>
>
>
>



--
Achilleas Mantzios

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--_1c9c11df-2871-4f4d-92f0-964df596e3b1_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


I think you can do something like that as a postgresql user

su - postgres

psql -c "\! uname -n"

or actually you can do that inside of any sql statement

psql
\!uname -n




> Date: Fri=2C 30 Apr 2010 09:14:04 -0500
> From: reedstrm [at] rice.edu
> To: pgsql-admin [at] postgresql.org
> Subject: Re: [ADMIN] Querying the hostname of the server
>
> On Fri=2C Apr 30=2C 2010 at 03:33:13PM +0200=2C P=E9ter Kov=E1cs wrote:
> > Hi=2C
> >
> > I have a number of PostgreSQL servers which I often access through ssh
> > tunnel with Pgadmin3. I would like to double check which one I have lan=
ded
> > on (if the tunnel is really configured the way I want). Is there a way =
to
> > query the hostname from the catalogs?
>
> Hmm=2C that's a bit tricky=2C since I assume you're using a local db
> connection inside the tunnel=2C so inet_server_addr() probably returns
> null. If you're talking unix/linux machines=2C then /etc/hostname _should=
_
> have the current hostname in it=2C so:
>
> create temp table foo (t text)=3B
> copy foo from '/etc/hostname'=3B
> select * from foo=3B
> drop table foo=3B
>
> Should work.
>
> Ross
> --
> Ross Reedstrom=2C Ph.D. reedstrm [at] rice.edu
> Systems Engineer & Admin=2C Research Scientist phone: 713-348-6166
> The Connexions Project http://cnx.org fax: 713-348-3665
> Rice University MS-375=2C Houston=2C TX 77005
> GPG Key fingerprint =3D F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BED=
E
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
=

--_1c9c11df-2871-4f4d-92f0-964df596e3b1_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<style><!--
..hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Verdana
}
--></style>
</head>
<body class=3D'hmmessage'>
I think you can do something like that as a postgresql user<br><br>su - pos=
tgres<br> =3B<br>psql -c "\! uname -n"<br><br>or actually you can do th=
at inside of any sql statement<br><br>psql <br>\!uname -n<br><br><br><br><b=
r>>=3B Date: Fri=2C 30 Apr 2010 09:14:04 -0500<br>>=3B From: reedstrm [at] r=
ice.edu<br>>=3B To: pgsql-admin [at] postgresql.org<br>>=3B Subject: Re: [AD=
MIN] Querying the hostname of the server<br>>=3B <br>>=3B On Fri=2C Apr=
30=2C 2010 at 03:33:13PM +0200=2C P=E9ter Kov=E1cs wrote:<br>>=3B >=3B=
Hi=2C<br>>=3B >=3B <br>>=3B >=3B I have a number of PostgreSQL ser=
vers which I often access through ssh<br>>=3B >=3B tunnel with Pgadmin3=
.. I would like to double check which one I have landed<br>>=3B >=3B on =
(if the tunnel is really configured the way I want). Is there a way to<br>&=
gt=3B >=3B query the hostname from the catalogs?<br>>=3B <br>>=3B Hmm=
=2C that's a bit tricky=2C since I assume you're using a local db<br>>=3B=
connection inside the tunnel=2C so inet_server_addr() probably returns<br>=
>=3B null. If you're talking unix/linux machines=2C then /etc/hostname _s=
hould_<br>>=3B have the current hostname in it=2C so:<br>>=3B <br>>=
=3B create temp table foo (t text)=3B<br>>=3B copy foo from '/etc/hostnam=
e'=3B<br>>=3B select * from foo=3B<br>>=3B drop table foo=3B<br>>=3B =
<br>>=3B Should work.<br>>=3B <br>>=3B Ross<br>>=3B -- <br>>=3B R=
oss Reedstrom=2C Ph.D. reedstrm [at] rice.edu<br=
>>=3B Systems Engineer &=3B Admin=2C Research Scientist phone: =
713-348-6166<br>>=3B The Connexions Project http://cnx.org =
fax: 713-348-3665<br>>=3B Rice University MS-375=2C Houston=2C TX 77005=
<br>>=3B GPG Key fingerprint =3D F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E=
88F0 BEDE<br>>=3B <br>>=3B -- <br>>=3B Sent via pgsql-admin mailing =
list (pgsql-admin [at] postgresql.org)<br>>=3B To make changes to your subscri=
ption:<br>>=3B http://www.postgresql.org/mailpref/pgsql-admin<br> =
</body>
</html>=

--_1c9c11df-2871-4f4d-92f0-964df596e3b1_--