Re: Oracle data to PG - Mailing list pgsql-admin

On 7/28/22 03:55, Dischner, Anton wrote:

@font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.E-MailFormatvorlage17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif; mso-fareast-language:EN-US;}div.WordSection1 {page:WordSection1;}ol {margin-bottom:0cm;}ul {margin-bottom:0cm;}

Hi Team,

 

we are searching for a way to import a dump from an Oracle Installation into PostgreSQL.

 

Ist not administrated by us, so foreing table or connect as an user (as ora2pg seems to need) is not an option.

 

We had a look into https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative

especially:

• Ora2pg—a robust migration tool that connects to an Oracle database,  extracts schemas and tables and generates SQL scripts that can be loaded into Postgres.
• Ora_migrator—an extension that  uses  an oracle_fdw foreign data wrapper to extract data from an Oracle database. 
• Orafce—this extension allows you to implement a number of Oracle functions in Postgres.  It also provides support for Oracle date formatting and additional Oracle data types.
• EDB Migration Portal—a web-based service for migrating from Oracle to EDB Postgres Advanced Server that features Assessment, Schema conversion and Compatibility reporting. 

we have to import data only.

No indexes, PL/SQL or other stuff.

 

Maybe it makes sense to install an Oracle Instance, import the data and then connect as a user and do the conversion.

 

What are you hints and experiences to plan and accomplish this task?

We successfully used ora2pg to extract 5TB of LOB-heavy tables out of Oracle.  Each SQL files generated was a bunch of COPY statements in stdin format.

It's multi-process, can extract from views, and tunable based on the size of the LOBs.