Re: One Database per Data File?

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: One Database per Data File?
Date: 2004-09-06 03:35:30
Message-ID: m3zn44133x.fsf@wolfe.cbbrowne.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Oops! Randy Yates <yates(at)ieee(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Randy Yates 2004-09-06 04:02:48 Re: One Database per Data File?
Previous Message Randy Yates 2004-09-06 02:47:56 One Database per Data File?