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

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 _are_ three databases there.  If PGAdmin III
is saying otherwise, it's hiding something.

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.

>> 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.
--
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/advocacy.html
debugging, v:
        Removing the needles from the haystack.