To: pgsql-admin@postgresql.org
From: Walter Hurry <walterhurry@gmail.com>
Subject: Re: migrate from PostgreSQL to Oracle
Date: Thu, 25 Oct 2012 17:58:50 +0000 (UTC)
Lines: 23
Message-ID: <k6bukq$nqd$1@ger.gmane.org>
References: <50893EB9.70306@sferacarta.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: Pan/0.135 (Tomorrow I'll Wake Up and Scald Myself with Tea; GIT
	30dc37b master)

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.