Thread: Querying the hostname of the server
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
On Fri, Apr 30, 2010 at 03:33:13PM +0200, Péter Kovács 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@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 = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
"Ross J. Reedstrom" <reedstrm@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
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@rice.edu> wrote:
On Fri, Apr 30, 2010 at 03:33:13PM +0200, Péter Kovács 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@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 = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--- On Fri, 30/4/10, Ross J. Reedstrom <reedstrm@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 = `hostname`; return $host; $BODY$ LANGUAGE 'plperlu';
Yes, nice and simple. I just did this in C and it works ok! hostname.c =========================================== #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 = strlen(buf); //elog(NOTICE, "hostname=%s\n",buf); result=(text *)palloc(len + VARHDRSZ); SET_VARSIZE(result, len + VARHDRSZ); memcpy(VARDATA(result),buf,strlen(buf)); PG_RETURN_POINTER(result); } Makefile =========================================== MODULE_big = hostname OBJS = hostname.o ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = /var/lib/pgsql/src/Ccode/hostname top_builddir = /usr/local/src/postgresql-8.3.3 include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif hostname.sql ================================================== CREATE OR REPLACE FUNCTION hostname() RETURNS text AS '$libdir/hostname', 'hostname' LANGUAGE c IMMUTABLE STRICT; Στις Friday 30 April 2010 17:43:49 ο/η Glyn Astill έγραψε: > --- On Fri, 30/4/10, Ross J. Reedstrom <reedstrm@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 = `hostname`; > return $host; > $BODY$ > LANGUAGE 'plperlu'; > > > > > > -- Achilleas Mantzios
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, 30 Apr 2010 09:14:04 -0500
> From: reedstrm@rice.edu
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Querying the hostname of the server
>
> On Fri, Apr 30, 2010 at 03:33:13PM +0200, Péter Kovács 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@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 = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
su - postgres
psql -c "\! uname -n"
or actually you can do that inside of any sql statement
psql
\!uname -n
> Date: Fri, 30 Apr 2010 09:14:04 -0500
> From: reedstrm@rice.edu
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Querying the hostname of the server
>
> On Fri, Apr 30, 2010 at 03:33:13PM +0200, Péter Kovács 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@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 = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin