Load data into Postgres from MySQL

Question

I have a database hosted on Amazon RDS. I am developing a Rails application with primary database as PostgreSQL. The Postgres database needs to be updated with data from RDS. I need to pull records from MySQL and then load it into Postgres.

Also, this is not database migrations.

One option is to create a secondary database (MySQL) in my rails application. But this does not seem a good option as I need to transfer data only when needed and not very frequently.

What is the best way to achieve this? Can this be done through pgloader?

UPDATE

This solved my issue. I just needed to read from MySQL and this is working fine. Are there any drawbacks for this approach?

Why not just export a CSV and then import it? If it isn't something done frequently, it might be best to go with a simple solution. It could also be easily automated if it needs to happen on a regular basis. — Sloan Thrasher
– Sloan Thrasher, Commented Apr 28, 2017 at 4:51
Can I export to CSV from within Rails while using Postgres as the primary database and without setting a secondary MySQL database? — nishant
– nishant, Commented Apr 28, 2017 at 5:12
Either connect to the mysql database from your rails application directly and pull whatever data you need or develop an api for the mysql database and use that. — Shadow
– Shadow, Commented Apr 28, 2017 at 5:17

mdag02 · Accepted Answer · 2017-04-28 07:23:38Z

0

You can use PostgreSQL's Foreign Data Wrappers : your MySQL tables will be viewed as PostgreSQL tables. See : https://www.postgresql.org/docs/current/static/postgres-fdw.html

answered Apr 28, 2017 at 7:23

mdag02

1,1759 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Load data into Postgres from MySQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related