Thread: Multi Database queries

Hi All..
I am looking into setting up a multi-database system with Postgres7.3.4.
In this case all the databases will be on the one server.
Initial research shows that the contrib/dblink module is needed....
My question is:
    Is this the only way to query across databases?

Jonathon

Jonathon Batson wrote:
> I am looking into setting up a multi-database system with Postgres7.3.4.
> In this case all the databases will be on the one server.
> Initial research shows that the contrib/dblink module is needed....
> My question is:
>    Is this the only way to query across databases?

Yes, but, if your databases are all on one server under one postmaster
you should consider using one database with multiple schemas. See:
   http://www.postgresql.org/docs/current/static/sql-createschema.html

HTH,

Joe

Jonathon Batson wrote:

> I am looking into setting up a multi-database system with Postgres7.3.4.
> In this case all the databases will be on the one server.
> Initial research shows that the contrib/dblink module is needed....
> My question is:
>     Is this the only way to query across databases?

Another way is to make separate simultaneous connections in your
application (which you can do using the perl DBI and probably other
languages, too) and split up the query into separate queries for each
database, assembling them in the application.

Doing it this way the databases could be on separate machines.

That's not very convenient if you are doing this many times, but if that's
the case maybe you shouldn't be using separate databases anyway.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
     Check out my Musician's Online Database Exchange (The MODE Pages)
                        http://www.TheMode.com
 ==========================================================================

Joe Conway wrote:

> Jonathon Batson wrote:
>
>> I am looking into setting up a multi-database system with Postgres7.3.4.
>> In this case all the databases will be on the one server.
>> Initial research shows that the contrib/dblink module is needed....
>> My question is:
>>    Is this the only way to query across databases?
>
>
> Yes, but, if your databases are all on one server under one postmaster
> you should consider using one database with multiple schemas. See:
>   http://www.postgresql.org/docs/current/static/sql-createschema.html

We're using this for one of our projects and it really works well.
Initially we had separate databases and queried them using dblink, but
for our purposes multiple schemas works much better, and cross schema
queries are as simple as any other queries. BTW if you are looking at
setting up a new system, why not use 7.4.5 (or whatever the latest
stable version is)?

Ron

>
> HTH,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>

On 22/09/04, Ron St-Pierre (rstpierre@syscor.com) wrote:
> >Yes, but, if your databases are all on one server under one postmaster
> >you should consider using one database with multiple schemas. See:
> >  http://www.postgresql.org/docs/current/static/sql-createschema.html
>
> We're using this for one of our projects and it really works well.
> Initially we had separate databases and queried them using dblink, but
> for our purposes multiple schemas works much better, and cross schema
> queries are as simple as any other queries. BTW if you are looking at
> setting up a new system, why not use 7.4.5 (or whatever the latest
> stable version is)?

I concur. Using schemas works very well. Another benefit is that you can
load your plpgsql functions into a schema and set your search path to
see it or perhaps another schema loaded with test functions.

eg "set search_path = data_schema, function_schema, global_schema;"
versus
eg "set search_path = data_schema, test_function_schema, global_schema;"

This can be a great way of testing your new functions against existing
data.

Rory