Обсуждение: Re: One Database per Data File?
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. 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
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.
Christopher Browne wrote: >>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. Check out Display -> "System objects" from the menu. Martin
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
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
Randy Yates <yates@ieee.org> writes: > 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. You definitely can. Each one needs a different value of PDGATA and PGPORT. You also need to make sure shared memory limits, semaphores etc are beefy enough to handle all the instances. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863