Skip site navigation (1) Skip section navigation (2)

Multiple logical databases

From: "Mark Woodward" <pgsql(at)mohawksoft(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Multiple logical databases
Date: 2006-02-02 15:23:44
Message-ID: 16687.24.91.171.78.1138893824.squirrel@mail.mohawksoft.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
I am working on an issue that I deal with a lot, there is of course a
standard answer, but maybe it is something to think about for PostgreSQL
9.0 or something. I think I finally understand what I have been fighting
for a number of years. When I have been grousing about postgresql
configuration, this has been what I have been fighting.

One of the problems with the current PostgreSQL design is that all the
databases operated by one postmaster server process are interlinked at
some core level. They all share the same system tables. If one database
becomes corrupt because of disk or something, the whole cluster is
affected. If one db is REALLY REALLY huge and doesn't change, and a few
others are small and change often, pg_dumpall will spend most of its time
dumping the unchanging data.

Now, the answer, obviously, is to create multiple postgresql database
clusters and run postmaster for each logical group of databases, right?
That really is a fine idea, but....

Say, in pgsql, I do this: "\c newdb" It will only find the database that I
have in that logical group. If another postmaster is running, obviously,
psql doesn't know anything about it.

From the DB admin perspective, maybe there should be some heirarchical
structure to this. What if there were a program, maybe a special parent
"postmaster" process, I don't know, that started a list of child
postmasters based on some site config? The parent postmaster would hold
all the configuration parameters of the child postmaster processes, so
there would only be on postgresql.conf.

This also answers "how do we get postgresql options in a database,"
because the parent postmaster only needs to bootstrap the others, it can
be configured to run lean and mean, and the "real" settings can be
inspected and changed at will. A trigger will send a HUP to child
postmasters when their settings change. The parent postmaster only needs
one connection for each child and one admin, right?

Does anyone see this as useful?

Responses

pgsql-hackers by date

Next:From: Mark WoodwardDate: 2006-02-02 15:57:09
Subject: Re: Multiple logical databases
Previous:From: Tom LaneDate: 2006-02-02 15:19:04
Subject: Re: Multiple logical databases

pgsql-patches by date

Next:From: Alvaro HerreraDate: 2006-02-02 15:34:28
Subject: Re: TODO-Item: TRUNCATE ... CASCADE
Previous:From: Tom LaneDate: 2006-02-02 15:19:04
Subject: Re: Multiple logical databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group