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

Christopher Browne <cbbrowne@acm.org> writes:

> After takin a swig o' Arrakan spice grog, Randy Yates <yates@ieee.org> belched out:
>> 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.
>
> Look more closely;

There was nothing wrong with my eyes.

> there _are_ three databases there.  If PGAdmin III
> is saying otherwise, it's hiding something.

As Martin kindly pointed out, PGADMIN III hides these other two
databases (the one being my application database which I created).

> I don't know what the "one" is, but the other two are definitely
> template0 and template 1.  template1 is used as the "default template"
> for new databases that are created; template0 is locked down against
> updates.

Right, with system objects unhidden, that is the case reported by
PGADMIN III.

>>> 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;
>>
>> 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.
>
> The "goal of 1 db per initdb" isn't strictly possible, because there
> will always be template0 and template1.  But I suppose you could
> configure things to forbid access to anything other than the one
> database that you _want_ used.
>
> As for the 'multiple instances' part, that may be possible.
>
> I have hosts on which there are four instances of PostgreSQL running.
> Four instances of initdb; four init scripts; four base "postmasters;"
> four different ports for clients to connect to.

Ah - good idea! Map the individual databases to specific ports. Thanks!
--
%  Randy Yates                  % "Bird, on the wing,
%% Fuquay-Varina, NC            %   goes floating by
%%% 919-577-9882                %   but there's a teardrop in his eye..."
%%%% <yates@ieee.org>           % 'One Summer Dream', *Face The Music*, ELO
http://home.earthlink.net/~yatescr