Re: [HACKERS] pg_service.conf
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 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] pg_service.conf
On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote: Like I said, in this thread of posts, yes there are ways of doing this, and I've been doing it for years. It is just one of the rough eges that I think could be smoother. (in php) pg_connect(dbname=geo host=dbserver); Could connect and query the dbserver, if the db is not on it, connect to a database of known servers, find geo, and use that information to connect. It sounds like a simple thing, for sure, but to be useful, there needs to be buy in from the group otherwise it is just some esoteric hack. 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
Re: [HACKERS] pg_service.conf
On Sun, 2006-02-19 at 10:00 -0500, Mark Woodward wrote: On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote: Like I said, in this thread of posts, yes there are ways of doing this, and I've been doing it for years. It is just one of the rough eges that I think could be smoother. (in php) pg_connect(dbname=geo host=dbserver); Could connect and query the dbserver, if the db is not on it, connect to a database of known servers, find geo, and use that information to connect. It sounds like a simple thing, for sure, but to be useful, there needs to be buy in from the group otherwise it is just some esoteric hack. 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!! 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
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 10:00:01AM -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 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. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
Simon Riggs [EMAIL PROTECTED] 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
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 09:58:01AM -0500, Douglas McNaught wrote: Simon Riggs [EMAIL PROTECTED] 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. 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. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 10:00:01AM -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 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
Re: [HACKERS] pg_service.conf
On Sun, 2006-02-19 at 10:00 -0500, Mark Woodward wrote: On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote: Like I said, in this thread of posts, yes there are ways of doing this, and I've been doing it for years. It is just one of the rough eges that I think could be smoother. (in php) pg_connect(dbname=geo host=dbserver); Could connect and query the dbserver, if the db is not on it, connect to a database of known servers, find geo, and use that information to connect. It sounds like a simple thing, for sure, but to be useful, there needs to be buy in from the group otherwise it is just some esoteric hack. 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!! 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
Re: [HACKERS] pg_service.conf
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
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 04:56:11PM +0100, Peter Eisentraut wrote: 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. 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. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
Peter Eisentraut [EMAIL PROTECTED] writes: 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. 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
Re: [HACKERS] pg_service.conf
Mark Woodward [EMAIL PROTECTED] 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
Re: [HACKERS] pg_service.conf
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
Re: [HACKERS] pg_service.conf
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. 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
Re: [HACKERS] pg_service.conf
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. 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
Re: [HACKERS] pg_service.conf
Mark Woodward [EMAIL PROTECTED] 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. What is the timeout of that DNS lookup, before it goes to the second DNS server? 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. 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
Re: [HACKERS] pg_service.conf
Mark Woodward [EMAIL PROTECTED] 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? 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
Re: [HACKERS] pg_service.conf
On Feb 19, 2006, at 10:59 AM, Mark Woodward wrote: Mark Woodward [EMAIL PROTECTED] 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. 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
