Cross-DB linking? - Mailing list pgsql-admin

From Andrew Biagioni
Subject Cross-DB linking?
Date
Msg-id D0MHPM42PJPOCB74EBOL93MGGEUSXWF.3f60e4f9@Laptop
Whole thread Raw
In response to Export to file  ("Marvin" <magosoft@gmx.net>)
Responses Re: Cross-DB linking?
List pgsql-admin
I am thinking of separating my data into various DBs (maybe on the same server,
probably not) -- mostly for performance/stability/backup reasons -- but I have
a considerable amount of foreign keys, views, and queries that would need to
work across DBs if I were to split things the way I want to.

Is it possible to have foreign keys / views / queries work across database
boundaries?  On the same server / on separate servers?  If so, how?

For example, I have:
 - a table, A, with > 200 K rows which never changes;
 - another table, B with < 10 K rows which changes frequently;
 - and a third table, C, which joins A and B, i.e. has foreign keys into A and
B, and changes rarely

I would like to have A in one DB, dbA (possibly its own server);  B in another
DB, dbB (possibly its own server);  and C either with A or with B (this one is
not an issue per se).

What I'm looking to gain is:
 - dbA would be backed up/replicated religiously, and possibly on a server
optimized for frequent writes
 - dbB would NEVER be backed up, possibly on a server optimized for cacheing
 - each database's schema would be simpler and easier to manage
 - as the number of records and users grow, be able to distribute the
computing/storage/memory load among various machines rather than have to
upgrade the hardware

Thanks in advance!

        Andrew




pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Does VACUUM ever free up any disk space?
Next
From: "scott.marlowe"
Date:
Subject: Re: Cross-DB linking?