Re: Multiple logical databases

> On Thu, 2 Feb 2006, Mark Woodward wrote:
>
>> 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?
>
> Not as described above, no. Perhaps with a more concrete plan that
> actually talks about these things in more details. For example, you posit
> the \c thing as an issue, I don't personally agree, but you also don't
> address it with a solution.

While I understand that it is quite a vague suggestion, I guess I was
brainstorming more than detailing an actual set of features.

My issue is this, (and this is NOT a slam on PostgreSQL), I have a number
of physical databases on one machine on ports 5432, 5433, 5434. All
running the same version and in fact, installation of PostgreSQL.

Even though they run on the same machine, run the same version of the
software, and are used by the same applications, they have NO
interoperability. For now, lets just accept that they need to be on
separate physical clusters because some need to be able to started and
stopped while others need to remain running, there are other reasons, but
one reason will suffice for the discussion.

From an administration perspective, a single point of admin would seem
like a logical and valuable objective, no?

Beyond just the admin advanatges, the utilities could be modified to
handle a root server that redirects to child servers. The psql program,
when handling a "\c" command, queries the root server to find the child
server and then connects to that.

libpq could also be modified to handle this without changing the
applications.

The child postmasters will query the root postmaster when a DB is created
and deleted to keep it up to date. Conflicts between two children can be
managed by either some sort of first come first serve or disallow creating
of a duplicate name, or some other method.

So, conn = connect("host=localhost dbname=mydb"); Will connect to the root
server, find the actual server, and then connect to it, completely hiding
the different physical databases, and creating one very large logical
install.

Perhaps this can even be written to include large scale clustering. Who
knows?