Sync transactions between databases in PostgreSQL using dblink

Question

I would like to sync a set of tables between two databases, transparently without application code chages. My idea is to create triggers on insert, update and delete, in the source database tables to replicate the data using dblink to the dest. database tables seamlessly.

The problem is that changes in the source tables are always done inside a transaction. The triggers automatically replicate changes in the dest. tables but if the source transaction is rolled back the dest. tables changes are not.

Is there a way to automatically sync transaction begin and commit/rollback between the two databases? A trigger-like behavior would be ideal.

filiprem · Accepted Answer · 2012-01-18 13:27:57Z

1

Yes, it's possible from ages - using Slony-I, or other trigger-based replication.

Row updates are logged to special tables on "master" side, and replayed asynchronously on "slave" side.

External program/daemon is used to synchronise changes.

See http://www.postgresql.org/docs/current/static/different-replication-solutions.html and http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling#Replication for more information.

answered Jan 18, 2012 at 13:27

filiprem

7,2131 gold badge34 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Van Dyk · Accepted Answer · 2013-09-05 04:01:22Z

1

When 9.3 comes out, check out http://www.postgresql.org/docs/9.3/static/postgres-fdw.html. You can roll back transactions in foreign databases.

answered Sep 5, 2013 at 4:01

Joe Van Dyk

6,9908 gold badges61 silver badges74 bronze badges

Collectives™ on Stack Overflow

Sync transactions between databases in PostgreSQL using dblink

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related