Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA47722; Sat, 20 May 2000 09:30:57 -0400 (EDT) (envelope-from peter@localhost.its.uu.se) Received: from regulus.student.UU.SE ([130.238.5.2]:47984 "EHLO regulus.its.uu.se") by merganser.its.uu.se with ESMTP id ; Sat, 20 May 2000 15:30:40 +0200 Received: from peter (helo=localhost) by regulus.its.uu.se with local-esmtp (Exim 3.02 #2) id 12t9Q6-0000Cp-00; Sat, 20 May 2000 15:35:58 +0200 Date: Sat, 20 May 2000 15:35:58 +0200 (CEST) From: Peter Eisentraut To: Thomas Lockhart cc: Richard J Kuhns , pgsql-general@postgresql.org, PostgreSQL Development Subject: Re: Question about databases in alternate locations... In-Reply-To: <39254511.5447BBA9@alumni.caltech.edu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: 8BIT X-Archive-Number: 200005/176 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