One Database per Data File?

[Randy Yates]

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?
--
% Randy Yates % "Maybe one day I'll feel her cold embrace,
%% Fuquay-Varina, NC % and kiss her interface,
%%% 919-577-9882 % til then, I'll leave her alone."
%%%% <ya***@ieee.org> % 'Yours Truly, 2095', *Time*, ELO
http://home.earthlink.net/~yatescr

[Christopher Browne]

Oops! Randy Yates <ya***@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

[Randy Yates]

Christopher Browne <cb******@acm.org> writes:

Oops! Randy Yates <ya***@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)',
%%%% <ya***@ieee.org> % *A New World Record*, ELO
http://home.earthlink.net/~yatescr

[Christopher Browne]

After takin a swig o' Arrakan spice grog, Randy Yates <ya***@ieee.org> belched out:

Christopher Browne <cb******@acm.org> writes:

Oops! Randy Yates <ya***@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.

[Martin Münstermann]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Randy Yates]

Christopher Browne <cb******@acm.org> writes:

After takin a swig o' Arrakan spice grog, Randy Yates <ya***@ieee.org> belched out:

Christopher Browne <cb******@acm.org> writes:

Oops! Randy Yates <ya***@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..."
%%%% <ya***@ieee.org> % 'One Summer Dream', *Face The Music*, ELO
http://home.earthlink.net/~yatescr

[Martijn van Oosterhout]

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:

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?
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ 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.

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

iD8DBQFBQoouY5Twig3Ge+YRAo3HAJ9JRx5FYnYox6KSOy878N nV2EizOwCfaaiR
2waD67KYY0vI4HLeifbFmCo=
=8UQu
-----END PGP SIGNATURE-----

[Doug McNaught]

Randy Yates <ya***@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

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