Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oHPnR-0001Ki-8j for pgsql-admin@arkaria.postgresql.org; Fri, 29 Jul 2022 13:12:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oHPnP-0000Ok-VS for pgsql-admin@arkaria.postgresql.org; Fri, 29 Jul 2022 13:12:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oHPnP-0000NP-E7 for pgsql-admin@lists.postgresql.org; Fri, 29 Jul 2022 13:12:51 +0000 Received: from mail-qk1-x734.google.com ([2607:f8b0:4864:20::734]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oHPnM-0006sr-5h for pgsql-admin@lists.postgresql.org; Fri, 29 Jul 2022 13:12:50 +0000 Received: by mail-qk1-x734.google.com with SMTP id e12so958931qkl.2 for ; Fri, 29 Jul 2022 06:12:48 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=ILLlBfAXPWlYiPZN8TEOt0OxEg3Rh2iX51RvgmIm1u0=; b=TuO27Jig6SOrHaiQv+gsAszeuiz9mHdKVu+HayxkSYWutE4oHzndS6XlOBX/LJuIlK YoYsaWrGi4E5pmo4zke7piZG17GSafs/0KShM0e+CrS+D9RB8UbUzv8RNMo+Y+17DklQ CZr/ITZ1oo8llTZXdYlMMyWsQY9M9z/g+cRlo6zlRCnHXfPGW+oVmhDBvh1HMYV7ojrr x8loqiEAn6V6xCsCnUcs7owwt81A1fVxhNtqtIYgMiSxPqIDiTLeGy41FmgCFwbHmOvz iUi4REPeqS5vT+R7pEp0tO00aHmFTyulUJi9VK3bGypYH5+VkFvmEOegRc2p0qqP29FH lFlQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=ILLlBfAXPWlYiPZN8TEOt0OxEg3Rh2iX51RvgmIm1u0=; b=3Y8En7yRKKnN0JbWYREh+vm/B2U6lKWqblzLvzSqeAPaTW4wgvioGcKhQJI6Cc6Lpa /5s24+8Nh0sCudJyv9U7lENF7KaHHlYvp0kVovKHhsBnZpjYrSj4eAkqv+LON96m4anx r9hUribDcKN/szbTCTQ9ufHOv9lie4MDoHE0AcWw39usw8fnuP6CjJagYZ/xJhcscqka ChquA1kgLabqo1sYqXoeEnCTd8TQGSxQMJ3YNP6YQyvoB4rtMiKq/Ty2zvHg9hX6FUK9 QK3aBcy8d1DqIJ8MnKql5lAyeHGRek2op+dHwrZx28aCUTF1TJ+rlfMD91tAcQJpU+wV yMRg== X-Gm-Message-State: AJIora/dOWaufKbIP4hQLW48RfLktoSJKBj/erKTWv3jOpUWgbgXgavn Ixt4Fkf1jfkyGrA38xeTCjaTKj5uEooCZg== X-Google-Smtp-Source: AGRyM1uTjFQx++XvUxoBw62rmkS3EJIK+DkI6EuHsWCQxpQspCcRcPQ095l9eQJgVhgVbJjQI+wkig== X-Received: by 2002:a05:620a:469e:b0:6b8:705b:9a5a with SMTP id bq30-20020a05620a469e00b006b8705b9a5amr891647qkb.371.1659100366425; Fri, 29 Jul 2022 06:12:46 -0700 (PDT) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id k5-20020a05620a414500b006af08c26774sm2721291qko.47.2022.07.29.06.12.45 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 29 Jul 2022 06:12:46 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------2v4y8zn738Pe0MIn3tKFUHS8" Message-ID: <1ddfbc1c-9ee1-547d-62cf-afce6c070670@gmail.com> Date: Fri, 29 Jul 2022 09:12:45 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.11.0 Subject: Re: Oracle data to PG Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <31df7c7aa56a44cf8e23aca8b3f80513@MITMB5.helios.med.uni-muenchen.de> From: Mladen Gogala In-Reply-To: <31df7c7aa56a44cf8e23aca8b3f80513@MITMB5.helios.med.uni-muenchen.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------2v4y8zn738Pe0MIn3tKFUHS8 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 7/28/22 04:55, Dischner, Anton wrote: > > 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? > > best > > Anton > Hi Anton, Long time no see. Ora2pg can produce just the "CREATE TABLE/INDEX/VIEW" statements from Oracle, translated to PgSQL vernacular. You will have to be able to log onto the original Oracle instance, albeit as an application owner. Furthermore, Oracle has a free ODBC gateway which can be used to connect to Postgres. What you need is an Oracle listener, not an entire instance. Another thing that can be exceedingly useful is this: https://osalvador.github.io/ReplicaDB/ You can extract Oracle data into CSV files and do COPY in PgSQL. I know it works with Snowflake. There is no reason for it not to work witg PgSQL as well. You will need some scripting, but it can be done. Be aware of some key differences: Oracle doesn't store NULL values in indexes. PostgreSQL does. The corollary is different behavior with the unique, multi-column indexes. There are many more index types in PgSQL, I find Bloom indexes exceptionally useful. Good luck! -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------2v4y8zn738Pe0MIn3tKFUHS8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 7/28/22 04:55, Dischner, Anton wrote:

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?

 

best

 

Anton

 

 

 

 

Hi Anton,

Long time no see. Ora2pg can produce just the "CREATE TABLE/INDEX/VIEW" statements from Oracle, translated to PgSQL vernacular. You will have to be able to log onto the original Oracle instance, albeit as an application owner.

Furthermore, Oracle has a free ODBC gateway which can be used to connect to Postgres. What you need is an Oracle listener, not an entire instance. Another thing that can be exceedingly useful is this: https://osalvador.github.io/ReplicaDB/

You can extract Oracle data into CSV files and do COPY in PgSQL. I know it works with Snowflake. There is no reason for it not to work witg PgSQL as well. You will need some scripting, but it can be done.

Be aware of some key differences: Oracle doesn't store NULL values in indexes. PostgreSQL does. The corollary is different behavior with the unique, multi-column indexes. There are many more index types in PgSQL, I find Bloom indexes exceptionally useful.

Good luck!

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------2v4y8zn738Pe0MIn3tKFUHS8--