Re: [GENERAL] Question about databases in alternate locations...
От | Peter Eisentraut |
---|---|
Тема | Re: [GENERAL] Question about databases in alternate locations... |
Дата | |
Msg-id | Pine.LNX.4.21.0005200047570.489-100000@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: [GENERAL] Question about databases in alternate locations... (Thomas Lockhart <lockhart@alumni.caltech.edu>) |
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: