Sharing static data among several databases

From: "Robert James" <srobertjames(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Sharing static data among several databases
Date: 2007-11-18 20:39:12
Message-ID: e09785e00711181239l59f932f0r91b976228b821d6e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

We have an application in which every customer has their own database, all
running from our Postgres server.

There is a large, mostly static, database of information (chemical
information), which each customer needs read access to. Lots of customer
data points to this static db, with foreign keys.

Being that Postgres doesn't support FKs
spanning databases, we currently have a copy of the static data in
each customer's database. However, we'd much rather keep all the
static data in one, separate, shared database, with each customer
having read only access. (This should improve performance, make
maintenance easier, and simplify the customer's database layout.)
But we don't want to give up on Foreign Keys.

So, my questions are:
1.) Is there a way of separating, isolating, and sharing the shared data
that will still allow FKs to it?
2.) If so, can this be done in a way which won't rely on non-standard PG
extensions? That is, although we are using Postgres now, we don't want to
structure our system in a way which will marry us to it - we'd like to
retain the flexibility to migrate DBMS without having to rearchitecture the
entire data.

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Douglas McNaught 2007-11-18 20:46:31 Re: Sharing static data among several databases
Previous Message Tom Lane 2007-11-18 20:33:19 Re: 8.3b2 XPath-based function index server crash