Thread: Oracle to PostgreSQL Migration

Hi,

Hope everyone is fine.

Can someone help or guide regarding Open Source tools for Oracle to PostgreSQL migration with real time CDC. along with this is there any possibility to change the structure of the database? Let me explain a little more,

We have an Oracle DB which is around 1TB and we want to migrate to PostgreSQL that have a new table structure, so we want to perform data transformation and real time CDC from Oracle to PostgreSQL. Do we have any good open source tool to achieve this with No Coding involved.??

Thanks.

Regards,

Inzamam Shafiq

Sr. DBA

Is there any practical limit on the number of schemas in a database?

Will the number of schemas in a user's search path impact performance?

Thanks

Dave Roth

On 03/20/2023 10:15 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

Real-time CDC is the difficult part.  ora2pg (using views) can do a static migration.  No coding (unless you consider clever use of bash to modify config files to be coding).  I used it to migrate a 7TB db to Postgresql.

https://ora2pg.darold.net/

On Mon, Mar 20, 2023 at 8:58 AM Inzamam Shafiq <inzamam.shafiq@hotmail.com> wrote:

Hi,

Hope everyone is fine.

Can someone help or guide regarding Open Source tools for Oracle to PostgreSQL migration with real time CDC. along with this is there any possibility to change the structure of the database? Let me explain a little more,

We have an Oracle DB which is around 1TB and we want to migrate to PostgreSQL that have a new table structure, so we want to perform data transformation and real time CDC from Oracle to PostgreSQL. Do we have any good open source tool to achieve this with No Coding involved.??

Thanks.

Regards,

Inzamam Shafiq

Sr. DBA

> david.g.johnston@gmail.com wrote:
>
>> adaptron@comcast.net wrote:
>>
>> Is there any good reference to explain the best usage of each of these structures. I am coming from Oracle.  What is the best analog to Oracle's "user".
>
> A schema is a namespace mechanism for objects.  It has no relationship to roles aside from the possibility, if you so choose, to define a schema to have the same name as a role, in which case that schema becomes parts of that role's default search_path.
>
> There is no low-level difference between role and user.  A user is a role with the login privilege.

I came from Oracle, too. I soon came to see that these facts about PG are an improvement on Oracle Database:

— In ORCL, "user" and "role" are distinct notions but in PG they collapse into one. This means that the nodes in a PG role hierarchy can all own objects. And schemas are among these owned objects.

— In ORCL, "user" and "schema" are 1:1 and so, informally, collapse into a single notion. In PG, a role can own many schemas and this can be used to advantage as a classification scheme for objects with the same owner.

However, there's more to say.

— "set role" (to a role upon which the current role is a grantee) has to be re-learned. For example, it cannot be governed by a required password. And it has the same effect on "current_role" (versus "session_role") that a "security definer" subprogram has (but with no push-pop notion).

— It's not enough to say, for example, "grant select on table s1.t to r2" (when s1.t is owned by, say, r1 and the schema s1 is not owned by r2). You have, at a coarser granularity, to also say "grant usage on schema s1 to r2". (This is nice because you can prevent r2 from using any of r1's objects with just a single "revoke".)

— The "search_path" notion sounds at first to be appealing. And, loosely, it makes up for the fact that PG has no synonym notion. However, just as in ORCL there's a whole discussion about how nefarious actors can capture a synonym with a bogus local object, so is there a similar discussion in PG about nefarious misuse of redefining the search path (no privilege governs this). This discussion is further complicated by the fact that "pg_temp" and "pg_catalog" are inevitably on the search path whether or not you mention them (and that when you don't, their positions in the search order is surprising). My personal conclusion is that you must always use a schema-qualified identifier for all objects in real application code (verbosity notwithstanding). This is rather like the ORCL practice never to create synonyms and to refer to ORCL-shipped objects as "sys.dbms_output" and the like.

— Closely related, a freshly-created database has a "public" schema (unless you customize the "template1" database to change this. This is very useful for ad hoc testing when you're learning something, But it's a nuisance in the database that a serious application uses.

— Having said this, a caveat is probably needed for "pg_catalog" objects because even common-or-garden objects like the "+" operator are implemented ordinarily via various objects in the "pg_catalog" schema. And the syntax for invoking an operator using a schema-qualified identifier is baroque:

select ((2+3) operator(pg_catalog.=) (1+4))::text;

I decided, eventually, to use schema-qualified identifiers for everything except for "pg_catalog" objects and always to set the search path thus:

set search_path = pg_catalog, pg_temp;

and especially always to use that list as an attribute in a subprogram's source code.

Hi Thomas,

We have tried kafka, but in that we stuck in CDC part, update/delete was not working due to some NULL value issue. Do you have any helping material for Oracle to PostgreSQL data migration using debezium?

Regards,

Inzamam Shafiq

Sr. DBA

---

From: Thomas Kellerer <shammat@gmx.net>
Sent: Monday, March 20, 2023 7:21 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Oracle to PostgreSQL Migration

 

Inzamam Shafiq schrieb am 20.03.2023 um 13:57:
> We have an Oracle DB which is around 1TB and we want to migrate to
> PostgreSQL that have a new table structure, so we want to perform
> data transformation and real time CDC from Oracle to PostgreSQL. Do
> we have any good open source tool to achieve this with No Coding
> involved.??


You could try debezium, but I don't know how good it works and if it qualifies as "no coding involved"

Hi,

Hope everyone is fine.

Can someone help or guide regarding Open Source tools for Oracle to PostgreSQL migration with real time CDC. along with this is there any possibility to change the structure of the database? Let me explain a little more,

We have an Oracle DB which is around 1TB and we want to migrate to PostgreSQL that have a new table structure, so we want to perform data transformation and real time CDC from Oracle to PostgreSQL. Do we have any good open source tool to achieve this with No Coding involved.??

Thanks.

Regards,

Inzamam Shafiq

Sr. DBA

EDB do have a replication server which can be used to transfer real time data from oracle to postgres.

don't know if it can be used to get to "no downtime"

BTW what do you call "no downtime" as anyway a switch, as fast as it can be do take ""some"" time ?

Marc MILLAS

Senior Architect

+33607850334

www.mokadb.com

On Mon, Mar 20, 2023 at 1:58 PM Inzamam Shafiq <inzamam.shafiq@hotmail.com> wrote:

Hi,

Hope everyone is fine.

Can someone help or guide regarding Open Source tools for Oracle to PostgreSQL migration with real time CDC. along with this is there any possibility to change the structure of the database? Let me explain a little more,

We have an Oracle DB which is around 1TB and we want to migrate to PostgreSQL that have a new table structure, so we want to perform data transformation and real time CDC from Oracle to PostgreSQL. Do we have any good open source tool to achieve this with No Coding involved.??

Thanks.

Regards,

Inzamam Shafiq

Sr. DBA