Re: [GENERAL] Question about databases in alternate locations...

Thomas Lockhart writes:

> So pg_location would hold the full path (absolute or logical) to every
> file resource in every database? Or would it hold only a list of
> allowed paths?

The way I imagined it it would hold data like this:

    locname     |      locpath
----------------+-------------------
 alt1           | /mnt/foo/db
 joes alt store | /home/joe/storage

When I create a database I would then do CREATE DATABASE "my_db" WITH
LOCATION = "alt1"; which would place the database at
/mnt/foo/db/data/base/my_db. Then if I create another that I want at the
same place I do CREATE DATABASE "another" WITH LOCATION =
"alt1";. pg_database would presumably contain a reference to
pg_location.oid instead of the current datpath attribute. So one could say
I'm really just normalizing pg_database.

In some future life you might be able to do CREATE TABLE xxx (...) WITH
LOCATION = "joes alt store" but then we'd have to think about how to
resolve the path. One idea would be to get rid of per-database
subdirectories and just store all heap files in one directory, but I'm
sure Bruce would hate that. :) But that's another day's story.

So yes, it is a list of allowed locations associated with freely choosable
descriptive names. Environment variables do essentially provide a similar
service but I find this much more administration friendly and
flexible. (E.g., "What sort of stuff is being stored at /var/abc/def?" --
use a query)

> > 1. shut down database
> > 2. move data area
> > 3. connect to template1
> > 4. update pg_location
> > 5. connect to the moved database
> > That's not very different.
>
> But hard to do?

ALTER LOCATION "name" SET PATH TO '/new/path';? (Alternatively, use update
pg_location set locpath='/new/path' where locname='name'.) That isn't any
harder than setting environment variables. It might in fact be easier.

> but imho having some decoupling between logical paths in the database
> and actual paths outside is A Good Thing. Always has been a mark of
> good design in my experience.

Sure, that's exactly what this would provide. locname is the logical name
of the "storage location", locpath is the physical path. It's just a
matter of whether you maintain that information in environment variables
(which might get unset, forgotten, require postmaster shutdown, are
subject to certain rules we don't control) or in the database (which comes
with all the conveniences you might imagine).

--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden