Thread: schema proxying virtual database

Dear List,

There are many opensource applications that support postgresql
(eg , gforge , bricolage , dspam ..) but does not use schemas(namespaces)
as a result of which you are forced to use/create a new database and loose the
advantage of linking the application data with your existing database.
as no-cross database queries can be done in PG.


my question is , is it a feasible idea to have some special kind of database
in the postgresql cluster that mimics a schema of an existsing database.

say rt3 is a special database that links to existing maindb databases' 
rt3 namespace then  any DDL / DML done to public schema of rt3 database
is redirected to rt3 namespace of the existing database maindb's rt3 schema.

regds
mallah.

Rajesh Kumar Mallah <mallah.rajesh@gmail.com> writes:

> my question is , is it a feasible idea to have some special kind of database
> in the postgresql cluster that mimics a schema of an existsing
> database.

Try abusing pgbouncer to this effect. Configure several pgbouncer
databases pointing to the same physical one, with different roles
e.g. having each their own search_path so that they first see their
application's schema.

Regards,
--
dim

Rajesh Kumar Mallah wrote:
> Dear List,
>
> There are many opensource applications that support postgresql
> (eg , gforge , bricolage , dspam ..) but does not use schemas(namespaces)
> as a result of which you are forced to use/create a new database and
> loose the
> advantage of linking the application data with your existing database.
> as no-cross database queries can be done in PG.
>
>
> my question is , is it a feasible idea to have some special kind of database
> in the postgresql cluster that mimics a schema of an existsing database.
>
> say rt3 is a special database that links to existing maindb databases'
> rt3 namespace then  any DDL / DML done to public schema of rt3 database
> is redirected to rt3 namespace of the existing database maindb's rt3 schema.

well in most cases it is enought to give those apps their own user and
setting the default search_path for that user to it's own schema. That
way you usually don't have to modify the app at all and still get it
(more or less) running in it's own schema.


Stefan

thanks that sounds like a really easy & neat solution.