"Dynamic routing" to different databases - Mailing list pgsql-admin

Hello all,

We have a rather complex service delivery application that is presently
designed with a "single-tenant" use case in mind: one self-contained
PostgreSQL database with all necessary tables, and one service consuming
that database, configured via a static connection string.

We are trying to figure out the most economical technical possibilities
for retrofitting "multi-tenant" functionality into it; you might think
of it as a kind of virtual hosting, where different requests coming into
the consuming service, segregated by, say, request URI domain, result in
very different behaviour.

Obviously, one way to do this is to add an additional layer of
abstraction at the database schema and the application level itself,
creating nested entities and so forth. This is impractical, however; it
would be nothing short of a wholesale overhaul of the entire codebase
(which consists in large part of PostgreSQL stored procedures for the
business layer).

So, what we're trying to figure out is the feasibility of grafting
multi-tenant functionality into the existing paradigm. For instance, we
could have a "meta-data" database with a table that defines DNS domains
and maps them to other database names/connection info on the same
server. We could then create a new and self-contained database for each
"tenant". I think that would make the most sense by far.

The major limitation is that the consuming service can connect to one
database and one database only, so what is needed is some way to present
foreign databases through one PostgreSQL connection handle to one
particular database.

The dblink facility seems to provide ways of doing this, but what I am
not clear on is: 1) how transparently the foreign database can be
rendered, vs. having to graft in lots of dblink_*() calls and 2) whether
there is a significant performance hit in using dblink, assuming the
"remote" tenant databases are located on the same cluster. Can this be
done using foreign data wrappers or something of the sort, for instance?

Failing that, is there some functionality in PgPool or another
connection proxy which can effectively take

    SELECT * FROM tbl META_ATTRIB = 'domain.com';

and transform this into

    SELECT * FROM tbl;

on the appropriate database for 'domain.com', which is a mapping that
would be maintained in something that is, at least in principle,
dynamically reloadable?

Advice much appreciated!

-- Alex

--
Alex Balashov | Principal | Evariste Systems LLC
303 Perimeter Center North, Suite 300
Atlanta, GA 30346
United States

Tel: +1-800-250-5920 (toll-free) / +1-678-954-0671 (direct)
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/