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.