Message-ID: <ee5a27d790ac43d4358b6353f37d09eee4214bbf.camel@cybertec.at>
Subject: Re: Exporting TBs of data in Oracle schema/database into CSV format
 to migrate into PostgreSQL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pepe TD Vo <pepevo@yahoo.com>, pgsql-admin@lists.postgresql.org, Thomas
	Kellerer <shammat@gmx.net>
Date: Mon, 20 Apr 2020 16:39:39 +0200
In-Reply-To: <172687860.2288957.1587391373846@mail.yahoo.com>
References: 
	<CAOLrvP6sSTH2Mq5q29-9Jdk3JrR-PGT5WE+eN=8xnqKg8vnvbw@mail.gmail.com>
	 <c5b9987a-ad9e-ce37-d734-2fb38d1d7330@gmx.net>
	 <172687860.2288957.1587391373846@mail.yahoo.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Mon, 2020-04-20 at 14:02 +0000, Pepe TD Vo wrote:
> we can access Oracle table within Postgres?  How?  Need to create a database link
> and/or how can both talk to each other?

That would be the Oracle foreign data wrapper:
https://laurenz.github.io/oracle_fdw/

> My data is from production, can turn off the foreign key.  Is there a way to copy
> data from Oracle to Postgres faster?  Should I export the Oracle data table via
> dump file as text/csv and insert it into Postgres?  Can we use an oracle dump file?

You cannot use an Oracle dump, because that is in a proprietary format.

Using the foreign data wrapper, you define a foreign table in PostgreSQL.
When you select from that table, the data are directly fetched from Oracle.

Then you can do

   INSERT INTO localtable SELECT * FROM foreign_table;

to transfer the data, without any intermediary file storage.
In a way, the data are streamed from Oracle to PostgreSQL.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com