pg_config, pg_service.conf, postgresql.conf ....

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.

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:

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]http://ftp.debian.org/debian/pool/main/p/postgresql-common/postgresql-common_43.tar.gz and a quick
demonstration was posted [2]http://archives.postgresql.org/pgsql-hackers/2006-02/msg00942.php -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/.

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/
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----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 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!"

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/

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.

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,

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.

"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/

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.

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:

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"

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 specified in 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 are better uses for regret."
--thi <ttn@netcom.com>

"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

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 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:

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