Read from database, write to another database, simultaneously

[Paul Boddie]

Sean Davis wrote:
>
> As for the specifics, Oracle data is going to be coming in as a DB-API
> 2 cursor in manageable chunks (and at a relatively slow pace).  On the
> postgres loading side, I wanted to use the pscycopg2 copy_from
> function, which expects an open file-like object (that has read and
> readline functionality) and is quite fast for loading data.

And which seems to use the COPY FROM command in PostgreSQL...

> Note the disconnect here--Oracle is coming in in discrete chunks, while
> postgresql is looking for a file object.  I solved this problem by
> creating a temporary file as an intermediary, but why wait for Oracle
> to finish dumping data when I can potentially be loading into postgres
> at the same time that the data is coming in?

My experience along with the PostgreSQL documentation tells me that you
shouldn't worry about this problem too much: using COPY FROM is *far*
faster than performing many inserts or updates. The only thing you'd
need to worry about is data being copied into the database that
duplicates existing data, causing constraint violations, but since
you're already using this method I imagine that this is not a likely
problem.

Paul