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.

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.