|
Home > Archive > PostgreSQL Hacks > February 2006 > pg_service.conf
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Mark Woodward 2006-02-19, 9:23 am |
| > On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote:
>
> It turns out what you like actually exists, lookup the "service"
> parameter in the connectdb string. It will read the values for the
> server, port, etc from a pg_service.conf file.
>
> There is an example in the tree but it looks something like the following:
>
> [servicename]
> dbname=blah
> user=blah
> pass=blah
>
> So all you need to specify is "service=servicename" and it will grab
> the parameters. This allows you to change the connection without
> changeing the code.
>
This is a great feature!!
It doesn't seem to be documented in the administrators guide. Its
mentioned in the libpq section, and only a reference to
pg_service.conf.sample
IMHO we should push for this to be the mainstream connection
methodology!!! The variables: host, port, and dbname are very problematic
for admins and developers who often live in different worlds.
The developers "should" just use the "servicename" of a database, and the
admins should maintain pg_service.conf. This moves the responsibility of
the wheres and hows of connecting to the database to the admin away from
the developer.
Should there be a section of the administration manual for this?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Douglas McNaught 2006-02-19, 9:23 am |
| Simon Riggs <simon@2ndquadrant.com> writes:
> A server-side (i.e. centrally managed) name server seems like an
> improvement over the client-side solutions described, IMHO, but I'd
> leave it to others to describe how that might work. (e.g. DNS is a
> better solution than multiple distributed /etc/hosts files).
Funnily enough, you could *use* DNS for this--you could define a
custom RR type containing hostname, port, database etc and have
entries in DNS for each "service" (e.g. 'production-db.mycorp.com').
I think HESIOD used this mechanism.
Of course, you'd need an internal DNS server that you had full control
over...
-Doug
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Martijn van Oosterhout 2006-02-19, 9:23 am |
| On Sun, Feb 19, 2006 at 09:58:01AM -0500, Douglas McNaught wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
>
>
> Funnily enough, you could *use* DNS for this--you could define a
> custom RR type containing hostname, port, database etc and have
> entries in DNS for each "service" (e.g. 'production-db.mycorp.com').
> I think HESIOD used this mechanism.
Well, there exist such things as SRV records already for describing how
to find services. In theory you could create an entry like:
_postgres._tcp.example.com SRV 10 5 5432 db1.example.com
So that if you typed "psql example.com" it would lookup the server and
port number. You may be able to put a dbname after that, not sure. And
you can always put whatever you like into a TXT record.
In any case, someone still needs to write the code for it.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
| |
| Mark Woodward 2006-02-19, 9:23 am |
| > On Sun, Feb 19, 2006 at 10:00:01AM -0500, Mark Woodward wrote:
>
> Indeed, I only just found out about it yesterday. It's a very little
> known feature that needs some advertisement. Right now we need to work
> up some documentation patches so people come across it easier.
>
> Where do you think it should be mentioned?
As it was mentioned in another reply, this is not "everything" I wanted,
but it is a big step closer that makes the rest managable.
As for the "central" administration issue, yes, it is not a central
administration solution, but files like these fall into the category of
one to many "push" strategies, something like "bulkcopy -f targets
pg_service.conf /usr/local/etc"
I think it should be clearly in the administration section of the manual.
A DBA is not going to look at the libpq section, similarly, PHP or Java
developers won't either. I use libpq all the time, the last time I looked
at pq_connect was years ago.
Like I said, this is a REALLY USEFULL feature that should be presented as
the "best method" for specifying databases, in the administration manual.
It should also be mentioned in the PHP API as well.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Mark Woodward 2006-02-19, 11:23 am |
| > On Sun, 2006-02-19 at 10:00 -0500, Mark Woodward wrote:
>
> Yes, it is, but there is a distinct difference between what you asked
> for and what have been described as solutions (good though they are).
Well, true, it isn't what I want, but it makes a big step.
>
> Both services and pg_service.conf are client-side solutions. So if you
> have 20,000 clients to worry about you have some problems. What was
> proposed was a central naming service (described as a database of known
> servers) that would allow a server-side name to service mapping.
True, but the one to many cluster push solution has been dealt with so
many times that as a datacenter solution isn't too troubling.
>
> A server-side (i.e. centrally managed) name server seems like an
> improvement over the client-side solutions described, IMHO, but I'd
> leave it to others to describe how that might work. (e.g. DNS is a
> better solution than multiple distributed /etc/hosts files).
DNS isn't always a better solution than /etc/hosts, both have their pros
and cons. The /etc/hosts file is very useful for "instantaneous,"
reliable, and redundent name lookups. DNS services, espcially in a large
service environment can get bogged down. 20,000 hosts doing a lot of
lookups can require a dedicated single point of failure. OK, so you add
two DNS machines and load balance across them with a fault tollerant load
balancer, how many thousands of dollars? For how much information? A
simple "clustercpy -f targets pg_service.conf /etc" would save thousands
of dollars, increase efficiency, increase reliability, decrease electrical
costs, etc.
Don't get me wrong, DNS, as it is designed, is PERFECT for the distributed
nature of the internet, but replication of fairly static data under the
control of a central authority (the admin) is better.
>
> Best Regards, Simon Riggs
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Simon Riggs 2006-02-19, 11:23 am |
| On Sun, 2006-02-19 at 10:00 -0500, Mark Woodward wrote:
>
> This is a great feature!!
Yes, it is, but there is a distinct difference between what you asked
for and what have been described as solutions (good though they are).
Both services and pg_service.conf are client-side solutions. So if you
have 20,000 clients to worry about you have some problems. What was
proposed was a central naming service (described as a database of known
servers) that would allow a server-side name to service mapping.
A server-side (i.e. centrally managed) name server seems like an
improvement over the client-side solutions described, IMHO, but I'd
leave it to others to describe how that might work. (e.g. DNS is a
better solution than multiple distributed /etc/hosts files).
Best Regards, Simon Riggs
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Martijn van Oosterhout 2006-02-19, 11:23 am |
| On Sun, Feb 19, 2006 at 10:00:01AM -0500, Mark Woodward wrote:
>
> This is a great feature!!
>
> It doesn't seem to be documented in the administrators guide. Its
> mentioned in the libpq section, and only a reference to
> pg_service.conf.sample
Indeed, I only just found out about it yesterday. It's a very little
known feature that needs some advertisement. Right now we need to work
up some documentation patches so people come across it easier.
Where do you think it should be mentioned?
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
| |
| Peter Eisentraut 2006-02-19, 11:23 am |
| Mark Woodward wrote:
> Don't get me wrong, DNS, as it is designed, is PERFECT for the
> distributed nature of the internet, but replication of fairly static
> data under the control of a central authority (the admin) is better.
What about this zeroconf/bonjour stuff? I'm not familiar with it, but
it sounds like it could tie into this discussion.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Martijn van Oosterhout 2006-02-19, 11:23 am |
| On Sun, Feb 19, 2006 at 04:56:11PM +0100, Peter Eisentraut wrote:
> Mark Woodward wrote:
>
> What about this zeroconf/bonjour stuff? I'm not familiar with it, but
> it sounds like it could tie into this discussion.
I think the major issue is that most such systems (like RFC2782) deal
only with finding the hostname:port of the service and don't deal with
usernames/passwords/dbname. What we want is a system that not only
finds the service, but tells you enough to connect. You can't connect
to a postgres server without a dbname and these discovery protocols
don't generally provide that.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
| |
| Douglas McNaught 2006-02-19, 11:23 am |
| Peter Eisentraut <peter_e@gmx.net> writes:
> Mark Woodward wrote:
>
> What about this zeroconf/bonjour stuff? I'm not familiar with it, but
> it sounds like it could tie into this discussion.
That's a possibility, but I think it's hard to make it work outside a
single LAN (as in, it's not zero-conf anymore :) because it relies on
broadcasts.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Douglas McNaught 2006-02-19, 11:23 am |
| "Mark Woodward" <pgsql@mohawksoft.com> writes:
> DNS isn't always a better solution than /etc/hosts, both have their pros
> and cons. The /etc/hosts file is very useful for "instantaneous,"
> reliable, and redundent name lookups. DNS services, espcially in a large
> service environment can get bogged down. 20,000 hosts doing a lot of
> lookups can require a dedicated single point of failure. OK, so you add
> two DNS machines and load balance across them with a fault tollerant load
> balancer, how many thousands of dollars? For how much information? A
> simple "clustercpy -f targets pg_service.conf /etc" would save thousands
> of dollars, increase efficiency, increase reliability, decrease electrical
> costs, etc.
Um, is there something wrong with having multiple DNS servers in
resolv.conf? Other than having to time out on #1 before you try #2?
I'm genuinely curious.
> Don't get me wrong, DNS, as it is designed, is PERFECT for the distributed
> nature of the internet, but replication of fairly static data under the
> control of a central authority (the admin) is better.
You're probably right; clustercpy or rsync would work better if you
have admin access to all the machines in question. The nice thing
about the DNS method is that you wouldn't necessarily have to have
that access on an ongoing basis.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Tom Lane 2006-02-19, 1:23 pm |
| NNTP-Posting-Host: news.hub.org
X-Trace: news.hub.org 1140370914 63458 200.46.204.72 (19 Feb 2006 17:41:54 GMT)
X-Complaints-To: usenet@news.hub.org
NNTP-Posting-Date: Sun, 19 Feb 2006 17:41:54 +0000 (UTC)
X-Received: from postgresql.org (postgresql.org [200.46.204.71])
by news.hub.org (8.13.1/8.13.1) with ESMTP id k1JHfrug063451
for <pgsql-hackers@news.hub.org>; Sun, 19 Feb 2006 17:41:53 GMT
(envelope-from pgsql-hackers- owner+M79915=pgsql+2
Dhackers=news.hub.org@postgresql.org)
X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org
X-Received: from localhost (av.hub.org [200.46.204.144])
by postgresql.org (Postfix) with ESMTP id 8C8219DC814
for <pgsql-hackers-postgresql.org@localhost.postgresql.org>; Sun, 19 Feb 2006 13:41:35 -0400 (AST)
X-Received: from postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 19653-08
for <pgsql-hackers-postgresql.org@localhost.postgresql.org>;
Sun, 19 Feb 2006 13:41:34 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by postgresql.org (Postfix) with ESMTP id 087819DCB7D
for <pgsql-hackers@postgresql.org>; Sun, 19 Feb 2006 13:41:32 -0400 (AST)
X-Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k1JHfLWV011756;
Sun, 19 Feb 2006 12:41:21 -0500 (EST)
X-To: Martijn van Oosterhout <kleptog@svana.org>
X-cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgresql.org,
Mark Woodward <pgsql@mohawksoft.com>,
Simon Riggs <simon@2ndquadrant.com>
X-In-reply-to: <20060219161044.GE1323@svana.org>
X-Comments: In-reply-to Martijn van Oosterhout <kleptog@svana.org>
message dated "Sun, 19 Feb 2006 17:10:45 +0100"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110]
X-Spam-Score: 0.11
X-Spam-Level:
X-Mailing-List: pgsql-hackers
X-List-Archive: <http://archives.postgresql.org/pgsql-hackers>
X-List-Help: < mailto:majordomo@pos
tgresql.org?body=help>
X-List-ID: <pgsql-hackers.postgresql.org>
X-List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
X-List-Post: <mailto:pgsql-hackers@postgresql.org>
X-List-Subscribe: < mailto:majordomo@pos
tgresql.org?body=sub%20pgsql-hackers>
X-List-Unsubscribe: < mailto:majordomo@pos
tgresql.org?body=unsub%20pgsql-hackers>
X-Precedence: bulk
Xref: news.hub.org pgsql.hackers:17497
Martijn van Oosterhout <kleptog@svana.org> writes:
> I think the major issue is that most such systems (like RFC2782) deal
> only with finding the hostname:port of the service and don't deal with
> usernames/passwords/dbname. What we want is a system that not only
> finds the service, but tells you enough to connect.
In other words, anyone on the LAN who asks nicely can get a database
password? No thank you.
I don't actually believe that a server-side substitute for pg_service
would be worth anything at all. First, it just begs the question of
how you find the server. Second, pg_service is only really interesting
if there are multiple servers you want to connect to. It's not
reasonable to assume that one of them will know about any (let alone
all) of the others. Once you start to think about security it's even
worse: you've got that one storing passwords and so on for the other
servers.
My complaint about pg_service is actually that it should have been
designed to support per-user values more easily. It's a takeoff on
the ODBC ini file concept, but we forgot the per-user ~/.odbc.ini part.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Mark Woodward 2006-02-19, 1:23 pm |
| > Martijn van Oosterhout <kleptog@svana.org> writes:
>
> In other words, anyone on the LAN who asks nicely can get a database
> password? No thank you.
>
> I don't actually believe that a server-side substitute for pg_service
> would be worth anything at all. First, it just begs the question of
> how you find the server. Second, pg_service is only really interesting
> if there are multiple servers you want to connect to. It's not
> reasonable to assume that one of them will know about any (let alone
> all) of the others. Once you start to think about security it's even
> worse: you've got that one storing passwords and so on for the other
> servers.
Tom, mark your calendar, I think in this one instance, we are in 100%
total agreement. I'm not sure what this means, does one of have to change
our opinion?
Actually, pg_service.conf, as I think more about it, is more than just
"pg_service is only really interesting if there are multiple servers you
want to connect to," it even abstracts the physical database name, which
is interesting as well.
>
> My complaint about pg_service is actually that it should have been
> designed to support per-user values more easily. It's a takeoff on
> the ODBC ini file concept, but we forgot the per-user ~/.odbc.ini part.
I can certainly see that application, and it should be trivial to add any
that code. Do you think it is worth doing?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Mark Woodward 2006-02-19, 1:23 pm |
| > Mark Woodward wrote:
>
> What about this zeroconf/bonjour stuff? I'm not familiar with it, but
> it sounds like it could tie into this discussion.
>
Perhaps zeroconf is useful for stuff like thin clients, but I'm not sure
that it introduces anything into this discussion.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Mark Woodward 2006-02-19, 1:23 pm |
| > "Mark Woodward" <pgsql@mohawksoft.com> writes:
>
>
> Um, is there something wrong with having multiple DNS servers in
> resolv.conf? Other than having to time out on #1 before you try #2?
> I'm genuinely curious.
What is the "timeout" of that DNS lookup, before it goes to the second DNS
server?
>
>
> You're probably right; clustercpy or rsync would work better if you
> have admin access to all the machines in question. The nice thing
> about the DNS method is that you wouldn't necessarily have to have
> that access on an ongoing basis.
That is, of course, one of DNS' pros, but in an environment where that is
not nessisary, why bother?
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Douglas McNaught 2006-02-19, 8:37 pm |
| "Mark Woodward" <pgsql@mohawksoft.com> writes:
>
> What is the "timeout" of that DNS lookup, before it goes to the second DNS
> server?
I think on the order of 20-30 seconds, which may or may not be an
issue.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Steve Atkins 2006-02-19, 8:37 pm |
|
On Feb 19, 2006, at 10:59 AM, Mark Woodward wrote:
>
> What is the "timeout" of that DNS lookup, before it goes to the
> second DNS
> server?
Depends on the resolver you use. Often the "timeout" is zero. Other
times
it's adaptive, depending on history of response time from the servers.
Except in the case of horrible misconfiguration, it's rarely a problem.
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Bruce Momjian 2006-02-25, 9:49 am |
| Mark Woodward wrote:
>
> This is a great feature!!
>
> It doesn't seem to be documented in the administrators guide. Its
> mentioned in the libpq section, and only a reference to
> pg_service.conf.sample
>
> IMHO we should push for this to be the mainstream connection
> methodology!!! The variables: host, port, and dbname are very problematic
> for admins and developers who often live in different worlds.
The documenation is sparse because at the time it was added, there was
little interest in it from the community, so a single mention was added
and documentation was pushed into the config file. We can adjust that
now that there is interest.
--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com
+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|
|
|
|
|