Oracle to PostgreSQL Migration

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:

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"

On Mon, Mar 20, 2023 at 10:21 AM Thomas Kellerer <shammat@gmx.net> wrote:

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"

I've seen some good results using SymmetricDS
https://www.symmetricds.org/

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

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".
Thanks
Dave Roth

Hi

po 20. 3. 2023 v 17:08 odesílatel DAVID ROTH <adaptron@comcast.net> napsal:

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?

Sure, it should have an impact. When you use an unqualified identifier,
then the identifier is searched sequentially in schemas in the search path.
With a large number of schemas in the search path, the planning time can be
higher (and memory usage for cache of system objects can be higher too).

Regards

Pavel

Please don't hijack threads.

https://www.postgresql.org/docs/13/limits.html
No mention of schemas.

On Mon, Mar 20, 2023 at 12:08 PM DAVID ROTH <adaptron@comcast.net> wrote:

On Mon, Mar 20, 2023 at 9:13 AM DAVID ROTH <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.

David J.

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.

https://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema

On Mon, Mar 20, 2023 at 2:57 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

Hi Ron,

I have used ora2pg for a small database, but we have limitation of zero downtime, how do we replicate real time data from Oracle to PostgreSQL. We can migrate schema from Oracle to PostgreSQL using ora2pg and single time bulk data but what about the changed data after bulk load?

Regards,

Inzamam Shafiq
Sr. DBA
________________________________
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Monday, March 20, 2023 7:15 PM
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Oracle to PostgreSQL Migration

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<mailto: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

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"

Could oracle_fdw <https://github.com/laurenz/oracle_fdw&gt; help in your use
case? You could consider setting up a job to pull data into PostgreSQL live
and then cut over when you are ready.

- Umair

On Tue, Mar 21, 2023 at 10:36 AM Inzamam Shafiq <inzamam.shafiq@hotmail.com>
wrote:

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:

EDB has migration toolkit , which is very helpful in migration from Oracle
to EDB.

Thanks and Regards
Paramjib Baruah

On Tue, Mar 21, 2023 at 9:12 PM Marc Millas <marc.millas@mokadb.com> wrote:

Hi Umair,

Thanks, oracle_fdw can be used, but it doesn't provide real time sync, we have to schedule jobs to insert data in actual schema from foreign tables, so some delay might be there.

Regards,

Inzamam Shafiq
________________________________
From: Umair Shahid <umair.shahid@gmail.com>
Sent: Tuesday, March 21, 2023 3:48 PM
To: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Cc: Thomas Kellerer <shammat@gmx.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Oracle to PostgreSQL Migration

Could oracle_fdw<https://github.com/laurenz/oracle_fdw&gt; help in your use case? You could consider setting up a job to pull data into PostgreSQL live and then cut over when you are ready.

- Umair

On Tue, Mar 21, 2023 at 10:36 AM Inzamam Shafiq <inzamam.shafiq@hotmail.com<mailto:inzamam.shafiq@hotmail.com>> wrote:
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<mailto:shammat@gmx.net>>
Sent: Monday, March 20, 2023 7:21 PM
To: pgsql-general@lists.postgresql.org<mailto:pgsql-general@lists.postgresql.org> <pgsql-general@lists.postgresql.org<mailto: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"

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.??

To meet all of your requirements, you are almost certainly going to need to look at a commercial solution such as:

* EDB's xDB.
* Oracle's Golden Gate.
* FiveTran's HVR.

We've used xDB successfully in the past for this type of migration.

One open-source change capture tool that you might investigate is:

https://www.symmetricds.org

I personally don't have experience with it, but it might be suitable.

Best,
-- Christophe

On Fri, 2023-03-24 at 08:00 +0000, Inzamam Shafiq wrote:

Thanks, oracle_fdw can be used, but it doesn't provide real time sync, we have to
schedule jobs to insert data in actual schema from foreign tables, so some delay might be there.

In addition to the commercial tools that Christophe mentioned, and which might be
your best option, let me point out that ora_migrator
(https://github.com/cybertec-postgresql/ora_migrator), which is open source and based
on oracle_fdw, offers some degree of what you are looking for using a cursor-based soution.
Certainly net refined and certainly not feasible in cases with high transactional
workload, but perhaps it can be useful for you.

Yours,
Laurenz Albe

This is a little left field… but we’ve migrated a lot of onprem oracle
databases to Postgres (on aws rds) using aws database migration service -
which has worked very well and supports cdc.

I do wonder if you did the above and then add a 2nd cdc out from Postgres
rds to your onprem Postgres.
So onprem oracle -> rds Postgres -> onprem Postgres (all working
simultaneously)

(It wouldn’t work onprem oracle to onprem Postgres without a aws cloud
setup)

Just a random thought and fairly straightforward to setup.

On Tue, 28 Mar 2023 at 19:27, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Fri, 2023-03-24 at 08:00 +0000, Inzamam Shafiq wrote:

Thanks, oracle_fdw can be used, but it doesn't provide real time sync,

we have to

schedule jobs to insert data in actual schema from foreign tables, so

some delay might be there.

In addition to the commercial tools that Christophe mentioned, and which
might be
your best option, let me point out that ora_migrator
(https://github.com/cybertec-postgresql/ora_migrator), which is open
source and based
on oracle_fdw, offers some degree of what you are looking for using a
cursor-based soution.
Certainly net refined and certainly not feasible in cases with high
transactional
workload, but perhaps it can be useful for you.

Yours,
Laurenz Albe

--

Sent from iPhone