Re: migrate from PostgreSQL to Oracle

On Thu, 25 Oct 2012 15:29:29 +0200, jo wrote:

> Hi all,
>
> I'm working with same db schema in PostgreSQL and Oracle,
> We mainly work in PostgreSQL but sometimes we need to copy schema and
> data from pg to oracle because some our customers want to use oracle
> instead of pg.
>
> Thus I'm looking for some linux script to migrate from pg to oracle.
> At the moment, I dump data from pg using pg_dump in the format:
> INSERT INTO table (columns) (values)
> then I load it into the Oracle db using cx_Oracle this procedure is so
> slow, and sometimes I have to edit and modify data manually, because
> some INSERT format aren't compatible.
> Is there any interesting linux script to do this more easily? something
> like the ora2pg script.

If the schemata are truly the same, I'd use 'copy to' a CSV file on the
Postgres side, then SQL*Loader to put it into Oracle. Easily scriptable.

For maximum speed, use 'copy to on the server side; *not* psql, and
(assuming that the data is clean), direct path for SQL*Loader.