Is there some kind of table-level replication in PostgreSQL?

Question

I work with two databases in PostgreSQL 9.1, say A and B, one per application. Database B needs to read (and not write into) one table in database A (say users), but at the same time some tables from database B need to reference some of their fields as foreign keys on table users.

So far I've tried:

• Finding some kind of native table-level replication, so any change on users is automatically replicated to the clone table in database B. Didn't find anything.

• Creating a TRIGGER on table users so after any change a function is called, but that doesn't solve anything since I can't insert values from database A into database B's tables.

• Using FOREIGN TABLEs. Neither solves it, since no foreign keys may be applied to foreign tables.

• Use a SCHEMA instead of DATABASE for A, but application enforces it to be a database.

• I've even thought about a CRON each minute which would dump and import the table from A to B, but that would break any foreign key relationship with other tables.

I can't combine the two databases in one (although technically both have uniquely named tables), because when upgrading one of them it checks the database and should anything weird be in it, it would fail.

Do I have any other chances here? Any hint is very appreciated.

• Neil McGuigan: how about postgresql.org/docs/current/static/postgres-fdw.html to read from the other db? (9.3+ though) . else postgresql.org/docs/9.3/static/dblink.html
• Craig Ringer: Table-level replication isn't built-in though pglogical aims to change that. Londiste, Slony-I and Bucardo offer trigger-based replication at a table level.
• András Váczi: Could you be slightly more specific on what 'weird' means and how it is checked?
• bma: mimeo might help with your issue. Alternatively, if your application forces you to connect to a database, are you able to work around that by connecting to pgbouncer and have it redirect your queries to specific schemas in the same database?
• nKn: Thanks for the comments - @bma I'm afraid not, because both databases use the same schema. However, I've been able to fix this somehow, I'll post an answer in a while. Anyway, if someone has a way to improve this way I'll be grateful to know.

Answer 1

I finally managed to find a solution that works so far. I installed plpython procedural language module along with the psycopg2 module, wrote a FUNCTION and created two TRIGGERs, one for INSERT and one for DELETE (I won't have any UPDATE events).

The function code would be something like this:

CREATE OR REPLACE FUNCTION synchronize()
RETURNS trigger AS
$$
 import psycopg2
 conn = psycopg2.connect("host=... dbname=... user=... password=...")
 cur = conn.cursor()
 # If inserting...
 if TD['event'] == 'INSERT':
 cur.execute(...)
 # If deleting...
 if TD['event'] == 'DELETE':
 cur.execute(...)
 conn.commit()
 cur.close()
 conn.close()
$$
LANGUAGE 'plpython2u' VOLATILE;

Then, I defined both triggers:

CREATE TRIGGER replica_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE PROCEDURE synchronize();
CREATE TRIGGER replica_delete
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE PROCEDURE synchronize();

Maybe over-complicated, or patchy, but right now this is the easiest way I found how to do it. If someone improves it or has an alternative (preferably native) way to do it, I'll be glad to change the accepted answer.