Re: Multiple logical databases - Mailing list pgsql-hackers

> Mark Woodward schrieb:
> ...
>> Unless you can tell me how to insert live data and indexes to a cluster
>> without having to reload the data and recreate the indexes, then I
>> hardly
>> think I am "misinformed." The ad hominem attack wasn't nessisary.
>
> I see you had a usecase for something like pg_diff and pg_patch ;)
> ...
>> If no one sees a way to manage multiple physical database clusters as
>> one
>> logical cluster as something worth doing, then so be it. I have a
>> practical example of a valid reason how this would make PostgreSQL
>> easier
>> to work with. Yes there are work arounds. Yes it is not currently
>> unworkable.
>
> I dont see your problem, really ;)
>
> 1) if you have very big and very workloaded databases, you often have
> them on different physically boxes anyway
> 2) you can run any number of postmasters on the same box - just put
>    them to listen on different ip:port.
>
> Now to the management - you say cddb and geodb are managed off host.
> So they are not managed on the life server and so you dont need to
> switch your psql console to them.
>
> And yeah, its really not a problem, to quit psql and connect
> to a different server anyway :-)
>
> If you dont like to type -p otherport, you can either create
> aliases with all the arguments or use something like pgadmin3
> which enables you to easy switch from database to database,
> from host to host as you like.
>
> Now is there any usecase I have missed which you still would
> like to have addressed?

I don't, as it happens, have these databases on different machines, but
come to think about it, maybe it doesn't matter.

The "port" aspect is troubling, it isn't really self documenting. The
application isn't psql, the applications are custom code written in PHP
and C/C++.

Like I said, in this thread of posts, yes there are ways of doing this,
and I've been doing it for years. It is just one of the rough eges that I
think could be smoother.

(in php)
pg_connect("dbname=geo host=dbserver");

Could connect and query the dbserver, if the db is not on it, connect to a
database of known servers, find geo, and use that information to connect.
It sounds like a simple thing, for sure, but to be useful, there needs to
be buy in from the group otherwise it is just some esoteric hack.

The point is, that I have been working with this sort of "use case" for a
number of years, and being able to represent multiple physical databases
as one logical db server would make life easier. It was a brainstorm I had
while I was setting this sort of system for the [n]th time.

For my part, I have tried to maintain my own change list for PostgreSQL in
the past, but it is a pain. The main source changes too frequently to keep
up and in the end is just another project to maintain.

Using the "/etc/hosts" file or DNS to maintain host locations for is a
fairly common and well known practice, but there is no such mechanism for
"ports." The problem now becomes a code issue, not a system administration
issue.

If one writes the code to their website to use a generic host name, say,
"dbserver," then one can easily test system changes locally and push the
code to a live site. The only difference is the host name. When a port is
involved, there is no systemic way to represent that to the operating
system, and must therefor be part of the code. As part of the code, it
must reside in a place where code has access, and must NOT be pushed with
the rest of the site.

Having some mechanism to deal with this would be cleaner IMHO.