1

The question is rather simple, but I can't find any documentation on it:

How can I mirror specific tables from a database to another?

The basic idea is having twin databases that share only specific tables between them

Any advice will be appreciated! If PostgreSQL can't do it, is there another RDBMS that can? Thanks in advance!

EDIT : The reason I want to do this is to share "arbitrary" info across two databases using django, without losing proper referential integrity. For example:

Let's say we have a clients, products and sales tables. We want to share our client and product base between two companies, but not our sales. This can be extended to any particular situation (share stocks but not clients, users but not permissions, etc). So I thought the easiest solution was to share specific tables among databases. If there is a better approach to the problem, feel free to share your experiences! Thanks in advance

Alvaro
  • 11,797
  • 9
  • 40
  • 57
  • You can do that using Slony (a replication tool for Postgres) –  Jan 15 '14 at 13:33
  • The easiest solution is to use different schemas in the same database. Much easier than twin databases. This [this question](http://stackoverflow.com/questions/1152405) among others. – Daniel Vérité Jan 15 '14 at 13:42
  • Yeah, it looks like it, but django doesn't support schemas yet – Alvaro Jan 15 '14 at 13:54
  • 1
    @Alvaro: you can setup two different users that have different search paths. All common tables would go e.g. into the schema public, and the others into their own schema. Because of the search path, the tables in the non-public schema don't need to be prefixed with the schema. And each user sees a different set of tables. If you name the schema like the username, you don't even need to change the search_path because that is the default setting –  Jan 15 '14 at 14:47
  • Okay! I will give it a try!! Thanks for giving me a startpoint, really appreciate it (if you want, make an answer out of it and I'll accept it) – Alvaro Jan 15 '14 at 15:21

1 Answers1

7

There are few possibilities:

  • Master/Master replication (Bucardo), Master/Slave replication (Slony)

  • Using foreign data wrappers - you can access a any table from other databases. 9.2 provide comfort FDW read only driver, 9.3 contains read/write FDW driver

CREATE EXTENSION postgres_fdw ;
CREATE SERVER omega FOREIGN DATA WRAPPER postgres_fdw 
   OPTIONS (host 'localhost', dbname 'other_database');
CREATE USER MAPPING FOR pavel SERVER omega;
CREATE FOREIGN TABLE oo (a int) SERVER omega;

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM oo WHERE a BETWEEN 1 AND 100;

FDW is probably most simple solution how to share data.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • Bucardo looks interesting for my use case, since the only thing django would need to do is use a different database for the queries – Alvaro Jan 15 '14 at 13:41