One Database per Data File?

**Christopher Browne** · Nov 23 '05, 02:00 AM

Re: One Database per Data File?

Oops! Randy Yates <yates@ieee.org > was seen spray-painting on a wall:[color=blue]
> 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?[/color]

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") )

404 Not Found

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

**Randy Yates** · Nov 23 '05, 02:00 AM

Re: One Database per Data File?

Christopher Browne <cbbrowne@acm.o rg> writes:
[color=blue]
> Oops! Randy Yates <yates@ieee.org > was seen spray-painting on a wall:[color=green]
>> 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?[/color]
>
> 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.[/color]

That does not seem to be the case. I have three subdirectories in
my base/ directory, but according to PGADMIN III, only one database.
[color=blue]
> 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[/color]

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

**Christopher Browne** · Nov 23 '05, 02:00 AM

Re: One Database per Data File?

After takin a swig o' Arrakan spice grog, Randy Yates <yates@ieee.org > belched out:[color=blue]
> Christopher Browne <cbbrowne@acm.o rg> writes:
>[color=green]
>> Oops! Randy Yates <yates@ieee.org > was seen spray-painting on a wall:[color=darkred]
>>> 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?[/color]
>>
>> 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.[/color]
>
> That does not seem to be the case. I have three subdirectories in
> my base/ directory, but according to PGADMIN III, only one database.[/color]

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.
[color=blue][color=green]
>> 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;[/color]
>
> 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.[/color]

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 "postmaster s;"
four different ports for clients to connect to.
--
output = ("cbbrowne" "@" "cbbrowne.c om")

http://www3.sympatico.ca/cbbrowne/advocacy.html

debugging, v:
Removing the needles from the haystack.

**Martin Münstermann** · Nov 23 '05, 02:00 AM

Re: One Database per Data File?

Christopher Browne wrote:[color=blue][color=green]
>>That does not seem to be the case. I have three subdirectories in
>>my base/ directory, but according to PGADMIN III, only one database.[/color]
>
>
> Look more closely; there _are_ three databases there. If PGAdmin III
> is saying otherwise, it's hiding something.[/color]

Check out Display -> "System objects" from the menu.

Martin

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Randy Yates** · Nov 23 '05, 02:00 AM

Re: One Database per Data File?

Christopher Browne <cbbrowne@acm.o rg> writes:
[color=blue]
> After takin a swig o' Arrakan spice grog, Randy Yates <yates@ieee.org > belched out:[color=green]
>> Christopher Browne <cbbrowne@acm.o rg> writes:
>>[color=darkred]
>>> 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.[/color]
>>
>> That does not seem to be the case. I have three subdirectories in
>> my base/ directory, but according to PGADMIN III, only one database.[/color]
>
> Look more closely;[/color]

There was nothing wrong with my eyes.
[color=blue]
> there _are_ three databases there. If PGAdmin III
> is saying otherwise, it's hiding something.[/color]

As Martin kindly pointed out, PGADMIN III hides these other two
databases (the one being my application database which I created).
[color=blue]
> 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.[/color]

Right, with system objects unhidden, that is the case reported by
PGADMIN III.
[color=blue][color=green][color=darkred]
>>> 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;[/color]
>>
>> 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.[/color]
>
> 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 "postmaster s;"
> four different ports for clients to connect to.[/color]

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

**Martijn van Oosterhout** · Nov 23 '05, 02:05 AM

Re: One Database per Data File?

Not even close. PostgreSQL uses one or more files per
table/index/sequence/etc. Each database has its own directory. I think
with tablespaces you can even spread a database over multiple
directories.

Secondly, every connection gets its very own postmaster, they can can
each access any file they wish.

Hope this helps,

On Mon, Sep 06, 2004 at 02:47:56AM +0000, Randy Yates wrote:[color=blue]
> 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?[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBQoouY5T wig3Ge+YRAo3HAJ 9JRx5FYnYox6KSO y878NnV2EizOwCf aaiR
2waD67KYY0vI4HL eifbFmCo=
=8UQu
-----END PGP SIGNATURE-----

**Doug McNaught** · Nov 23 '05, 02:05 AM

Re: One Database per Data File?

Randy Yates <yates@ieee.org > writes:
[color=blue]
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings