Re: pg_config, pg_service.conf, postgresql.conf ....

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/

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/

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

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.

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

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/

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/

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?