From: Adonias Malosso <malosso@gmail.com>
To: Jonah H. Harris <jonah.harris@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Best practice to load a huge table from ORACLE to PG
Date: Mon, 28 Apr 2008 18:37:46 -0300
Message-ID: <8a5d3c890804281437u6bd655b3k9a38a77a49dc5552@mail.gmail.com> (raw)
In-Reply-To: <36e682920804261814w3508b232n6cf935874b19bf31@mail.gmail.com>
References: <8a5d3c890804260625o7ad0700fp79f951c58c242720@mail.gmail.com>
<36e682920804261814w3508b232n6cf935874b19bf31@mail.gmail.com>
Jonah,
Thank you for the answer. Good to know about this enterprise DB feature.
I´ll follow using pgloader.
Regards.
Adonias Malosso
On Sat, Apr 26, 2008 at 10:14 PM, Jonah H. Harris <jonah.harris@gmail.com>
wrote:
> On Sat, Apr 26, 2008 at 9:25 AM, Adonias Malosso <malosso@gmail.com>
> wrote:
> > I´d like to know what´s the best practice to LOAD a 70 milion rows, 101
> > columns table
> > from ORACLE to PGSQL.
>
> The fastest and easiest method would be to dump the data from Oracle
> into CSV/delimited format using something like ociuldr
> (http://www.anysql.net/en/ociuldr.html) and load it back into PG using
> pg_bulkload (which is a helluva lot faster than COPY). Of course, you
> could try other things as well... such as setting up generic
> connectivity to PG and inserting the data to a PG table over the
> database link.
>
> Similarly, while I hate to see shameless self-plugs in the community,
> the *fastest* method you could use is dblink_ora_copy, contained in
> EnterpriseDB's PG+ Advanced Server; it uses an optimized OCI
> connection to COPY the data directly from Oracle into Postgres, which
> also saves you the intermediate step of dumping the data.
>
> --
> Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
> EnterpriseDB Corporation | fax: 732.331.1301
> 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
> Edison, NJ 08837 | http://www.enterprisedb.com/
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: malosso@gmail.com, jonah.harris@gmail.com
Subject: Re: Best practice to load a huge table from ORACLE to PG
In-Reply-To: <8a5d3c890804281437u6bd655b3k9a38a77a49dc5552@mail.gmail.com>
References: <8a5d3c890804260625o7ad0700fp79f951c58c242720@mail.gmail.com> <36e682920804261814w3508b232n6cf935874b19bf31@mail.gmail.com> <8a5d3c890804281437u6bd655b3k9a38a77a49dc5552@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox