PostgreSQL trigger execute operations on different database

Question

How can I execute operation on a different database on specific trigger? When a value is inserted in my table, I want to insert the same data in another db and table (applying a logic before doing it).

How to implement it in my trigger?
How to relate with the second db?

postgres_fdw or dblink or listen/notify and local trigger. anyway maybe you are overcomplicating things?.. — Vao Tsun
– Vao Tsun, Commented Oct 23, 2017 at 10:58

Laurenz Albe · Accepted Answer · 2017-10-23 11:09:18Z

2

You would update the table in the second database via postgres_fdw.

But stop and think before you implement a solution like that.

The consequence is that the transaction that inserts the value won't finish before the value is inserted in the remote table. That will slow down the transaction considerably, and if the remote database is down for some reason, your INSERT will fail.

Data duplication is normally a problem. Usually it is a better solution to store them only once. What keeps you from using postgres_fdw to access the data from another database?

answered Oct 23, 2017 at 11:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Giuseppe Over a year ago

it's because i need not just a replication of dataset, but an historical path of every single entity, in my main db i have only current informations, bt in my histical db i have all the history. e.g an update in my main db needs to be a new insert telling me that from an instant t1 to an instant t2 the values for an entity was certain, then at t2 informations changed and became something else, if i want to know what datas i had in a specific time i can have a match, hope i explained

Scott Marlowe Over a year ago

You might be better off doing the update locally and then replicating the table elsewhere. That way if replication fails / falls behind, the transaction still goes through, and once replication is re-established it will automatically catch up, all without negatively impacting your main db.

Collectives™ on Stack Overflow

PostgreSQL trigger execute operations on different database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related