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

From Christopher Browne
Subject Re: One Database per Data File?
Date
Msg-id m3zn44133x.fsf@wolfe.cbbrowne.com
Whole thread Raw
List pgsql-general
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

pgsql-general by date:

Previous
From: Joel
Date:
Subject: So, would it make sense to do something like a CRC on each record? (was How to determine a database is intact?)
Next
From: Joe Conway
Date:
Subject: Re: how to query the size of an array in a table