Re: exporting data from one DB to another asynchronously

On Mar 5, 2006, at 9:56 PM, Nagita Karunaratne wrote:

> Someone suggested that I have a single Postgres instance with three
> tablespaces. If I have a separate machine running the Postgres DB and
> three applications accessing only the tables in their respective
> tablespace that may be easier and more secure to implement.


This does not make sense. A tablespace is a way to setup storage for  
a single database. It does not buy you anything in terms of security  
or redundancy. If you really want separate databases and want to use  
only PostgreSQL to shuttle data between them, look at the contrib  
module called dblink (see FAQ 4.17 below).

The more typical way to do this would be to setup your three  
databases and use a client-side utility to merge them. By client-side  
I just mean some program (e.g. in Perl, PHP or any other language  
that can access PostgreSQL) which is setup to access all three  
databases and merge information between them.

I you have not yet built the web application for this, I highly  
recommend Drupal. You can setup multiple web sites easily, each with  
their own database. There is a cron facility you can invoke to sync  
your databases as needed. It would also be easy to setup each  
structure in a separate schema in the same database. You could setup  
permissions to provide the security you need, but have everything in  
one database making it easier to synchronize.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


=====
4.17) How do I perform queries using multiple databases?

There is no way to query a database other than the current one.  
Because PostgreSQL loads database-specific system catalogs, it is  
uncertain how a cross-database query should even behave.

contrib/dblink allows cross-database queries using function calls. Of  
course, a client can also make simultaneous connections to different  
databases and merge the results on the client side.