Replicate from MySQL to PostgreSQL

Solved!
cuezumo
Level 2
Replicate from MySQL to PostgreSQL

Hi everyone,

I just established a connection with a database which uses @MySQL. However, I'd like to convert the tables to @PostgreSQL which is my preferred data processing tool. 
Does anybody know whether this is possible within Dataiku? 

In general there are lots of available tools: https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL

Maybe pg_chameleon might work with the @Python plug-in? https://pypi.org/project/pg-chameleon/

I appreciate all your suggestions, thanks a lot!

Best, cuezumo

0 Kudos
1 Solution
Alex_Combessie
Dataiker Alumni

Hi,

Would the "Change connection" feature help?

change_connection_larger.gif

Another option would be to use the Sync recipe from MySQL to PostgreSQL datasets. 

Cheers,

Alex

View solution in original post

0 Kudos
5 Replies
Alex_Combessie
Dataiker Alumni

Hi,

To best answer your question, could you please add some details on your context:

- have you set up connections in Dataiku DSS for both PostgreSQL and MySQL?

- do you want to migrate an existing Dataiku project to PostgreSQL? 

- or do you want to migrate all tables in MySQL to be migrated to PostgreSQL? (and then deprecate MySQL)

Cheers,

Alex

0 Kudos
cuezumo
Level 2
Author

hi Alex, 
thanks for your quick reply! 

To answer your questions: 
- the connections are set up only in MySQL. I'm planning to update the datasets daily via this connection.
- Yes, I'd like to migrate an existing project to PostgreSQL.
- No, the original MySQL datasets remain and will be updated on a daily basis. I'd like to run a daily scenario which updates those tables and replicates them to PostgreSQL, if possible. 

thank you very much!

best, cuezumo

0 Kudos
Alex_Combessie
Dataiker Alumni

Hi,

Would the "Change connection" feature help?

change_connection_larger.gif

Another option would be to use the Sync recipe from MySQL to PostgreSQL datasets. 

Cheers,

Alex

0 Kudos
cuezumo
Level 2
Author

Thanks for your suggestions, Alex.

ad change of connection: 
I get an error saying that the action is not supported. I can't choose any connection other than Filesystem and MySQL.

Screenshot 2020-03-19 at 15.18.37.png

ad Sync recipe: 

Again, I can only choose from Filesystem and MySQL. 

 

I'm not sure whether I need to change something in the settings, because I just started using these datasets. The connection was established yesterday. Any further clues what I might be doing wrong? 

Thanks a lot for your efforts!

 

 

 

0 Kudos
cuezumo
Level 2
Author

Ah, I'm missing a postgreSQL based connection where I can direct the recipe to. 
That should solve it, thanks for your help! 

best, cuezumo