synching two databases in heroku rails app

Question

I have crm rails application deployed in heroku so my database is postgresql. I have an iphone app which is using mysql database. Now what i need to do is to synch my iphone database with my heroku database.

Can anyone please help me on this is it possible to sync two different databases in heroku

Thanks in Advance

It all depends on what you mean when you say "different" databases. Are they different in structure, or just data? — Richard Peck, Aug 27 '14 at 10:08
@RichPeck assume that they have same structure but using different technology one is mysql and another one is postgresql. any solution? — anusha, Aug 27 '14 at 10:10
@RichPeck Means is it complex to do Please give your thoughts — anusha, Aug 27 '14 at 10:16
@RichPeck initially contains less data but it might increase. Assume 3 to 4 tables with thousands of records in it — anusha, Aug 27 '14 at 10:18
It's hard to answer a question like that. You should try dumping the DB to sql and importing it not the other side. There will be different problems but it depends on what DB-specific things you used in Postgres. — Mike Szyndel, Aug 27 '14 at 10:35
One issue definitely is that MySQL doesn't have boolean type so you need to change the SQL file to use tinyint. — Mike Szyndel, Aug 27 '14 at 10:36
Yep - that's the way I did it back in when we migrated from Heroku - I'll write an answer in a minute for you! — Richard Peck, Aug 27 '14 at 10:36

score 0 · Answer 1 · edited May 23 '17 at 12:27

The way we did this was to do it manually

Yep, it's a real pain, but at least you know it's done correctly

--

Process

For migrating from Postgres to MYSQL is as follows:

"Backup" the database using pg_backups

Download the backup locally

In MYSQL (we used PHPMyAdmin), create a series of SQL calls to insert the respective data into the correct tables

Migrating from MYSQL to Postgres is, as much as I can determine, a similar process:

Download the SQL dumps of your MYSQL DB (I only know how to do this in PHPMyAdmin)

Configure those files to be "PG" ready

Send the SQL to your Heroku postgres db's

--

Notes

For specifics, I only have the following:

If you dump from Heroku, you'll not get a traditional SQL file; you'll receive a .dump file. To change this, you'll need to use pg_restore in the following way:

$ cd c:/your/postgres/install/pg_restore/location
$ pg_restore path_to_your_dump.dump > path_to_your_sql.sql

This will give you the ability to "read" the file, uploading to your db & importing as you wish. That's when the fun begins :)

Importing MYSQL

The way to import MYSQL into a PG db will have to be done manually

Although we've never done this directly (we only migrated from PG), there are several things you can do. Firstly, you can use the PG2MYSQL converter to give you an idea as to the syntax differences between MYSQL & PG

Then I would perform a "dump" from MYSQL in SQL format. This will give me the data required for PG. This will allow me to insert into my PG database (you might have to do this locally to give you the ability to determine the syntax), from which you can then patch in the MYSQL data

I understand it's not the most elaborate explanation - I hope it gives you some ideas

Sorry, I have just seen your answer, so that means we need do it manually. can't it be done programmatically — anusha, Aug 28 '14 at 02:57

synching two databases in heroku rails app

1 Answers1