Re: Question about databases in alternate locations...

> That brings up a comment, a question, and an offer.  First, the comment: I
> actually did check the user's guide before I posted the question, but the
> description of initlocation doesn't mention it at all -- it just gives an
> example that doesn't work unless the backend already knows about the
> variable.  It does refer to the CREATE DATABASE section, but at a quick
> glance (I know, I should have read more carefully, mea culpa!) I just saw
> an example that looked similar to the initlocation example.

Hmm. You are right, the doc on initlocation is weak. I'll put it on my
todo list (and will always gladly accept patches to the sgml sources
or just new words in an email ;)

However, the topic is covered in more detail in the Admin Guide, in
the chapter on "Disk Management" (actually, it is the only topic in
that chapter so far :(

> Now for the question.  What's the reason for using this method, as opposed
> to using, say, a system catalog to hold the valid locations?  Historical?
> Having to stop and restart the backend so it can re-read its environment
> seems kind of archaic.

This was and is a topic of discussion on the -hackers list. Peter E
(if I recall right) was proposing some changes to remove the
environment variable capabilities in Postgres. He also proposed making
a *list* of allowed locations as an environment variable as a way of
managing or controlling the allowed locations.

In my view, environment variables (or some other mechanism,
potentially) allow a dbamin to decouple the storage location from the
database contents, and give some control over allowed locations. The
current implementation is not ideal; for example Peter's proposal to
have a list of allowed locations seems great, since at the moment the
backend will try *any* environment variable (e.g. $HOME) so could be a
security problem.

Putting all of this stuff in a table is a possibility, but
1) Ingres did this, but they had way too many tables involved in
defining and using tables imho. We should do better.
2) If a dbadmin wants to *carefully* move database locations around,
the environment variables allow this to happen by just shutting down
the backend, tarring/untarring a disk area, redefining the environment
variable, and restarting the backend.
3) We don't (yet) have a way to move tables from within Postgres. So
hardcoding or "hard storing" absolute paths would make it pretty
difficult to accomplish (2).

> Now the offer.  I'm in the design stage of the process of converting a
> fairly large legacy application to PostgreSQL.  It's going to be
> essentially a complete re-write, but I should be able to do it in
> more-or-less independent sections.  I really like what I've experienced so
> far of PostgreSQL, I'd like to contribute, and modifying the postmaster to
> use (or at least look at, if it exists) a system catalog for this info
> might be a good way to get my feet wet.  Comments?

Not sure that we should do the system catalog thing without first
implementing the ability to do a "ALTER TABLE SET LOCATION=..."
command from within Postgres. But it's time to move the the -hackers
list. Welcome!

                      - Thomas

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California