topic How to migrate multiple schema data from oracle db to postgres in Talend Studio

How to migrate multiple schema data from oracle db to postgres

Anonymous — Sat, 16 Nov 2024 02:06:18 GMT

How can we migrate around 1900 tables which are in different schema in single database in oracle to postgres schema,
which is created for migrating these tables in talend 7.2.
What approach should we follow as we have to create the table structure as well in postgres using talend and each time table
column will be different and also have multiple schemas in source .Approx data of 1900 tables will be 9 tb.
Also what type of complexity we will face.

Thanks in Advance!!

Re: How to migrate multiple schema data from oracle db to postgres

TRF — Sun, 21 Jun 2020 09:45:54 GMT

As suggested in your previous post, think the approach of dynamicaly build "CREATE TABLE" requests based on Oracle's ALL_TAB_COLUMNS content.

This way, you can build on the fly the desired "CREATE TABLE" statements for all desired tables.

This link may helps.

Then, as you can't have a predetermined schema for each table, use the same approach to extract data from Oracle source tables and redirect result to a CSV file per table.

This llink may helps.

Finally, the same method may help to generate COPY statements to import data into target tables.

You may also use an external tool such as PgFutter to create tables and import data from CSV files.

All the steps can be orchestrated by your Talend job.

Enjoy!

Re: How to migrate multiple schema data from oracle db to postgres

JohnRMK — Mon, 22 Jun 2020 14:13:04 GMT

Hello,
If you have a Talend enterprise licence, then it will be easy to complete your work.
You can do this in 3/4 Jobs which will use the dynamic schema offered by Talend.
-Recover all tables from source (SQL query Oracle Table Name in tDBInput )
-Recover each column and build table creation requests (make convertion to Postgres with a routines) in the target (tDBInput-->tMap -->tJava-->tDBRow Get all columns in Oracle Table )
-Iterate on the name of the tables and recover the data with the dynamical schema
You can see a link below that does the same migration work

Dynamic migration and Routines Convert
If you have other questions, do not hesitate to contact me