Re: migrate from PostgreSQL to Oracle
- From: Mihajlo Tekic <mihajlo.tekic@xxxxxxxxx>
- To: jose.soares@xxxxxxxxxxxxxx
- Date: Wed, 24 Oct 2012 10:36:42 -0500
Hi Jose,
You can use COPY command to dump the data into csv file, then use something
like Mark described (external tables) to load data into your database. I
think COPY command works on table level, hence in order to export all
tables you may need to do some scripting to make it easier.
http://wiki.postgresql.org/wiki/COPY
What Postgres distribution you use? If it is Enterprise DB Postgres Plus
Advanced Server (commercial), they have Oracle Compatibility package (I
think it is available only with Advanced Server which requires a license)
. I am pretty certain you could use it to copy data between the databases.
http://www.enterprisedb.com/solutions/oracle-compatibility-technology
When you say "migrate" do you actually mean migrating entire Postgres
database or, just copying certain tables? If you want to migrate entire
database, moving data would be the easy part :-) it would be much bigger
challenge to migrate PL/pgSQL objects(procedures, functions,triggers, etc),
if any, since you may need to actually rewrite some of the code.
~Mihajlo
On Wed, Oct 24, 2012 at 9:29 AM, jo <jose.soares@xxxxxxxxxxxxxx> wrote:
> Yes Igor,
> I don't have any problems with PostgreSQL, I know how to dump a pg
> database,
> my problem is how to upload the pg dump file into Oracle.
>
> j
>
>
> Igor Neyman wrote:
> > Postgres is definitely capable of dumping data into flat (CSV) file,
> > using COPY command.
> >
> > Another option is direct connection between Oracle and Postgres using
> > Heterogeneous Services (ODBC).
> > I used it successfully to do conversion, though in opposite direction:
> > from Oracle to Postgres.
> >
> > Regards,
> > Igor Neyman
> >
> > On Wed, Oct 24, 2012 at 3:38 AM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx
> > <mailto:Mark.Bobak@xxxxxxxxxxxx>> wrote:
> >
> > Jo,
> > I'm not at all familiar with pg or pg_dump, however, if pg is
> > capable of efficiently dumping data to flat files, my first
> > suggestion would be to write a formatted text file, perhaps csv,
> > and use Oracle external table to bulk load. Insert /*+ append */
> > into oracle_table select * from external_table would do it.
> >
> > Also, gives you opportunity to use nologging and do any necessary
> > data transformations on the fly by adding functions on columns in
> > select list, and finally, to do index builds (also nologging)
> > after data loads.
> >
> > This approach should be pretty performant.
> >
> > Hope that helps,
> >
> > -Mark
> > Sent from my Samsung Galaxy Note?, an AT&T LTE smartphone
> >
> >
> >
> > -------- Original message --------
> > Subject: migrate from PostgreSQL to Oracle
> > From: jo <jose.soares@xxxxxxxxxxxxxx
> > <mailto:jose.soares@xxxxxxxxxxxxxx>>
> > To: ORACLE-L <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx
> >>
> > CC: migrate from PostgreSQL to Oracle
> >
> >
> > Hi all,
> >
> > 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?
> >
> > thanks
> > j
> >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Jose Soares Da Silva _/_/
> Sferacarta Net
> Via Bazzanese 69 _/_/ _/_/_/
> 40033 Casalecchio di Reno _/_/ _/_/ _/_/
> Bologna - Italy _/_/ _/_/ _/_/
> Ph +39051591054 _/_/ _/_/ _/_/ _/_/
> fax +390516131537 _/_/ _/_/ _/_/ _/_/
> web:www.sferacarta.com _/_/_/ _/_/_/
>
> Le informazioni contenute nella presente mail ed in ogni eventuale file
> allegato sono riservate e, comunque, destinate esclusivamente alla persona
> o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n.
> 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da
> parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La
> correttezza, l?integrità e la sicurezza della presente mail non possono
> essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di
> contattarci immediatamente e di eliminarla. Grazie.
>
> This communication is intended only for use by the addressee, pursuant to
> legislative decree 30 June 2003, n. 196. It may contain confidential or
> privileged information. You should not copy or use it to disclose its
> contents to any other person. Transmission cannot be guaranteed to be
> error-free, complete and secure. If you are not the intended recipient and
> receive this communication unintentionally, please inform us immediately
> and then delete this message from your system. Thank you.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
