Thread: pg_config, pg_service.conf, postgresql.conf ....

The pg_config program needs to display more information, specifically
where the location of pg_service.conf would reside.

Also, I know I've been harping on this for years (literally), but since
the PosgteSQL programs already have the notion that there is some static
directory for which to locate files (pg_service.conf), couldn't we also
use this directory to include pg_hba.conf, pg_ident.conf, and perhaps even
postgresql.conf?

Maybe we also add to pg_service.conf enough data to start a database so
that a tool, perhaps pg_service, could read and extract the info for
pg_ctl, so databases can be started by service names:

pg_ctl -S service_name start

This would tie together a great number of loose ends in PostgreSQL that I
have been whining about for years.

I'll do the coding and submit a patch, but I'd like to open a discussion
to know if it is worth the effort, i.e. do you guys want this behavior if
it is done well?

Mark Woodward wrote:
> The pg_config program needs to display more information, specifically
> where the location of pg_service.conf would reside.

pg_config --sysconfdir

> Also, I know I've been harping on this for years (literally), but
> since the PosgteSQL programs already have the notion that there is
> some static directory for which to locate files (pg_service.conf),
> couldn't we also use this directory to include pg_hba.conf,
> pg_ident.conf, and perhaps even postgresql.conf?

Considering that pg_service.conf is a client configuration file and the 
others are server configuration files, I don't think there is a causal 
relationship between putting them one place or another, independent of 
the individual merit of placing them in particular spot.  I'm not sure 
that sentence makes sense.

> Maybe we also add to pg_service.conf enough data to start a database
> so that a tool, perhaps pg_service, could read and extract the info
> for pg_ctl, so databases can be started by service names:
>
> pg_ctl -S service_name start

I don't mind a mechanism that pg_ctl can start more than one database 
cluster.  The Debian packages have started to develop such a system 
independently because of upgrading requirements, but putting it into 
the mainline would certainly be useful.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Tue, Feb 21, 2006 at 09:39:16AM -0500, Mark Woodward wrote:
> The pg_config program needs to display more information, specifically
> where the location of pg_service.conf would reside.

AIUI it's supposed to be in SYSCONFDIR which is displayed by pg_config.
I believe you can place the other config files there also.

> Maybe we also add to pg_service.conf enough data to start a database so
> that a tool, perhaps pg_service, could read and extract the info for
> pg_ctl, so databases can be started by service names:
>
> pg_ctl -S service_name start

This I don't understand. pg_service.conf is for configuring how clients
should connect to the server. It doesn't have any of the info needed
for the server to start up. What have you actually got in mind?

> This would tie together a great number of loose ends in PostgreSQL that I
> have been whining about for years.
>
> I'll do the coding and submit a patch, but I'd like to open a discussion
> to know if it is worth the effort, i.e. do you guys want this behavior if
> it is done well?

At this point I don't understand what behaviour you want. Do you want
to be able to control multiple backends or just one? I know the Debian
packages have stuff in them to deal with a number of clusters running
possibly different versions but I'm not sure if this is what you are
hinting at.

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.

Peter Eisentraut <peter_e@gmx.net> writes:
> Mark Woodward wrote:
>> Also, I know I've been harping on this for years (literally), but
>> since the PosgteSQL programs already have the notion that there is
>> some static directory for which to locate files (pg_service.conf),
>> couldn't we also use this directory to include pg_hba.conf,
>> pg_ident.conf, and perhaps even postgresql.conf?

> Considering that pg_service.conf is a client configuration file and the 
> others are server configuration files, I don't think there is a causal 
> relationship between putting them one place or another, independent of 
> the individual merit of placing them in particular spot.

Indeed, such an arrangement would tie pg_service to a single server
(and/or prevent multiple servers from being run on a single box).
This proposal seems nonsensical to me.
        regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:
> Mark Woodward wrote:
>> pg_ctl -S service_name start

> I don't mind a mechanism that pg_ctl can start more than one database 
> cluster.

You mean "pg_ctl start -D pgdatalocation", no?
        regards, tom lane

> Mark Woodward wrote:
>> The pg_config program needs to display more information, specifically
>> where the location of pg_service.conf would reside.
>
> pg_config --sysconfdir

Hmm, that doesn't show up with pg_config --help.

markw@outland:~$  pg_config --sysconfdir
pg_config: invalid argument: --sysconfdir

Try "pg_config --help" for more information

>
>> Also, I know I've been harping on this for years (literally), but
>> since the PosgteSQL programs already have the notion that there is
>> some static directory for which to locate files (pg_service.conf),
>> couldn't we also use this directory to include pg_hba.conf,
>> pg_ident.conf, and perhaps even postgresql.conf?
>
> Considering that pg_service.conf is a client configuration file and the
> others are server configuration files, I don't think there is a causal
> relationship between putting them one place or another, independent of
> the individual merit of placing them in particular spot.  I'm not sure
> that sentence makes sense.

Well, here's the thinking involved:

pg_service.conf may currently be considered a "client side" utility, but
it need not only be considered that.

One of my difficulties with PostgreSQL is that there is no "standardized"
location for where everything is located, i.e. self documenting. If you
know that "/usr/local/pgsql/etc/pg_service.conf" will contain a list of
services, that is really awesome. That you can use these services in libpq
is utterly fantastic, from a development point of view. IMHO that is how
ALL access to the database should be documented, with the "dbname," port,
etc. offered in the administrators guide as a we to debug the services
file.

Now, from an administrators point of view, say you have to manage over
1000 machines, (This is not unlikely in a Yahoo or other service
provider), how do you know where the physical databases reside on each of
these machines? OK, maybe you document it somewhere, but we all know that
the docs never get updated reliably. Even if you standardize, standards
change over years, and some of these machines last for years.

Allowing pg_service.conf to contain information about where physcial
databases reside on the server is HUGE.

This may be helpful to some who are willing to retrofit, but I'm think
more as a "moving forward" point of view.

OK, maybe pg_service.conf is not the right place for this, and that maybe
a valid argument, but the mechanics involved would be a great asset to the
admin. Perhaps pg_servers.conf?

On Tue, Feb 21, 2006 at 11:14:58AM -0500, Mark Woodward wrote:
> > pg_config --sysconfdir
>
> Hmm, that doesn't show up with pg_config --help.

What version are you using? If I type pg_config without argument it
appears in the list.

> pg_service.conf may currently be considered a "client side" utility, but
> it need not only be considered that.
>
> One of my difficulties with PostgreSQL is that there is no "standardized"
> location for where everything is located, i.e. self documenting. If you
> know that "/usr/local/pgsql/etc/pg_service.conf" will contain a list of
> services, that is really awesome. That you can use these services in libpq
> is utterly fantastic, from a development point of view. IMHO that is how
> ALL access to the database should be documented, with the "dbname," port,
> etc. offered in the administrators guide as a we to debug the services
> file.

pg_service.conf has to be for client because it may refer to databases
on other machines. Trusting it as a list of databases o the current
machine is silly.

> This may be helpful to some who are willing to retrofit, but I'm think
> more as a "moving forward" point of view.
>
> OK, maybe pg_service.conf is not the right place for this, and that maybe
> a valid argument, but the mechanics involved would be a great asset to the
> admin. Perhaps pg_servers.conf?

Like I said, perhaps you should look into the Debian package
configuration "postgresql-common". There it has a structure for storing
the config for many different clusters on the same machine. It
remembers the port and such. Here a list of programs it contains:

pg_checksystem
pg_createcluster
pg_ctlcluster
pg_dropcluster
pg_lsclusters
pg_maintenance
pg_upgradecluster

I havn't had the opportunity to look into it much but it looks like
it'll solve what you want. It allows you to configure the user, group,
data dir, socket dir, log file and encoding for each cluster. Each
cluster has a name, and pg_lsclusters would list all clusters on a
machine.

Is this what you're looking for?

http://pdo.debian.net/unstable/misc/postgresql-common

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" <pgsql@mohawksoft.com> writes:
>> pg_config --sysconfdir

> Hmm, that doesn't show up with pg_config --help.

It's in 8.1.

> One of my difficulties with PostgreSQL is that there is no "standardized"
> location for where everything is located, i.e. self documenting. If you
> know that "/usr/local/pgsql/etc/pg_service.conf" will contain a list of
> services, that is really awesome.

I can't see a use-case for this at all, certainly not one that would
override the reasons why there isn't a standardized location already.
If we tried to force this to happen, it would* break building temp installations without root, because you'd  not be
ableto list the installation in the central file* get modified by packagers to fit their ideas of filesystem  layout,
hencethe "standard" location would be no such thing
 

The concept really only works for one root-made installation on a single
filesystem layout, and in that situation you hardly need it anyway,
because you already know where the database is gonna be (eg with RPM
installations it's gonna be /var/lib/pgsql/data).

I don't see any plausibility to the concept of a configuration file that
is in a more predictable place than the database itself is.  I certainly
don't see any plausibility to the idea that we're going to be able to
force such a file to exist and be accurate in the face of admin
errors/oversights, which is basically the situation you are presenting
as the use-case.
        regards, tom lane

On Tue, 21 Feb 2006, Mark Woodward wrote:

> > Mark Woodward wrote:
> >> The pg_config program needs to display more information, specifically
> >> where the location of pg_service.conf would reside.
> >
> > pg_config --sysconfdir
>
> Hmm, that doesn't show up with pg_config --help.
>
> markw@outland:~$  pg_config --sysconfdir
> pg_config: invalid argument: --sysconfdir
>
> Try "pg_config --help" for more information
>
> >
> >> Also, I know I've been harping on this for years (literally), but
> >> since the PosgteSQL programs already have the notion that there is
> >> some static directory for which to locate files (pg_service.conf),
> >> couldn't we also use this directory to include pg_hba.conf,
> >> pg_ident.conf, and perhaps even postgresql.conf?
> >
> > Considering that pg_service.conf is a client configuration file and the
> > others are server configuration files, I don't think there is a causal
> > relationship between putting them one place or another, independent of
> > the individual merit of placing them in particular spot.  I'm not sure
> > that sentence makes sense.
>
> Well, here's the thinking involved:
>
> pg_service.conf may currently be considered a "client side" utility, but
> it need not only be considered that.

I think it should.  The meaning of what a client side configuration needs
and what a server side configuration needs are different, and intermixing
them only either leaks information (server side information visible on
clients) or involves working around that with multiple configuration
files, which pretty much defeats the purpose of sharing the configuration.

In addition, the "service" on the client side is not the same as a cluster
on the server side AFAIK (and if I'm right, that's pretty necessary), so
trying to equate them seems like a bad idea for things like pg_ctl.  I
think you'd need a different concept.

> "Mark Woodward" <pgsql@mohawksoft.com> writes:
>>> pg_config --sysconfdir
>
>> Hmm, that doesn't show up with pg_config --help.
>
> It's in 8.1.
>
>> One of my difficulties with PostgreSQL is that there is no
>> "standardized"
>> location for where everything is located, i.e. self documenting. If you
>> know that "/usr/local/pgsql/etc/pg_service.conf" will contain a list of
>> services, that is really awesome.
>
> I can't see a use-case for this at all, certainly not one that would
> override the reasons why there isn't a standardized location already.
> If we tried to force this to happen, it would
>     * break building temp installations without root, because you'd
>       not be able to list the installation in the central file
>     * get modified by packagers to fit their ideas of filesystem
>       layout, hence the "standard" location would be no such thing
>
> The concept really only works for one root-made installation on a single
> filesystem layout, and in that situation you hardly need it anyway,
> because you already know where the database is gonna be (eg with RPM
> installations it's gonna be /var/lib/pgsql/data).
>
> I don't see any plausibility to the concept of a configuration file that
> is in a more predictable place than the database itself is.  I certainly
> don't see any plausibility to the idea that we're going to be able to
> force such a file to exist and be accurate in the face of admin
> errors/oversights, which is basically the situation you are presenting
> as the use-case.

I think the fact that you can't enforce a standardized paradigm does not
mean you can't offer one.

A file, be it pg_services.conf, or something new like pg_clusters.conf or
pg_servers.conf, is very useful.

Maybe you haven't had to manage an absurdly large number of systems that
have grown/changed over years. Systems come and go, mounting points,
/vol01, /vol02, /raid0, /raid1, etc. have their own convention.

As a guy who administers a lot of systems, sometimes over the span of
years,  I can not understate the need for "a" place for the admin to find
what databases are on the machine and where they are located.

Your assertion that this file would "only works for one root-made
installation on a single filesystem layout" totally misses the point. The
point is that me, a consultant, could find where the database is, easily.
Given a large system, say it has 3 or 4 separate databases on it. How do
you know which is what?

/usr/local/pgsql/etc/pg_servers.conf >>>

#[SERVERNAME]
#DATADIR=LOC
#PORT=PORT
#..

[GEO]
DATADIR=/RAID1/pg80
PORT=5433
USER=postgres

[IMCDDS]
DATADIR=/home/orourke/pg80
PORT=5434
USER=orourke

[SITE]
DATADIR=/home/agarn/pg80
PORT=5432
USER=agarn

<<<<<<<<<<<

You get the idea.

This in no way removes existing functionality, but it provides a utility
where an admin can standarize their database installations.

pg_ctl startall

Could start all the database server processes in the config file. Can you
imagine the aggrevation of trying to find all this? If you've long
forgoten where any of this is?

Tom Lane wrote:
> > I don't mind a mechanism that pg_ctl can start more than one
> > database cluster.
>
> You mean "pg_ctl start -D pgdatalocation", no?

No, I mean pg_ctl start -D location1 -D location2, better yet controlled 
by a configuration file.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Mark Woodward wrote:
> OK, maybe pg_service.conf is not the right place for this, and that
> maybe a valid argument, but the mechanics involved would be a great
> asset to the admin. Perhaps pg_servers.conf?

I can see that being useful, in terms of providing pg_ctl with a list of 
instances to start.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Mark Woodward wrote:

> As a guy who administers a lot of systems, sometimes over the span of
> years,  I can not understate the need for "a" place for the admin to find
> what databases are on the machine and where they are located.
> 
> Your assertion that this file would "only works for one root-made
> installation on a single filesystem layout" totally misses the point. The
> point is that me, a consultant, could find where the database is, easily.
> Given a large system, say it has 3 or 4 separate databases on it. How do
> you know which is what?
> 

I think you make a good point. However you probably need to include the 
location of the server software too (in case you run multiple versions). 
This means there really needs to be a standard location (e.g 
/usr/local/etc, /etc ...???? on win32) for this "cluster registration" 
file, and you need to list (at minimum):

PGHOME
DATADIR
PORT
USER

As Tom hinted, to be effective, this would need to be maintained by the 
installation process, otherwise it is just another source of confusion 
(like the Oracle site I went to last year where they had an incorrect 
/etc/oratab - I wasted *hours* on that....)

Cheers

Mark

> Mark Woodward wrote:
>
>> As a guy who administers a lot of systems, sometimes over the span of
>> years,  I can not understate the need for "a" place for the admin to
>> find
>> what databases are on the machine and where they are located.
>>
>> Your assertion that this file would "only works for one root-made
>> installation on a single filesystem layout" totally misses the point.
>> The
>> point is that me, a consultant, could find where the database is,
>> easily.
>> Given a large system, say it has 3 or 4 separate databases on it. How do
>> you know which is what?
>>
>
> I think you make a good point. However you probably need to include the
> location of the server software too (in case you run multiple versions).
> This means there really needs to be a standard location (e.g
> /usr/local/etc, /etc ...???? on win32) for this "cluster registration"
> file, and you need to list (at minimum):
>
> PGHOME
> DATADIR
> PORT
> USER

I'm not sure that I agree. At least in my experience, I wouldn't have more
than one installation of PostgreSQL in a production machine. It is
potentially problematic.


>
> As Tom hinted, to be effective, this would need to be maintained by the
> installation process, otherwise it is just another source of confusion
> (like the Oracle site I went to last year where they had an incorrect
> /etc/oratab - I wasted *hours* on that....)

At least with "oratab" using standards would help.

I can tell you, I have tried to find PostgreSQL installs after a power
outage and it is hell. If people know there is *a* standard and are
expected to use it, they will, they want their systems to run. As it is
PostgreSQL has no standard and provides no mechanism to do this.

Quoth pgsql@mohawksoft.com ("Mark Woodward"):
>> Mark Woodward wrote:
>>> As a guy who administers a lot of systems, sometimes over the span of
>>> years,  I can not understate the need for "a" place for the admin to
>>> find
>>> what databases are on the machine and where they are located.
>>>
>>> Your assertion that this file would "only works for one root-made
>>> installation on a single filesystem layout" totally misses the point.
>>> The
>>> point is that me, a consultant, could find where the database is,
>>> easily.
>>> Given a large system, say it has 3 or 4 separate databases on it. How do
>>> you know which is what?
>>>
>>
>> I think you make a good point. However you probably need to include the
>> location of the server software too (in case you run multiple versions).
>> This means there really needs to be a standard location (e.g
>> /usr/local/etc, /etc ...???? on win32) for this "cluster registration"
>> file, and you need to list (at minimum):
>>
>> PGHOME
>> DATADIR
>> PORT
>> USER
>
> I'm not sure that I agree. At least in my experience, I wouldn't
> have more than one installation of PostgreSQL in a production
> machine. It is potentially problematic.

Curious.  On our production machines we seldom have less than four
PostgreSQL instances on any given machine.

Perhaps we're wrong and should stop that, but I'd need have to have
evidence WAY more specific than some devoid-of-details claim of "It is
potentially problematic."

>> As Tom hinted, to be effective, this would need to be maintained by
>> the installation process, otherwise it is just another source of
>> confusion (like the Oracle site I went to last year where they had
>> an incorrect /etc/oratab - I wasted *hours* on that....)
>
> At least with "oratab" using standards would help.
>
> I can tell you, I have tried to find PostgreSQL installs after a
> power outage and it is hell. If people know there is *a* standard
> and are expected to use it, they will, they want their systems to
> run. As it is PostgreSQL has no standard and provides no mechanism
> to do this.

What is happening is entirely proper.

PostgreSQL provides mechanisms; it does NOT impose policies.  This is
not a mistake; it is intentional.

If you require a policy, then YOU are free to choose the policy that
YOU need.  You're not forced to accept other peoples' policies that
may conflict with things in your environment.
-- 
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxdatabases.info/info/lisp.html
Everyone has a photographic memory, some don't have film.

Mark Woodward wrote:

> I'm not sure that I agree. At least in my experience, I wouldn't have more
> than one installation of PostgreSQL in a production machine. It is
> potentially problematic.
> 

I agree with you for production environments, but for development, test, 
support (and pre-sales) machines there are reasonable requirements for 
several.

Even if you have only one installation - something to tell you *where* 
the binaries are installed is convenient - as there are quite a few 
common locations (e.g. packages installing in /usr or /usr/local, source 
builds in /usr/local/pgsql or /opt/pgsql). I've seen many *uncommon* 
variants: (e.g. /usr/local/postgresql, /usr/local/postgresql-<version>, 
/usr/local/pgsql/<version>, ...).

Admittedly, given that the binaries are likely to be in the 
cluster-owners default PATH, it is not as hard to find them as the data 
directory. However, this is all about convenience it would seem, since 
(for many *nix platforms) two simple searches will give you most of what 
is needed:

$ locate postmaster
$ locate pg_hba.conf


Cheers

Mark

In the last exciting episode, markir@paradise.net.nz (Mark Kirkwood) wrote:
> Mark Woodward wrote:
>> I'm not sure that I agree. At least in my experience, I wouldn't
>> have more than one installation of PostgreSQL in a production
>> machine. It is potentially problematic.
>
> I agree with you for production environments, but for development,
> test, support (and pre-sales) machines there are reasonable
> requirements for several.

I still have to ask what *specifically* you imagine to be the
"potentially problematic" aspect of having multiple installations on a
production system.

Furthermore, I have to vigorously disagree with the claim, as I have a
counterexample that has no "potentially" about it.

Up until version 8.0, when cache management changed dramatically,
there were kinds of workload that more or less *mandated* having
multiple installations.  (... So that sequences of heavy updates
involving Pretty Large Rows wouldn't destroy the shared memory
cache...)

As far as I'm concerned, on versions 7.2 thru 7.4 (and we still have
7.4 systems in production), having LESS than two installations of
PostgreSQL on production machines is *specifically* and
*conspicuously* problematic for patterns of fairly heavy updates *that
we see*.  We saw some very specific performance degradations on our
systems that were only alleviated by adding an extra install.

I've got performance statistics around to back this up, not that I
necessarily have permission to give them out ;-).

On those systems, NOT having multiple PG installs is *specifically*
problematic.  Nothing "potential" about it; without the extra
instances around, performance was BAD.
-- 
"cbbrowne","@","gmail.com"
http://linuxfinances.info/info/
I knew you weren't really interested.
-- Marvin the Paranoid Android

Christopher Browne wrote:
> In the last exciting episode, markir@paradise.net.nz (Mark Kirkwood) wrote:
>>I agree with you for production environments, but for development,
>>test, support (and pre-sales) machines there are reasonable
>>requirements for several.
> 
> 
> I still have to ask what *specifically* you imagine to be the
> "potentially problematic" aspect of having multiple installations on a
> production system.
> 
> Furthermore, I have to vigorously disagree with the claim, as I have a
> counterexample that has no "potentially" about it.
> 
>

Sorry Christopher, I wasn't being clear enough - I (and Mark W as well I 
*think*) were referring to multiple postgres clusters with *different* 
versions of the binaries (e.g. running 1 cluster with 7.4.10, another 
with 8.0.5 and another with 8.1.2) - as opposed to multiple clusters 
using the *same* binaries (e.g.  three 8.1.2 clusters) - which I 
certainly have no issue with in any environment.

I was thinking that having several clusters with different versions of 
the software on a production box made for extra confusion (e.g "create 
the stored function on all the prod instances - oh yeah, don't forget to 
patch it for the 7.4 one....").

Having said that, I run *exactly* this configuration, as I tend to use 
my machines to replicate problems for customers (so I need whatever 
version *they* are running), but I guess that qualifies as a "support" 
installation in this discussion.

Cheers

Mark

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Mark Kirkwood
> Sent: 22 February 2006 01:53
> To: Mark Woodward
> Cc: Tom Lane; Peter Eisentraut; kleptog@svana.org;
> pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] pg_config, pg_service.conf,
> postgresql.conf ....
>
> I think you make a good point. However you probably need to
> include the
> location of the server software too (in case you run multiple
> versions).
> This means there really needs to be a standard location (e.g
> /usr/local/etc, /etc ...???? on win32) for this "cluster
> registration"
> file, and you need to list (at minimum):

pgInstaller actually already does this on Windows to help other apps
find the local installations.

In the registry, we have something like:

==================================================================
[HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations]

[HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\{34D95765-2D5A-470
F-A39F-BC9DEAAAF04F}]
"Base Directory"="C:\\Program Files\\PostgreSQL\\8.1\\"
"Data Directory"="C:\\Program Files\\PostgreSQL\\8.1\\data\\"
"Version"="8.1"
"Service ID"="pgsql-8.1"

[HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services]

[HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\pgsql-8.1]
"Display Name"="PostgreSQL Database Server 8.1"
"Service Account"="PC30\\postgres"
"Data Directory"="C:\\Program Files\\PostgreSQL\\8.1\\data\\"
"Port"=dword:00001538
"Database Superuser"="postgres"
"Encoding"="SQL_ASCII"
"Locale"="C"
"Product Code"="{34D95765-2D5A-470F-A39F-BC9DEAAAF04F}"
==================================================================

As an example, pgAdmin uses this info to automatically register any
local installations.

Regards, Dave

Am Mittwoch, 22. Februar 2006 09:06 schrieb Dave Page:
> As an example, pgAdmin uses this info to automatically register any
> local installations.

Curiously enough, pgAdmin already has a "Service" field in its connection 
dialog, but I guess that isn't the same thing.  The documentation is unclear 
on that.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> Am Mittwoch, 22. Februar 2006 09:06 schrieb Dave Page:
> 
>>As an example, pgAdmin uses this info to automatically register any
>>local installations.
> 
> 
> Curiously enough, pgAdmin already has a "Service" field in its connection 
> dialog, but I guess that isn't the same thing.  The documentation is unclear 
> on that.

On win32, the name of the win32 service ist to be entered (as used in 
the command NET START <servicename>), for *ix the command to start/stop 
pgsql (e.g. "sudo /etc/init.d/pgsql", start/stop/status is appended by 
pgAdmin).
Apparently, I should add this information to the help file some day.

Regards,
Andreas

> Quoth pgsql@mohawksoft.com ("Mark Woodward"):
>>> Mark Woodward wrote:
>>>> As a guy who administers a lot of systems, sometimes over the span of
>>>> years,  I can not understate the need for "a" place for the admin to
>>>> find
>>>> what databases are on the machine and where they are located.
>>>>
>>>> Your assertion that this file would "only works for one root-made
>>>> installation on a single filesystem layout" totally misses the point.
>>>> The
>>>> point is that me, a consultant, could find where the database is,
>>>> easily.
>>>> Given a large system, say it has 3 or 4 separate databases on it. How
>>>> do
>>>> you know which is what?
>>>>
>>>
>>> I think you make a good point. However you probably need to include the
>>> location of the server software too (in case you run multiple
>>> versions).
>>> This means there really needs to be a standard location (e.g
>>> /usr/local/etc, /etc ...???? on win32) for this "cluster registration"
>>> file, and you need to list (at minimum):
>>>
>>> PGHOME
>>> DATADIR
>>> PORT
>>> USER
>>
>> I'm not sure that I agree. At least in my experience, I wouldn't
>> have more than one installation of PostgreSQL in a production
>> machine. It is potentially problematic.
>
> Curious.  On our production machines we seldom have less than four
> PostgreSQL instances on any given machine.

"instances" or "installations?" in a production machine, I would keep only
one version of the software and often multiple data "clusters." (I guess
"cluster" is the right word for a physical database directory.)

>
> Perhaps we're wrong and should stop that, but I'd need have to have
> evidence WAY more specific than some devoid-of-details claim of "It is
> potentially problematic."

By problematic, I mean, not only do you need to find the database
"cluster," you would also need to find the correct software for it.

"which postmaster" would not be enough.
>
>>> As Tom hinted, to be effective, this would need to be maintained by
>>> the installation process, otherwise it is just another source of
>>> confusion (like the Oracle site I went to last year where they had
>>> an incorrect /etc/oratab - I wasted *hours* on that....)
>>
>> At least with "oratab" using standards would help.
>>
>> I can tell you, I have tried to find PostgreSQL installs after a
>> power outage and it is hell. If people know there is *a* standard
>> and are expected to use it, they will, they want their systems to
>> run. As it is PostgreSQL has no standard and provides no mechanism
>> to do this.
>
> What is happening is entirely proper.

I completely disagree.

>
> PostgreSQL provides mechanisms; it does NOT impose policies.  This is
> not a mistake; it is intentional.

I 100% absolutely agree with the "mechanism not policies" debate, but in
the case of multiple "clusters" on one machine, there is NO postgresql
standard mechanism to aid this.

>
> If you require a policy, then YOU are free to choose the policy that
> YOU need.  You're not forced to accept other peoples' policies that
> may conflict with things in your environment.

The problem is that there is no mechanism through which one can implement
policy. You are left to "roll your own" each and every time. A mechanism
provided, but not enforced, by postgresql would go a LONG way toward
enabling a coherent policy.

> Mark Woodward wrote:
>
>> I'm not sure that I agree. At least in my experience, I wouldn't have
>> more
>> than one installation of PostgreSQL in a production machine. It is
>> potentially problematic.
>>
>
> I agree with you for production environments, but for development, test,
> support (and pre-sales) machines there are reasonable requirements for
> several.

Oh, sure, for dev, margeting, etc. It doesn't matter. When you have to
manage a thousand systems, standards save tons of work.
>
> Even if you have only one installation - something to tell you *where*
> the binaries are installed is convenient - as there are quite a few
> common locations (e.g. packages installing in /usr or /usr/local, source
> builds in /usr/local/pgsql or /opt/pgsql). I've seen many *uncommon*
> variants: (e.g. /usr/local/postgresql, /usr/local/postgresql-<version>,
> /usr/local/pgsql/<version>, ...).
>
> Admittedly, given that the binaries are likely to be in the
> cluster-owners default PATH, it is not as hard to find them as the data
> directory. However, this is all about convenience it would seem, since
> (for many *nix platforms) two simple searches will give you most of what
> is needed:
>
> $ locate postmaster
> $ locate pg_hba.conf
>

That's not the issue.
I find it frustrating sometimes because when I describe one scenario,
people debate it using other scenarios. Maybe I lack the communications
skills to convey the problem accurately.

Lets say you are an admin at XYZ Services Corp. You have 20 data centers
world wide. In each data center, you have 10 to 1000 PostgreSQL servers.
Through your VPN you can access any one of the machines in any data center
through a simple IP address, thanks to your VPN.

First Scenario:
One of your databases crashed because it ran out of disk space. (someone
forgot to check the free space often enough.) The CIO, rightfully, now
requires a weekly database free space report. From this report you track
trends and etc.

Now, if there were a standard file from which you could "see" what
databases are installed and running on this system, you could write a
shell script:

scp $HOST:/usr/local/pgsql/etc/pg_clusters.conf $HOST.conf
ssh $HOST "df" > $HOST.df
rptdbfree.pl $HOST.conf $HOST.df

The "rptdbfree.pl" is a perl script to parse the pg_clusters.conf and
extract the volumes on which the databases reside. The "df" file has the
volume information for each disk.

You could run this over night to find the state of all your databases.

Second Scenario:
You are the same admin at the same XYZ corp. An electrician pulls the
breaker in the data center and your systems go down (This actually happend
to one installation I worked on). A couple of the admins in charge of some
of the boxes are on vacation and "accidentally" forgot to bring their cell
phones.

A few of the systems didn't come up correctly. You need to find the
correct databases. Unfortunately there are more database cluster
directories than there should be, and the admin hadn't yet documented
which was which. You don't even know how to test if they are.

Your site is down, you are very stressed, you are cursing the guy that
didn't write this stuff down. Since there is no facility to bring up
multiple PG databases, there is no standard to follow.

Wouldn't it be nice, to be able to do "pg_ctl startall?" Or better yet,
just have this in the startup?


I'm not saying that we abandon how it is currently done, I'm just
suggesting that we provide the facilities to help enterprise solutions.

On Wed, 22 Feb 2006, Mark Woodward wrote:

> > Mark Woodward wrote:
> >
> >> I'm not sure that I agree. At least in my experience, I wouldn't have
> >> more
> >> than one installation of PostgreSQL in a production machine. It is
> >> potentially problematic.
> >>
> >
> > I agree with you for production environments, but for development, test,
> > support (and pre-sales) machines there are reasonable requirements for
> > several.
>
> Oh, sure, for dev, margeting, etc. It doesn't matter. When you have to
> manage a thousand systems, standards save tons of work.
> >
> > Even if you have only one installation - something to tell you *where*
> > the binaries are installed is convenient - as there are quite a few
> > common locations (e.g. packages installing in /usr or /usr/local, source
> > builds in /usr/local/pgsql or /opt/pgsql). I've seen many *uncommon*
> > variants: (e.g. /usr/local/postgresql, /usr/local/postgresql-<version>,
> > /usr/local/pgsql/<version>, ...).
> >
> > Admittedly, given that the binaries are likely to be in the
> > cluster-owners default PATH, it is not as hard to find them as the data
> > directory. However, this is all about convenience it would seem, since
> > (for many *nix platforms) two simple searches will give you most of what
> > is needed:
> >
> > $ locate postmaster
> > $ locate pg_hba.conf
> >
>
> That's not the issue.
> I find it frustrating sometimes because when I describe one scenario,
> people debate it using other scenarios. Maybe I lack the communications
> skills to convey the problem accurately.

I don'tn think it is that.  I think it's to some extent that you are
starting from a basis that hasn't yet been agreed upon.

First, you should show that your scenario is reasonable. I haven't seen a
reason to assume that the configuration file will be more up to date than
other documentation of the setup. Without that, the theoretical benefit of
the configuration is not fully realized, and in fact could be negative
(for example, what if in your second scenario it is the important db
that's not in the config).

Second, you should show that it belongs in the main package. I think you
could write this without touching the main package. There's then a
question of whether having it in the main package has any negative effect
on people that aren't using it (which includes opportunity cost of
features that might be lost because they don't fit the scenario -- for
example, if someone does have multiple versions of postgresql, does this
preclude a feature to make their administration better) and a question of
whether there are any pieces that must be in the main package.

I think this is a reasonable idea because it can help suggest a way of
doing this to people that might otherwise be doing it by the seat of their
pants, but that's a somewhat different argument. I don't think that I'd
trust the configuration file to be correct if I couldn't trust the admin
to be doing a good job, and to me that includes at least marginally
reasonable documentation. I think that having some system for doing this
(whether it's in the main package or not) is better than multiple people
writing their own.  I am not sure whether having it in the main package
doesn't have a small negative effect on people that need for other reasons
to do their own thing but I haven't looked at it seriously. But since I
don't have time to do it, I'm not going to expect someone else to do it if
they disagree.

Mark Woodward wrote:
>
>>
>>Admittedly, given that the binaries are likely to be in the
>>cluster-owners default PATH, it is not as hard to find them as the data
>>directory. However, this is all about convenience it would seem, since
>>(for many *nix platforms) two simple searches will give you most of what
>>is needed:
>>
>>$ locate postmaster
>>$ locate pg_hba.conf
>>
> 
> 
> That's not the issue.
> I find it frustrating sometimes because when I describe one scenario,
> people debate it using other scenarios. Maybe I lack the communications
> skills to convey the problem accurately.

I think you are describing what you want very clearly. However I suspect 
people will want the solution to your scenario to be sufficiently 
general to help theirs too - which can sometimes be frustrating when you 
see your idea suffer "scope creep", but just as often can mean your idea 
gets critiqued and melded into something better that you would have 
thought of alone - well that's what I've found anyway :-).

> 
> Lets say you are an admin at XYZ Services Corp. You have 20 data centers
> world wide. In each data center, you have 10 to 1000 PostgreSQL servers.
> Through your VPN you can access any one of the machines in any data center
> through a simple IP address, thanks to your VPN.
> 
>
> 
> Second Scenario:
> You are the same admin at the same XYZ corp. An electrician pulls the
> breaker in the data center and your systems go down (This actually happend
> to one installation I worked on). A couple of the admins in charge of some
> of the boxes are on vacation and "accidentally" forgot to bring their cell
> phones.
> 
> A few of the systems didn't come up correctly. You need to find the
> correct databases. Unfortunately there are more database cluster
> directories than there should be, and the admin hadn't yet documented
> which was which. You don't even know how to test if they are.
> 
> Your site is down, you are very stressed, you are cursing the guy that
> didn't write this stuff down. Since there is no facility to bring up
> multiple PG databases, there is no standard to follow.
> 
> Wouldn't it be nice, to be able to do "pg_ctl startall?" Or better yet,
> just have this in the startup?

Absolutely -

In keeping with the idea of providing a mechanism for such:

I'm wondering if adding an options to  "pg_ctl register" to register 
clusters with a global registry and adding another option to pg_ctl for 
start|stop all.

e.g:

$ pg_ctl register [-R [filename]]  [-N servicename] [-U username] [-P 
password] [-D datadir] [-w] [-o options]
$ pg_ctl [-A] [-R [filename]] start|stop

The idea being:

1/ Maintaining a global cluster registry is optional.
2/ The choice of file location is optional (guess a sensible default is 
needed).
3/ Start and stop can specify all clusters (clearly some careful 
programming is needed to exec the right binaries in the case of multiple 
versions!).

This does not solve the issue of a registry file not being up to date, 
but provides a simple means to *keep* it up to date - just search for 
clusters that are not in the registry file and add 'em with 'pg_ctl 
register -R'.

Cheers

Mark

On Wed, Feb 22, 2006 at 09:22:14AM -0500, Mark Woodward wrote:
> That's not the issue.
> I find it frustrating sometimes because when I describe one scenario,
> people debate it using other scenarios. Maybe I lack the communications
> skills to convey the problem accurately.

<snip>

> Now, if there were a standard file from which you could "see" what
> databases are installed and running on this system, you could write a
> shell script:

If you're talking about standards perhaps you should consider how
Debian does it. All configuration is stored in

/etc/postgresql/<version>/<clustername>/

It provides wrapper scripts to run pg_ctl (pg_ctlcluster) on any
particular cluster which can be run by either the system user owning
the db, or root.

Within those files is all the information required to find the cluster
(base directory, etc).

I think the default config is to start all clusters on bootup. The main
downside of this system is that some sysadmin pretty much needs to
create the clusters for everyone. Kinda logical given the location of
the configuration.

I doubt something like this would ever make it into the standard
distribution though. What you're asking is more a distributer issue
than an issue for postgres.

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.

Hi Mark, hi Martijn!

Martijn van Oosterhout [2006-02-23 12:10 +0100]:
> If you're talking about standards perhaps you should consider how
> Debian does it. All configuration is stored in
>
> /etc/postgresql/<version>/<clustername>/
>
> It provides wrapper scripts to run pg_ctl (pg_ctlcluster) on any
> particular cluster which can be run by either the system user owning
> the db, or root.
>
> Within those files is all the information required to find the cluster
> (base directory, etc).

Right. But although this fits well for Debian, this is not necessarily
the case for other Linux distributions, let alone non-Linux systems.
Even less when using the upstream tarball (where all configuration is
usually kept in the data directory itself).

However, Debian has a tool 'pg_lsclusters' which comes pretty close to
what Mark wants, AFAICS:

$ pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
7.4     main      5432 online postgres /var/lib/postgresql/7.4/main       /var/log/postgresql/postgresql-7.4-main.log
8.1     main      5434 down   postgres /var/lib/postgresql/8.1/main       /var/log/postgresql/postgresql-8.1-main.log
8.1     test      5433 online martin   /home/martin/psql                  /var/log/postgresql/postgresql-8.1-test.log

So, even in the current Debian system we don't have a fixed location
for the data directories, log files, etc. There are defaults, but they
can be customized, for good reasons (like keeping my test cluster in
my home directory, and so on).

> I think the default config is to start all clusters on bootup.

Right; this is customizable in a file start.conf
(auto|manual|disabled) in the cluster configuration directory.

> The main downside of this system is that some sysadmin pretty much
> needs to create the clusters for everyone.

What do you mean in particular? The packages install a default cluster
(e. g. postgresql-8.1 creates a cluster 8.1/main), more clusters can
be created with pg_createcluster.

> I doubt something like this would ever make it into the standard
> distribution though. What you're asking is more a distributer issue
> than an issue for postgres.

I agree. Also, FYI, a while ago I proposed some patches which allow
several major versions to be installed in parallel, but they were
deemed too specific for upstream inclusion (which is fine; it's the
distributor's task to integrate an application into a distribution's
file layout/configuration handling/other quirks).

Thanks,

Martin
--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

In a world without walls and fences, who needs Windows and Gates?

On Thu, Feb 23, 2006 at 12:42:52PM +0100, Martin Pitt wrote:
> > The main downside of this system is that some sysadmin pretty much
> > needs to create the clusters for everyone.
>
> What do you mean in particular? The packages install a default cluster
> (e. g. postgresql-8.1 creates a cluster 8.1/main), more clusters can
> be created with pg_createcluster.

What I mean is that only root can run pg_createcluster (either via
package installation or directly). At least, that's what my reading of
the code tells me. Uless you have an pg_adoptcluster somewhere :)

> I agree. Also, FYI, a while ago I proposed some patches which allow
> several major versions to be installed in parallel, but they were
> deemed too specific for upstream inclusion (which is fine; it's the
> distributor's task to integrate an application into a distribution's
> file layout/configuration handling/other quirks).

I really like the way Debian does it, you do a good job. Have you
considered autogenerating entries for pg_service.conf?

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.

Hi Martijn!

Martijn van Oosterhout [2006-02-23 13:33 +0100]:
> What I mean is that only root can run pg_createcluster (either via
> package installation or directly). At least, that's what my reading of
> the code tells me. Uless you have an pg_adoptcluster somewhere :)

Ah, right, now I know what you mean. This is necessary since non-root
users can't (or rather shouldn't) write into /etc. If there is a
desire for it, we can certainly discuss a way to manage clusters
entirely as an user (with some missing features like autostart at
boot, of course). But that should happen in the Debian BTS, not here.

> I really like the way Debian does it, you do a good job.

Thanks :)

> Have you considered autogenerating entries for pg_service.conf?

Not yet. TBH I didn't know about the pg_service.conf feature until
just recently (in Debian you can select a cluster with --cluster
version/name, that worked pretty well so far). Sounds interesting,
though. :) (Again, let's discuss that in the Debian BTS).

Thanks for the suggestions,

Martin
--
Martin Pitt        http://www.piware.de
Ubuntu Developer   http://www.ubuntu.com
Debian Developer   http://www.debian.org

In a world without walls and fences, who needs Windows and Gates?

Mark Woodward wrote:
> > If you require a policy, then YOU are free to choose the policy that
> > YOU need.  You're not forced to accept other peoples' policies that
> > may conflict with things in your environment.
> 
> The problem is that there is no mechanism through which one can implement
> policy. You are left to "roll your own" each and every time. A mechanism
> provided, but not enforced, by postgresql would go a LONG way toward
> enabling a coherent policy.

Unless you can have +80% of sites using the default, it isn't worth it
and is more confusing than if you had never created it at all.  What is
wrong with defining an environment variable in /etc/profile?

--  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. +

> Mark Woodward wrote:
>> > If you require a policy, then YOU are free to choose the policy that
>> > YOU need.  You're not forced to accept other peoples' policies that
>> > may conflict with things in your environment.
>>
>> The problem is that there is no mechanism through which one can
>> implement
>> policy. You are left to "roll your own" each and every time. A mechanism
>> provided, but not enforced, by postgresql would go a LONG way toward
>> enabling a coherent policy.
>
> Unless you can have +80% of sites using the default, it isn't worth it
> and is more confusing than if you had never created it at all.  What is
> wrong with defining an environment variable in /etc/profile?
>
It isn't just "an" environment variable, it is a number of variables and a
mechanism. Besides, "profile," from an admin's perspective, is for
managing users, not databases.

OK, think of this, this is an actual site:

I have three PostgreSQL database clusters on one server. A general purpose
web service cluster that has all the web databases in it. I have a
geographical database that has a U.S. street map database. I have a third
which has a large music database on it.

The geo and the music database are rebuilt periodically and tested off
line. They are built and indexed, then tested at the office, and the
physical cluster is uploaded to the server, where the specific postmaster
processes are stopped, swapped, and restarted.

Now, the pg_service.conf, is a HUGE plus for our process. When I work that
into the coding spec, it makes testing the offline code easier because we
no longer have to reconcile connection differences between lab and colo.

Now, we have an environment that has multiple database clusters and server
processes on one machine. How do you manage them? PostgreSQL has no
facility to make this easier.

Similar to pg_service.conf, I am suggesting (the concept has evolved with
discussion) a pg_clusters.conf (name not important) that performs a
similar job as pg_services, but is used to bring up multiple postmaster
processes on one box. Currently, there is no standard way to manage this.

PostgreSQL will continue to perform as it currently does, but a PostgreSQL
"blessed" methodology of managing multiple clusters can be added to it.
Individual processes can still be started and stop independently. Database
clusters that are not in the pg_clusters file can still be created and
started.

I think Chris said it right, I don't want to make policy, I would to
provide functionality. I know my service environment is not unique, and so
what if it is only about 10% (or less) of the PostgreSQL users? There is a
need for this, and it is a valuable "enterprise" level feature. DB admins
will recognize and use this feature. It makes a lot of sense if you stand
back and think of the admin process instead of the core database.

Here's the jist of what I see:


pg_clusters.conf
>>>>>>>>
[GEO]
DPATH=/vol01/pg_geo
PORT=5434

[ICDMDB]
DPATH=/vol01/pg_icdmdb
PORT=5433

[GENERAL]
DPATH=/vol02/pg_users
PORT=5432
<<<<<<<<<

Now, we should be able to modify pg_ctl to do something like this:

pg_ctl -C GEO start
pg_ctl -C ICDMDB start

or

pg_ctl startall

On Mon, Feb 27, 2006 at 09:39:59AM -0500, Mark Woodward wrote:
> It isn't just "an" environment variable, it is a number of variables and a
> mechanism. Besides, "profile," from an admin's perspective, is for
> managing users, not databases.

Sure, you need to control the user, group, placement of logfile and
several other things.

<snip>

> I think Chris said it right, I don't want to make policy, I would to
> provide functionality. I know my service environment is not unique, and so
> what if it is only about 10% (or less) of the PostgreSQL users? There is a
> need for this, and it is a valuable "enterprise" level feature. DB admins
> will recognize and use this feature. It makes a lot of sense if you stand
> back and think of the admin process instead of the core database.

How is any of this different from the way Debian handles multiple
simultaneous clusters? Is there any particular reason you couldn't use
it or a variation thereof (other than that it enforces a particular
policy, namely debian's)? The source is available [1] and a quick
demonstration was posted [2].

In any case, nothing stops anyone from starting a project on
pgfoundary. Nothing convinces people quite like working code. Since
-core seems uninterested, I think this would be the best way to go.

Have a nice day,

[1] http://ftp.debian.org/debian/pool/main/p/postgresql-common/postgresql-common_43.tar.gz
[2] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00942.php
--
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.

> On Mon, Feb 27, 2006 at 09:39:59AM -0500, Mark Woodward wrote:
>> It isn't just "an" environment variable, it is a number of variables and
>> a
>> mechanism. Besides, "profile," from an admin's perspective, is for
>> managing users, not databases.
>
> Sure, you need to control the user, group, placement of logfile and
> several other things.
>
> <snip>
>
>> I think Chris said it right, I don't want to make policy, I would to
>> provide functionality. I know my service environment is not unique, and
>> so
>> what if it is only about 10% (or less) of the PostgreSQL users? There is
>> a
>> need for this, and it is a valuable "enterprise" level feature. DB
>> admins
>> will recognize and use this feature. It makes a lot of sense if you
>> stand
>> back and think of the admin process instead of the core database.
>
> How is any of this different from the way Debian handles multiple
> simultaneous clusters? Is there any particular reason you couldn't use
> it or a variation thereof (other than that it enforces a particular
> policy, namely debian's)? The source is available [1] and a quick
> demonstration was posted [2].

Well, I'm sure that one "could" use debian's solution, but that's the
problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the
mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL
admin manual?

We are talking about a feature, like pg_service.conf, now that people
notice it, we are saying "WOW, this is the API we should push." This is a
functionality, IMHO, must be the responsibility of PostgreSQL.

>
> In any case, nothing stops anyone from starting a project on
> pgfoundary. Nothing convinces people quite like working code. Since
> -core seems uninterested, I think this would be the best way to go.

Argg, the pgfoundary is sort of the "free speech zones" that the U.S. sets
up out of view of the president and the press. Yea, its there, and if you
go out of your way, you can find it. Think of Arthur Dent's "The plans
were on display!"

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Mark Woodward
> Sent: 27 February 2006 16:49
> To: Martijn van Oosterhout
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] pg_config, pg_service.conf,
> postgresql.conf ....
>
> Think of Arthur Dent's "The plans were on display!"

There are no leopards on pgFoundry.

Regards, Dave

On Mon, Feb 27, 2006 at 11:48:50AM -0500, Mark Woodward wrote:
> Well, I'm sure that one "could" use debian's solution, but that's the
> problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide the
> mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL
> admin manual?

I meant that it's a good start. It's a fully functional solution (for
its intended audience) that works now and thus might give you ideas how
you want your solution to work.

> Argg, the pgfoundary is sort of the "free speech zones" that the U.S. sets
> up out of view of the president and the press. Yea, its there, and if you
> go out of your way, you can find it. Think of Arthur Dent's "The plans
> were on display!"

My point is only that since trying to convince people on -hackers to
write the code isn't working, perhaps someone (you?) could write it
seperately for possible inclusion later. If someone writes it all
themselves then they can send a patch. OTOH if several people want to
collaborate on a solution, something like pgfoundary is useful.

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,

> Well, I'm sure that one "could" use debian's solution, but that's the
> problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide
> the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the
> PostgreSQL admin manual?
>
> We are talking about a feature, like pg_service.conf, now that people
> notice it, we are saying "WOW, this is the API we should push." This is
> a functionality, IMHO, must be the responsibility of PostgreSQL.

Then stop talking about it and write a patch.  

So far, you've failed to convince anyone else on this list that the 
functionality you suggest is actually useful for anyone other that you, 
personally.  The only way you're going to do so is to put up some code 
somewhere other people can use it and prove that it's useful.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

> On Mon, Feb 27, 2006 at 11:48:50AM -0500, Mark Woodward wrote:
>> Well, I'm sure that one "could" use debian's solution, but that's the
>> problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide
>> the
>> mechanisms? Will debian support FreeBSD? NetBSD? Is it in the PostgreSQL
>> admin manual?
>
> I meant that it's a good start. It's a fully functional solution (for
> its intended audience) that works now and thus might give you ideas how
> you want your solution to work.

I have a number of ideas as to how it would work and debian is certainly
another reference point.
>
>> Argg, the pgfoundary is sort of the "free speech zones" that the U.S.
>> sets
>> up out of view of the president and the press. Yea, its there, and if
>> you
>> go out of your way, you can find it. Think of Arthur Dent's "The plans
>> were on display!"
>
> My point is only that since trying to convince people on -hackers to
> write the code isn't working, perhaps someone (you?) could write it
> seperately for possible inclusion later. If someone writes it all
> themselves then they can send a patch. OTOH if several people want to
> collaborate on a solution, something like pgfoundary is useful.
>

Well, I said, at the top post, that I would write it, I'm not trying to
convince anyone to to work on it. If others would like to help, that would
certainly be OK. I'm trying to propose a feature, iron out how it should
work, and get feedback before I implement it.

> Mark,
>
>> Well, I'm sure that one "could" use debian's solution, but that's the
>> problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide
>> the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the
>> PostgreSQL admin manual?
>>
>> We are talking about a feature, like pg_service.conf, now that people
>> notice it, we are saying "WOW, this is the API we should push." This is
>> a functionality, IMHO, must be the responsibility of PostgreSQL.
>
> Then stop talking about it and write a patch.
>
> So far, you've failed to convince anyone else on this list that the
> functionality you suggest is actually useful for anyone other that you,
> personally.  The only way you're going to do so is to put up some code
> somewhere other people can use it and prove that it's useful.

Maybe I'm too used to working in engineering groups. I am trying to get
input for a project. Trying to iron out what the feature set should be and
the objectives that should be attained. BEFORE I start coding.

Just saying "submit a patch" is the antithesis to good engineering, it
works for hacking, but if I am going to develop a feature, I wish to do it
right and have it appeal to the broadest possible audience, collect as
much input about the needs of users, etc.

The feature set I am suggesting is, as been pointed out, similar to other
projects happening outside of PostgreSQL. The debian project for instance.
To say I am the only one that needs this, is of course, not true.

My frustration level often kills any desire to contribute to open source.
Sometimes, I think that open source is doomed. The various projects I
track and use are very frustrating, they remind me of dysfunctional
engineering departments in huge companies, it is very hard to positively
discuss any new ideas. The first response is always some variation on
"no."

Maybe it is that the whiteboard engineering discussion process doesn't
translate well to this medium.

> Maybe I'm too used to working in engineering groups. I am trying to get
> input for a project. Trying to iron out what the feature set should be and
> the objectives that should be attained. BEFORE I start coding.
>
>   
Well that is always a good idea but:

> Just saying "submit a patch" is the antithesis to good engineering, it
> works for hacking, but if I am going to develop a feature, I wish to do it
> right and have it appeal to the broadest possible audience, collect as
> much input about the needs of users, etc.
>   
The problem you are having is that sense many people do not see a 
benefit it is hard
to garner the feedback, thus the fallback to submit a patch.

If you submit a patch there is a chance that people will see the benefit 
within a simple
implementation and THEN you get the feedback you want.

Sincerely,

Joshua D. Drake

"Mark Woodward" <pgsql@mohawksoft.com> writes:
> My frustration level often kills any desire to contribute to open source.
> Sometimes, I think that open source is doomed. The various projects I
> track and use are very frustrating, they remind me of dysfunctional
> engineering departments in huge companies, it is very hard to positively
> discuss any new ideas. The first response is always some variation on
> "no."

Well, at least for PG the process has to be biased towards "no", because
we have to keep the code reliable and maintainable.  If we bias in the
direction of throwing in every little feature someone thinks up, we'll
soon have a buggy, incomprehensible mess.

FWIW, the proposal as it seems to have evolved (config file separate
from pg_service and known only to pg_ctl) doesn't seem too unreasonable
to me.  I might have some use for it personally, if the implementation
is capable of launching back-version postmasters as well as
current-version.
        regards, tom lane

On Mon, Feb 27, 2006 at 03:38:23PM -0500, Mark Woodward wrote:
> Maybe I'm too used to working in engineering groups. I am trying to get
> input for a project. Trying to iron out what the feature set should be and
> the objectives that should be attained. BEFORE I start coding.

Well yes, the problem is that what's been suggested so far doesn't
provide much to give feedback on. It needs to be much more worked out.

> Just saying "submit a patch" is the antithesis to good engineering, it
> works for hacking, but if I am going to develop a feature, I wish to do it
> right and have it appeal to the broadest possible audience, collect as
> much input about the needs of users, etc.

That works, but only as long as it's something a lot of people care
about. This isn't, so until you (or somebody) comes up with a fairly
complete proposal as to how it should interact with the rest of the
system, it's hard to get/give feedback. Sorry, that's the way it works
sometimes.

> Maybe it is that the whiteboard engineering discussion process doesn't
> translate well to this medium.

Yep. the turnaround time is so high and the amount of communication so
low that you pretty much have to submit huge chunks at a time to get
any meaningful work done. The quick turnaround you get on a whiteboard
simply doesn't exist.

Don't take it personally. One effect of this system is the "first-mover
advantage". The first person to implement gets the biggest say in the
final result.

Have a ncie 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,

> My frustration level often kills any desire to contribute to open
> source. Sometimes, I think that open source is doomed. The various
> projects I track and use are very frustrating, they remind me of
> dysfunctional engineering departments in huge companies, it is very hard
> to positively discuss any new ideas. The first response is always some
> variation on "no."

Well, if you weren't a regular I'd be more encouraging.  But you already 
know how things work here, so we can give you a hard time.    I'll point 
out the year-long argument over the newsysviews for the contributors, the 
two-year long process for 2PC, etc.  

Sometimes you can just throw stuff up on this forum and people will say 
"wow, cool, I could really use something that does that." and you're off 
and flying.  Other times, though, it's hard for people on this list to see 
how a vague proposal would be useful, so you need to have a prototype that 
*shows* how useful it is.  

As a parallel, I personally didn't see the utility of DTrace until I saw 
Brian Cantrell give a demo of the full feature set.  If you just read the 
spec, it's hard to see how it's an improvement over oprofile.

> Maybe it is that the whiteboard engineering discussion process doesn't
> translate well to this medium.

Nope.  

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Mark Woodward wrote:
> > Mark,
> >
> >> Well, I'm sure that one "could" use debian's solution, but that's the
> >> problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide
> >> the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the
> >> PostgreSQL admin manual?
> >>
> >> We are talking about a feature, like pg_service.conf, now that people
> >> notice it, we are saying "WOW, this is the API we should push." This is
> >> a functionality, IMHO, must be the responsibility of PostgreSQL.
> >
> > Then stop talking about it and write a patch.
> >
> > So far, you've failed to convince anyone else on this list that the
> > functionality you suggest is actually useful for anyone other that you,
> > personally.  The only way you're going to do so is to put up some code
> > somewhere other people can use it and prove that it's useful.
> 
> Maybe I'm too used to working in engineering groups. I am trying to get
> input for a project. Trying to iron out what the feature set should be and
> the objectives that should be attained. BEFORE I start coding.
> 
> Just saying "submit a patch" is the antithesis to good engineering, it
> works for hacking, but if I am going to develop a feature, I wish to do it
> right and have it appeal to the broadest possible audience, collect as
> much input about the needs of users, etc.

You are 100% right here.  Talking about it first is usually the best
policy.

One question I have is how this feature would be an improvement over
just pointing pg_ctl at a postgresql.conf configuration file.  That
config file has the ability to specify most if not all server
parameters.

--  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. +

> "Mark Woodward" <pgsql@mohawksoft.com> writes:
>> My frustration level often kills any desire to contribute to open
>> source.
>> Sometimes, I think that open source is doomed. The various projects I
>> track and use are very frustrating, they remind me of dysfunctional
>> engineering departments in huge companies, it is very hard to positively
>> discuss any new ideas. The first response is always some variation on
>> "no."
>
> Well, at least for PG the process has to be biased towards "no", because
> we have to keep the code reliable and maintainable.  If we bias in the
> direction of throwing in every little feature someone thinks up, we'll
> soon have a buggy, incomprehensible mess.

I would submit that there is an intermediate state, and perhaps the medium
is too binary, where someone says "Lets send a man to Jupiter, here's why"
Before dismissing it out of hand, one tries to understand the reasons why,
and sugest how to get there or alternate destinations. Not just say, I
don't want to go to jupiter.


>
> FWIW, the proposal as it seems to have evolved (config file separate
> from pg_service and known only to pg_ctl) doesn't seem too unreasonable
> to me.  I might have some use for it personally, if the implementation
> is capable of launching back-version postmasters as well as
> current-version.

This is what I'm talking about, this was constructive, and while I
wouldn't have thought of it, I think having something like
"POSTMASTER=/usr/local/pg7.4/bin/postmaster," while not something I would
personally use, may apply to other users.

> Mark Woodward wrote:
>> > Mark,
>> >
>> >> Well, I'm sure that one "could" use debian's solution, but that's the
>> >> problem, it isn't PostgreSQL's solution. Shouldn't PostgreSQL provide
>> >> the mechanisms? Will debian support FreeBSD? NetBSD? Is it in the
>> >> PostgreSQL admin manual?
>> >>
>> >> We are talking about a feature, like pg_service.conf, now that people
>> >> notice it, we are saying "WOW, this is the API we should push." This
>> is
>> >> a functionality, IMHO, must be the responsibility of PostgreSQL.
>> >
>> > Then stop talking about it and write a patch.
>> >
>> > So far, you've failed to convince anyone else on this list that the
>> > functionality you suggest is actually useful for anyone other that
>> you,
>> > personally.  The only way you're going to do so is to put up some code
>> > somewhere other people can use it and prove that it's useful.
>>
>> Maybe I'm too used to working in engineering groups. I am trying to get
>> input for a project. Trying to iron out what the feature set should be
>> and
>> the objectives that should be attained. BEFORE I start coding.
>>
>> Just saying "submit a patch" is the antithesis to good engineering, it
>> works for hacking, but if I am going to develop a feature, I wish to do
>> it
>> right and have it appeal to the broadest possible audience, collect as
>> much input about the needs of users, etc.
>
> You are 100% right here.  Talking about it first is usually the best
> policy.

Thanks!
>
> One question I have is how this feature would be an improvement over
> just pointing pg_ctl at a postgresql.conf configuration file.  That
> config file has the ability to specify most if not all server
> parameters.

Like I have repeated a number of times, sometimes, there is more than one
database cluster on a machine. The proposed pg_clusters.conf, could look
like this:

pg_clusters.conf>>>>
[GEO]
DATADIR=/vol01/pggeo
PORT=5435

[ICDMDB]
DATADIR=/vol01/pgicdmdb
PORT=5434

[TOMLANE]
DATADIR=/vol03/pg74
PORT=5433
POSTMASTER=/usr/local/pg7.4.1/bin/postmaster

[POSTMASTER]
DATADIR=/vol02/pg90
PORT=5432

# Virtual target starts all?
[ALL]
DB0=GEO
DB1=ICDMDB
DB2=TOMLANE

<<<<<<<<<

pg_ctl start
(Finds and starts the "POSTMASTER" entry)

pg_ctl -S ICDMDB start
(Starts the ICDMDB cluster)

pg_ctl startall
or
pg_ctl -S [*|all] start
or
pg_ctl startall


Or maybe even "start" will start a virtual target "ALL"

I don't see how this is much better than just pointing to different
configuration file for each postmaster.

---------------------------------------------------------------------------

Mark Woodward wrote:
> > One question I have is how this feature would be an improvement over
> > just pointing pg_ctl at a postgresql.conf configuration file.  That
> > config file has the ability to specify most if not all server
> > parameters.
> 
> Like I have repeated a number of times, sometimes, there is more than one
> database cluster on a machine. The proposed pg_clusters.conf, could look
> like this:
> 
> pg_clusters.conf>>>>
> [GEO]
> DATADIR=/vol01/pggeo
> PORT=5435
> 
> [ICDMDB]
> DATADIR=/vol01/pgicdmdb
> PORT=5434
> 
> [TOMLANE]
> DATADIR=/vol03/pg74
> PORT=5433
> POSTMASTER=/usr/local/pg7.4.1/bin/postmaster
> 
> [POSTMASTER]
> DATADIR=/vol02/pg90
> PORT=5432
> 
> # Virtual target starts all?
> [ALL]
> DB0=GEO
> DB1=ICDMDB
> DB2=TOMLANE
> 
> <<<<<<<<<
> 
> pg_ctl start
> (Finds and starts the "POSTMASTER" entry)
> 
> pg_ctl -S ICDMDB start
> (Starts the ICDMDB cluster)
> 
> pg_ctl startall
> or
> pg_ctl -S [*|all] start
> or
> pg_ctl startall
> 
> 
> Or maybe even "start" will start a virtual target "ALL"
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 

--  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. +

Mark Woodward wrote:

> [TOMLANE]
> DATADIR=/vol03/pg74
> PORT=5433
> POSTMASTER=/usr/local/pg7.4.1/bin/postmaster

Seems better to me to specify PREFIX (the --prefix arg to configure)
instead of POSTMASTER, because then you can search any needed executable
there (pg_config for example).  Or maybe use it to set PATH,
LD_LIBRARY_PATH, etc.  This PREFIX would be possibly superseded for
particular variables with EXEC_PREFIX or the other --*dir args to
configure.

OTOH it would also be nice to have the program have a mode to emit shell
variable declarations.  I have a nice little shell script that does that
(among many other things):

$ runpg 00orig  
export
PATH=/pgsql/install/00orig/bin:/home/alvherre/bin:/sbin:/usr/sbin:/usr/local/sbin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games
export PGDATA=/pgsql/install/00orig/data
export PGPORT=55432
export PGLIBS=/pgsql/install/00orig/lib
export PGINCLUDE=/pgsql/install/00orig/include

So I can run it like so:
`runpg 00orig`

And it will define those variables for the current shell.  (00orig is
what I call my unmodified CVS HEAD tree).

My script doesn't depend on a config file though -- it knows what
"installations" exist by poking the contents of a predefined directory.
But it's capable of following symlinks, so there's no need for a config
file really; just make a symlink pointing to the basedir into the
"runpg root dir" and voilà.

(Throwing out ideas in case something is useful ... I can share the
script if there's any interest.)

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

After takin a swig o' Arrakan spice grog, pgsql@mohawksoft.com ("Mark Woodward") belched out:
>> Mark Woodward wrote:
> Like I have repeated a number of times, sometimes, there is more than one
> database cluster on a machine. The proposed pg_clusters.conf, could look
> like this:
>
> pg_clusters.conf>>>>
> [GEO]
> DATADIR=/vol01/pggeo
> PORT=5435
>
> [ICDMDB]
> DATADIR=/vol01/pgicdmdb
> PORT=5434
>
> [TOMLANE]
> DATADIR=/vol03/pg74
> PORT=5433
> POSTMASTER=/usr/local/pg7.4.1/bin/postmaster
>
> [POSTMASTER]
> DATADIR=/vol02/pg90
> PORT=5432
>
> # Virtual target starts all?
> [ALL]
> DB0=GEO
> DB1=ICDMDB
> DB2=TOMLANE
>
> <<<<<<<<<
>
> pg_ctl start
> (Finds and starts the "POSTMASTER" entry)
>
> pg_ctl -S ICDMDB start
> (Starts the ICDMDB cluster)
>
> pg_ctl startall
> or
> pg_ctl -S [*|all] start
> or
> pg_ctl startall
>
>
> Or maybe even "start" will start a virtual target "ALL"

I can point at three things there that are distinctly wrong.

- PORT should *never* be specified in that file, because it is already specified in each respective postgresql.conf
file.

- POSTMASTER is the wrong thing to point to; you should be pointing to a path, instead, and it must ALWAYS be
specified.

- You provide no indication of where log files are to be stowed. In version 8, there are options for that to be
specifiedin the postgresql.conf file, but not so, for earlier versions...
 

I'm not keen on the Windows .ini file style sectioning; that makes it
look like a mix between a shell script and something else.  It should
be one or the other.  It probably should be directly executable by
something...

But having some central "registry" that consists of data directories
and binary directories (and possibly log directories) seems reasonably
elegant.
-- 
output = ("cbbrowne" "@" "ntlug.org")
http://linuxdatabases.info/info/postgresql.html
"war is an inappropriate analogy; ``flame war'' is a misnomer.in any usenet exchange, the only casualty is time.there
arebetter uses for regret."   --thi <ttn@netcom.com>
 

> After takin a swig o' Arrakan spice grog, pgsql@mohawksoft.com ("Mark
> Woodward") belched out:
>>> Mark Woodward wrote:
>> Like I have repeated a number of times, sometimes, there is more than
>> one
>> database cluster on a machine. The proposed pg_clusters.conf, could look
>> like this:
>>
>> pg_clusters.conf>>>>
>> [GEO]
>> DATADIR=/vol01/pggeo
>> PORT=5435
>>
>> [ICDMDB]
>> DATADIR=/vol01/pgicdmdb
>> PORT=5434
>>
>> [TOMLANE]
>> DATADIR=/vol03/pg74
>> PORT=5433
>> POSTMASTER=/usr/local/pg7.4.1/bin/postmaster
>>
>> [POSTMASTER]
>> DATADIR=/vol02/pg90
>> PORT=5432
>>
>> # Virtual target starts all?
>> [ALL]
>> DB0=GEO
>> DB1=ICDMDB
>> DB2=TOMLANE
>>
>> <<<<<<<<<
>>
>> pg_ctl start
>> (Finds and starts the "POSTMASTER" entry)
>>
>> pg_ctl -S ICDMDB start
>> (Starts the ICDMDB cluster)
>>
>> pg_ctl startall
>> or
>> pg_ctl -S [*|all] start
>> or
>> pg_ctl startall
>>
>>
>> Or maybe even "start" will start a virtual target "ALL"
>
> I can point at three things there that are distinctly wrong.
>
> - PORT should *never* be specified in that file, because it is already
>   specified in each respective postgresql.conf file.

If one can specify a different port than the default on the command line,
why wouldn't a file designed to describe the server process include it. My
intention is to include all the options available via environment or
command lon in the file.

>
> - POSTMASTER is the wrong thing to point to; you should be pointing to
>   a path, instead, and it must ALWAYS be specified.

I'm not sure I agree. I have been intending to let the system to use the
defaults when alternatves are not specified. The POSTMASTER entry was for
Tom Lane's suggestion that alternate postgresql versions be used.

I am open to a better cleaner way to accomplish Tom's request.

>
> - You provide no indication of where log files are to be stowed.
>   In version 8, there are options for that to be specified in the
>   postgresql.conf file, but not so, for earlier versions...

OK, I guess that comes along with all the command line or environment
variables.

>
> I'm not keen on the Windows .ini file style sectioning; that makes it
> look like a mix between a shell script and something else.  It should
> be one or the other.  It probably should be directly executable by
> something...

Hmm, while I agree that "ini" file is kind of ugly, it is fairly common in
the industry, human readable, and does what it needed.

>
> But having some central "registry" that consists of data directories
> and binary directories (and possibly log directories) seems reasonably
> elegant.

Oh, man, don't even get me started about registries and bottomless
directories. Let's just say I respectfully disagree :-)


How's this:

[SERVERNAME]
DATADIR=path_to_data
LOGFILE=path_to_log
OPTIONS=....
PORT=nnn
MAX-CONNECT=nnn
DEBUG=nnn
FSYNC=[on|off]
NAME=VALUE
PGBIN=path_to_pg_installation

"Mark Woodward" <pgsql@mohawksoft.com> writes:
> If one can specify a different port than the default on the command line,
> why wouldn't a file designed to describe the server process include it. My
> intention is to include all the options available via environment or
> command lon in the file.

I think that's a really bad goal.  In the first place, we generally
discourage people from setting options on the postmaster command line
if they could be set in postgresql.conf, because if you set them on
the command line they can't be overridden later via editing the conf
file and SIGHUP.  (Of course that doesn't matter for startup-time-only
options, but the fact remains that this isn't and shouldn't be the
preferred way to set options.)  In the second place, the set of options
changes from version to version, so you'd be creating a large make-work
project for yourself trying to keep the config file parser in sync with
the available options.  I think it's sufficient if you provide a single
config file entry which is extra command line options, eg,
SWITCHES="--port=5444 -i -F"

ISTM that postmaster pathname, data directory, and extra-switches are all
you really need.  (Breaking out data directory is useful because pg_ctl
has its own reasons for wanting to know that, but it does not care about
the other switches.)
        regards, tom lane

Mark Woodward wrote:
>>After takin a swig o' Arrakan spice grog, pgsql@mohawksoft.com ("Mark
>>Woodward") belched out:

>>I'm not keen on the Windows .ini file style sectioning; that makes it
>>look like a mix between a shell script and something else.  It should
>>be one or the other.  It probably should be directly executable by
>>something...
> 
> 
> Hmm, while I agree that "ini" file is kind of ugly, it is fairly common in
> the industry, human readable, and does what it needed.
> 
> 

You could follow the UNIX style for such files e.g. /etc/hosts, 
/etc/services (and also pg_hba.conf).

cheers

Mark

> Mark Woodward wrote:
>>>After takin a swig o' Arrakan spice grog, pgsql@mohawksoft.com ("Mark
>>>Woodward") belched out:
>
>>>I'm not keen on the Windows .ini file style sectioning; that makes it
>>>look like a mix between a shell script and something else.  It should
>>>be one or the other.  It probably should be directly executable by
>>>something...
>>
>>
>> Hmm, while I agree that "ini" file is kind of ugly, it is fairly common
>> in
>> the industry, human readable, and does what it needed.
>>
>>
>
> You could follow the UNIX style for such files e.g. /etc/hosts,
> /etc/services (and also pg_hba.conf).
The /etc/hosts file does not allow a hierarchical representation of a
group that contains zero or more name/value pairs.

Mark Woodward wrote:
>>Mark Woodward wrote:
>>
>>>>After takin a swig o' Arrakan spice grog, pgsql@mohawksoft.com ("Mark
>>>>Woodward") belched out:
>>
>>>>I'm not keen on the Windows .ini file style sectioning; that makes it
>>>>look like a mix between a shell script and something else.  It should
>>>>be one or the other.  It probably should be directly executable by
>>>>something...
>>>
>>>
>>>Hmm, while I agree that "ini" file is kind of ugly, it is fairly common
>>>in
>>>the industry, human readable, and does what it needed.
>>>
>>>
>>
>>You could follow the UNIX style for such files e.g. /etc/hosts,
>>/etc/services (and also pg_hba.conf).
> 
> The /etc/hosts file does not allow a hierarchical representation of a
> group that contains zero or more name/value pairs.
> 

Do you need name, value pairs? I was thinking that something like:

# Postgres Cluster Registration
#
# PG_HOME PGDATA PORT
/usr/local/pg7.4.1   /vol01/pggeo      5435
/usr/local/pg7.4.1   /vol01/pgicdmdb   5434
/usr/local/pg7.4.1   /vol03/pg74       5432


Clearly other fields are possible (like ALIAS for the names you were 
using, and OPTS for extra arguments).

This sort of layout is easily readable (more easily readable for those 
of us used to standard UNIX config files) and simply parsable too.

Cheers

M

Mark Kirkwood wrote:

>>
>>
>
> Do you need name, value pairs? I was thinking that something like:
>
> # Postgres Cluster Registration
> #
> # PG_HOME PGDATA PORT
> /usr/local/pg7.4.1   /vol01/pggeo      5435
> /usr/local/pg7.4.1   /vol01/pgicdmdb   5434
> /usr/local/pg7.4.1   /vol03/pg74       5432
>
>
> Clearly other fields are possible (like ALIAS for the names you were 
> using, and OPTS for extra arguments).
>
> This sort of layout is easily readable (more easily readable for those 
> of us used to standard UNIX config files) and simply parsable too.
>
>

I am a Unix guy through and through, but its config files have pained me 
many times over the years. Not least because of lack of consistency.

This sort of layout fails miserably if there are optional fields. Look 
at the handsprings we had to turn to put CIDR addresses into 
pg_hba.conf. And not without debate.

I don't much like ini style configs either.

These days, for Perl apps I generally make the config file a perl hash, 
which can be as deeply structured as you like. The great advantage is 
that you get parsing for free. For other apps I'm mildly inclined to 
YAML or XML configs. All of these might be verbose, but they have 2 huge 
advantages: they can adapt to structure, and they are somewhat 
self-documenting. I am currently wrestling with an app that does 
horrible things because its config needs to be tree structured and is 
instead flat  (and also utterly unreadable).

cheers

andrew

Andrew Dunstan wrote:
> 
> 
> Mark Kirkwood wrote:
> 
>>>
>>>
>>
>> Do you need name, value pairs? I was thinking that something like:
>>
>> # Postgres Cluster Registration
>> #
>> # PG_HOME PGDATA PORT
>> /usr/local/pg7.4.1   /vol01/pggeo      5435
>> /usr/local/pg7.4.1   /vol01/pgicdmdb   5434
>> /usr/local/pg7.4.1   /vol03/pg74       5432
>>
>>
>> Clearly other fields are possible (like ALIAS for the names you were 
>> using, and OPTS for extra arguments).
>>
>> This sort of layout is easily readable (more easily readable for those 
>> of us used to standard UNIX config files) and simply parsable too.
>>
>>
> 
> I am a Unix guy through and through, but its config files have pained me 
> many times over the years. Not least because of lack of consistency.
> 
> This sort of layout fails miserably if there are optional fields. Look 
> at the handsprings we had to turn to put CIDR addresses into 
> pg_hba.conf. And not without debate.
> 
> I don't much like ini style configs either.
> 
> These days, for Perl apps I generally make the config file a perl hash, 
> which can be as deeply structured as you like. The great advantage is 
> that you get parsing for free. For other apps I'm mildly inclined to 
> YAML or XML configs. All of these might be verbose, but they have 2 huge 
> advantages: they can adapt to structure, and they are somewhat 
> self-documenting. I am currently wrestling with an app that does 
> horrible things because its config needs to be tree structured and is 
> instead flat  (and also utterly unreadable).
> 

I agree that if there are real requirements that demand (more than one) 
optional parameter(s), and tree structures, then by all means let's use 
a format that can handle them properly.

However, it would be a shame to dive off into something complex if 
something simple would do. From what I've seen on this thread so far, 
the simple conf file is a good fit (unless I've missed something - 
always possible unfortunately :-)  ).

regards

Mark

markir@paradise.net.nz (Mark Kirkwood) wrote:
> Do you need name, value pairs? I was thinking that something like:
>
> # Postgres Cluster Registration
> #
> # PG_HOME PGDATA PORT
> /usr/local/pg7.4.1   /vol01/pggeo      5435
> /usr/local/pg7.4.1   /vol01/pgicdmdb   5434
> /usr/local/pg7.4.1   /vol03/pg74       5432
>
> Clearly other fields are possible (like ALIAS for the names you were
> using, and OPTS for extra arguments).
>
> This sort of layout is easily readable (more easily readable for those
> of us used to standard UNIX config files) and simply parsable too.

As mentioned before, the port number is redundant, and therefore
shouldn't be there at all.

There needs to be a name to identify each instance, so a mandatory
field is missing.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/slony.html
"By golly,  I'm beginning  to think Linux  really *is* the  best thing
since sliced bread." -- Vance Petree, Virginia Power

On Monday 27 February 2006 19:59, Josh Berkus wrote:
> > My frustration level often kills any desire to contribute to open
> > source. Sometimes, I think that open source is doomed. The various
> > projects I track and use are very frustrating, they remind me of
> > dysfunctional engineering departments in huge companies, it is very hard
> > to positively discuss any new ideas. The first response is always some
> > variation on "no."

> Well, if you weren't a regular I'd be more encouraging.  But you already
> know how things work here, so we can give you a hard time.    I'll point
> out the year-long argument over the newsysviews for the contributors, the
> two-year long process for 2PC, etc.

For what it's worth, I've been longing for a multiple cluster multi-version 
capable centralized startup and control mechanism for at least five years, 
and I think the archives would bear this out.  I just have never had the time 
to implement it, and it was always an RPM-centric thought plan for me.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

On Monday 27 February 2006 21:09, Bruce Momjian wrote:
> One question I have is how this feature would be an improvement over
> just pointing pg_ctl at a postgresql.conf configuration file.  That
> config file has the ability to specify most if not all server
> parameters.

The big problem is that postgresql.conf is dynamically generated during 
initdb, and its location depends upon initdb's parameters directly.  This 
makes it difficult to distribute, at least for packagers, a template of 
postgresql.conf or a 'default' postgresql.conf that plays nice with multiple 
versions and clusters, yet has centralized database tracking.
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu

Lamar Owen wrote:
> On Monday 27 February 2006 21:09, Bruce Momjian wrote:
> > One question I have is how this feature would be an improvement over
> > just pointing pg_ctl at a postgresql.conf configuration file.  That
> > config file has the ability to specify most if not all server
> > parameters.
> 
> The big problem is that postgresql.conf is dynamically generated during 
> initdb, and its location depends upon initdb's parameters directly.  This 
> makes it difficult to distribute, at least for packagers, a template of 
> postgresql.conf or a 'default' postgresql.conf that plays nice with multiple 
> versions and clusters, yet has centralized database tracking.

But looking at postgresql.conf I see:
#data_directory = 'ConfigDir'           # use data in another directory...#port = 5432

so it seems everything in this configuration file is going to be
duplicated in postgresql.conf.

We are adding an "include" capability for postgresql.conf.  Does that help?

Also, keep in mind this TODO item:* Allow pg_ctl to work properly with configuration files located outside  the PGDATA
directory pg_ctl can not read the pid file because it isn't located in the  config directory but in the PGDATA
directory. The solution is to  allow pg_ctl to read and understand postgresql.conf to find the  data_directory value.
 

I am thinking it should be fixed as part of this.

What if we add an option to initdb to allow the user to specify the name
and location of the postgresql.conf file?

--  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. +

Christopher Browne wrote:
> markir@paradise.net.nz (Mark Kirkwood) wrote:
> 
>>Do you need name, value pairs? I was thinking that something like:
>>
>># Postgres Cluster Registration
>>#
>># PG_HOME PGDATA PORT
>>/usr/local/pg7.4.1   /vol01/pggeo      5435
>>/usr/local/pg7.4.1   /vol01/pgicdmdb   5434
>>/usr/local/pg7.4.1   /vol03/pg74       5432
>>
>>Clearly other fields are possible (like ALIAS for the names you were
>>using, and OPTS for extra arguments).
>>
>>This sort of layout is easily readable (more easily readable for those
>>of us used to standard UNIX config files) and simply parsable too.
> 
> 
> As mentioned before, the port number is redundant, and therefore
> shouldn't be there at all.

Now that's an interesting one - I would be quite keen on being able to 
override postgresql.conf's port (typically do it now using PGPORT env) - 
however if there is a concensus that it's a bad thing, then lets drop it.

> There needs to be a name to identify each instance, so a mandatory
> field is missing.

Yeah, agreed - my vague muttering about ALIAS was attempting to suggest 
that :-).

Bruce Momjian wrote:
> Lamar Owen wrote:
> 
>>On Monday 27 February 2006 21:09, Bruce Momjian wrote:
>>
>>>One question I have is how this feature would be an improvement over
>>>just pointing pg_ctl at a postgresql.conf configuration file.  That
>>>config file has the ability to specify most if not all server
>>>parameters.
>>
>>The big problem is that postgresql.conf is dynamically generated during 
>>initdb, and its location depends upon initdb's parameters directly.  This 
>>makes it difficult to distribute, at least for packagers, a template of 
>>postgresql.conf or a 'default' postgresql.conf that plays nice with multiple 
>>versions and clusters, yet has centralized database tracking.
> 
> 
> But looking at postgresql.conf I see:
> 
>     #data_directory = 'ConfigDir'           # use data in another directory
>     ...
>     #port = 5432
> 
> so it seems everything in this configuration file is going to be
> duplicated in postgresql.conf.
> 
> We are adding an "include" capability for postgresql.conf.  Does that help?
> 
> Also, keep in mind this TODO item:
>     
>     * Allow pg_ctl to work properly with configuration files located outside
>       the PGDATA directory
>     
>       pg_ctl can not read the pid file because it isn't located in the
>       config directory but in the PGDATA directory.  The solution is to
>       allow pg_ctl to read and understand postgresql.conf to find the
>       data_directory value.
> 
> I am thinking it should be fixed as part of this.
> 
> What if we add an option to initdb to allow the user to specify the name
> and location of the postgresql.conf file?
> 

That is certainly a way to approach it, I see the tough bit being the 
parsing of postgresql.conf to figure out which parts of the global 
included file to ignore (i.e the stuff for the *other* clusters).

Would this work for the situation where you have older clusters on the 
box (versions that don't understand 'include')?

Additionally this would need to tackle start|stop etc for all clusters...

Cheers

Mark

Mark Kirkwood wrote:
> > What if we add an option to initdb to allow the user to specify the name
> > and location of the postgresql.conf file?
> 
> That is certainly a way to approach it, I see the tough bit being the 
> parsing of postgresql.conf to figure out which parts of the global 
> included file to ignore (i.e the stuff for the *other* clusters).
> 
> Would this work for the situation where you have older clusters on the 
> box (versions that don't understand 'include')?
> 
> Additionally this would need to tackle start|stop etc for all clusters...

I guess I am thinking we should move in a direction where all the
postgresql.conf files can be put in a single directory and pg_ctl would
know how to process multiple config files, rather than create a central
file with conflicts with postgresql.conf.

--  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. +

Bruce Momjian wrote:
> Mark Kirkwood wrote:
> 
>>>What if we add an option to initdb to allow the user to specify the name
>>>and location of the postgresql.conf file?
>>
>>That is certainly a way to approach it, I see the tough bit being the 
>>parsing of postgresql.conf to figure out which parts of the global 
>>included file to ignore (i.e the stuff for the *other* clusters).
>>
>>Would this work for the situation where you have older clusters on the 
>>box (versions that don't understand 'include')?
>>
>>Additionally this would need to tackle start|stop etc for all clusters...
> 
> 
> I guess I am thinking we should move in a direction where all the
> postgresql.conf files can be put in a single directory and pg_ctl would
> know how to process multiple config files, 

Ok - that certainly makes a lot of sense. I do see a need to be able to 
handle older versions tho (I'm guessing that this could probably be made 
to work as long as a *newer* pg_ctl parsing the config files). At first 
sight this looks more complicated to implement (this not necessarily 
being a major objection in this audience :-))
> rather than create a central
> file with conflicts with postgresql.conf.
> 

With respect to this point, the minimal proposal is a register of 
instance binary homes and data directories (plus a name/alias to identify):

ALIAS PGHOME PGDATA

I don't believe this conflicts with any postgresql.conf - it is merely a 
'signpost' to where they are. (Now Mark W and myself were suggesting 
having PORT and may OPTS there too, but see prev mail about that - I'm 
ok about losing these).

Cheers

Mark