Re: 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 ?
>
> I am assuming you can use the java trigger from oracle to load the
> postgresql jdbc driver, make a connection to postgresql and perform
> whatever statement needed to be done.

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