Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 4AB422E0040 for ; Mon, 28 Apr 2008 18:37:55 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 48286-01 for ; Mon, 28 Apr 2008 18:37:48 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.230]) by developer.postgresql.org (Postfix) with ESMTP id 302122E002D for ; Mon, 28 Apr 2008 18:37:47 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id b25so3317629rvf.43 for ; Mon, 28 Apr 2008 14:37:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=na/9ChV3SvvuC8goCE4up3oMoZboR9bpE06qpIEbm44=; b=Jw1H4+CxlZMIkLD16vcHxb8fcAsq9xcSEYyls9OXtwwq5rlW4RdFoWfOIPVnjXP+2fzDqyn2sF9GxDYNpy18sbIYZGEJBSmEZH4WZbezDRnGKMTqDgtdodtEWxWiUYaU5zTpVdzOzbNIjq54iV+0jcfJphv0TWIbiFFUB1YWKk4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=uYOaxnPJHODiAQtmnCGJy5b12wDIDTAG3iM2lxKkhZbTa5lU81TGlKEWsbahjrR49ZcL03Q2yOw7F5e6spKAJjT/Kf5yT6xtwYckZ/fOkpmjpSvl1mBxKwrPbIHcddZJV6DKxiD66cRCBpnAjOO4sOfi5dya2o+rTKHrvmpppb0= Received: by 10.141.76.21 with SMTP id d21mr2847167rvl.242.1209418666528; Mon, 28 Apr 2008 14:37:46 -0700 (PDT) Received: by 10.141.179.19 with HTTP; Mon, 28 Apr 2008 14:37:46 -0700 (PDT) Message-ID: <8a5d3c890804281437u6bd655b3k9a38a77a49dc5552@mail.gmail.com> Date: Mon, 28 Apr 2008 18:37:46 -0300 From: "Adonias Malosso" To: "Jonah H. Harris" Subject: Re: Best practice to load a huge table from ORACLE to PG Cc: pgsql-performance@postgresql.org In-Reply-To: <36e682920804261814w3508b232n6cf935874b19bf31@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5855_26718264.1209418666552" References: <8a5d3c890804260625o7ad0700fp79f951c58c242720@mail.gmail.com> <36e682920804261814w3508b232n6cf935874b19bf31@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/417 X-Sequence-Number: 29910 ------=_Part_5855_26718264.1209418666552 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Jonah, Thank you for the answer. Good to know about this enterprise DB feature. I=B4ll follow using pgloader. Regards. Adonias Malosso On Sat, Apr 26, 2008 at 10:14 PM, Jonah H. Harris wrote: > On Sat, Apr 26, 2008 at 9:25 AM, Adonias Malosso > wrote: > > I=B4d like to know what=B4s 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/ > ------=_Part_5855_26718264.1209418666552 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Jonah,
 
Thank you for the answer. Good to know about this enterprise DB featur= e.
 
I=B4ll follow using pgloader.
 
Regards.
 
Adonias Malosso


------=_Part_5855_26718264.1209418666552--