Re: One Database per Data File? - Mailing list pgsql-general

Christopher Browne <cbbrowne@acm.org> writes:

> Oops! Randy Yates <yates@ieee.org> was seen spray-painting on a wall:
>> I'm a complete newbie to postgres so please look the other way if
>> these questions are really stupid.
>>
>> Is it legitimate to have one database per data file? For
>> organizational and backup purposes, I'd like to keep the database
>> files for each of several projects separate.  This means, e.g., that
>> postmaster must have multiple instances going simultaneously?
>>
>> I'm thinking the answer is NO because, for one, the TCPIP connection
>> seems to be to ONE instance of postmaster which then sorts out which
>> database objects are in its container.
>>
>> Am I close?
>
> Not terribly.
>
> For a given "cluster" (e.g. - an instance initialized using "initdb"),
> you have a set of databases, each of which is indicated by a directory
> under 'base/' in that cluster.

That does not seem to be the case. I have three subdirectories in
my base/ directory, but according to PGADMIN III, only one database.

> Within each database in the cluster, each table and index is indicated
> by one (or more, if size > 1GB) files.
>
> Thus, each database will have numerous data files, essentially one per
> table and one per index.
>
> If you rummage around in the files, you can learn quite a lot about
> the structuring of things.  Each file has a number; that number
> corresponds to the OID number in pg_class.
>
> Thus, if you find a file called "17441," then you could find out more
> about it by the query
>
>  select * from pg_class where oid = 17441;
> --
> (reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
> http://cbbrowne.com/info/internet.html
> "I love the way  Microsoft follows standards. In  much the same manner
> that fish follow migrating caribou." -- Sinister Midget

Ahh, ok. So how does this answer my question or help me achieve
my goal of one database per "initdb file set?" You also have not
answered whether or not postmaster can have multiple instances
running, each pointing to a different initdb file set.
--
%  Randy Yates                  % "How's life on earth?
%% Fuquay-Varina, NC            %  ... What is it worth?"
%%% 919-577-9882                % 'Mission (A World Record)',
%%%% <yates@ieee.org>           % *A New World Record*, ELO
http://home.earthlink.net/~yatescr