Re: [PERFORM] Best practice to load a huge table from ORACLE to PG

Joshua D. Drake wrote:
> Potluri Srikanth wrote:

>> But do we link oracle trigger to postgres trigger ?
>>
>> i mean :
>>
>> oracle trigger will take a note of what has been changed .
>> but then how do we pass those changes to postgres trigger ?

Note that this will be rather inefficient if you're obtaining a new connection every time. It looks like Oracle's Java stored procedures and triggers run in an appserver-like environment, though, so you should be able to use a connection pool, JNDI, or similar.

Some Java stored procedure examples:

http://www.oracle.com/technology/sample_code/tech/java/jsp/oracle9ijsp.html

You could also use a Java trigger to send simpler change message, with a serialized row if required, to an external app that's responsible for updating the PostgreSQL database. That might cause less load on the DB server.

The trouble with this approach, though, is that it might be hard to get right when transactions roll back. An alternative is to use an Oracle trigger that inserts records in a change tracking / audit table. You can then periodically read and clear the audit table, using that change history data to update the PostgreSQL database. This method has the advantage of being transaction safe, as data will never become visible in the audit table until the transaction making the changes has committed.

--
Craig Ringer

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance