2

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.

  • 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 – Neil McGuigan Jan 23 '16 at 0:12
  • 3
    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. – Craig Ringer Jan 23 '16 at 16:35
  • Could you be slightly more specific on what 'weird' means and how it is checked? – dezso Jan 23 '16 at 22:55
  • 2
    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? – bma Jan 24 '16 at 5:25
  • 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. – nKn Jan 25 '16 at 13:05
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.