cancel
Showing results for 
Search instead for 
Did you mean: 

Is it possible to DIY change-data-capture based on foreign data wrapper?

Level 2 Adventurer

Is it possible to DIY change-data-capture based on foreign data wrapper?

Hi,

 

We tried to copy remote table data regularly based on pgAgent, upsert syntax in PGSQL 9.5 and foreign data wrapper.

 

The following link is our test note.(In Traditional Chinese)

https://ravenonhill.blogspot.tw/2016/11/pgagent-foreign-data-wrapper.html

 

But now we can only run over all rows in the remote table.

 

Is it possible to modify our approach to achieve change data capture totally based on PostgreSQL available features?

 

1 REPLY 1
Moderator

Re: Is it possible to DIY change-data-capture based on foreign data wrapper?


@alexc168 wrote:

 

Is it possible to modify our approach to achieve change data capture totally based on PostgreSQL available features? 


Hi Alex,

 

I think in the past, some have attempted to "achieve change data capture totally based on PostgreSQL available features" by using triggers, but that has often resulted in a loss of performance.

 

Aside from that, I haven't come across a completely-internal change data capture solution.  One idea is to leverage logical decoding to parse through the WAL stream/sequence to capture all the changes that occurred in the cluster.  There doesn't seem to be any software dedicated to doing this yet, though you could conceivably hack something around existing log-based replication engine to do this (i.e., EDB Replication Server).