Sybase, MSSQL and Oracle to PostgreSQL Migration using Talend Open Studio?

[devx]

Hello All,

I am looking for some answers -

I want to know if it is possible to Migrate Databases and their Schemas from Sybase, MSSQL and Oracle to PostgreSQL using Talend Open Studio ?

We want to migrate the Data and the Schema - Table definitions, Triggers, indexes, Views and Stored Procedures.

Our databases are in Terabytes.

Would it be possible using Talend Open Studio ?

I also want to know what would be the throughput of migration - The speed of migration (Data per time), the Accuracy of migration and what is the largest database size which can be migrated ?

Thanks a lot !

[JohnRMK]

Hello,

The answer to your question is quite complicated but I will do my best. It's yes and no ! or yes with some limitation.

TOS remains a very powerful tool with a lot of connectors so yes you can migrate your databases.

After that, a limitation to Talend Open Studio is the fact that it does not include a dynamic schema (like the paid version) so, if you want to migrate a table, you have to manually define its mapping.

there is a question about the human resources used for this migration?

Quick advice, you can use Talend as a tool to orchestrate your flows.

For example :

- A job which connects to the system schema and retrieves the name of the schema , tables and columns. This job can then dynamically construct DDL queries for postgre (with columns type conversion)

- A second job which takes these metadata (table names and columns) and constructs shell / batch scripts to extract tables in CSV files and builds DML query for inserting data into the target database

the idea is to use the power of shell scripts or others and not the Talend read and write components. then for other database objects like triggers or stored procedures, you can use Talend components like tDBRow

Don't hesitate if you have specific questions

[rhefinity]

I have just completed testing a TALEND ETL that migrates databases from MS SQL to POSTGRESQL.

Tested with a number of database such as WorldwideImporters and AdventureWorks.

The ETL will migrate tables, views, data, indexes, constraints, sequences, generates columns.

The process will convert the MS SQL data types to comparable Postgresql data types.

All that is required is the target database to be set up, and then update a configuration file to provide the access connections to the source and target databases.

All schemas in the source database will be migrated, or you can selected them one at a time.

Looking to work with you to test it further with your database if you have the requirement to move to Postgresql.

​Email me if you are interested - richard@e-finityconsulting.com