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/