pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter Eisentraut <peter_e@gmx.net>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: Richard J Kuhns <rjk@grauel.com>
Cc: pgsql-general@postgresql.org, PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: Question about databases in alternate locations...
Date: Sat, 20 May 2000 15:35:58 +0200 (CEST)
Message-ID: <Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain> (raw)
In-Reply-To: <39254511.5447BBA9@alumni.caltech.edu>

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




view thread (10+ messages)

Message-ID: <Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain>
Permalink:  ../Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain/
Also on:    postgresql.org/message-id/Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: peter_e@gmx.net, lockhart@alumni.caltech.edu, rjk@grauel.com, pgsql-hackers@postgresql.org
  Subject: Re: Question about databases in alternate locations...
  In-Reply-To: <Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain>
  References: <39254511.5447BBA9@alumni.caltech.edu> <Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox