Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I move data from Postgres to MySQL running on Amazon's RDS?

I need to move a database with a Django schema from Postgres to MySQL, running on Amazon's RDF. I can re-generate the tables using manage.py, but I'm still looking for a way to migrate over all of the row data. Does anyone know a clean way of moving it over? Are there any gotchas to watch out for with Amazon's RDF?

like image 927
Andrey Fedorov Avatar asked Oct 28 '09 21:10

Andrey Fedorov


People also ask

Does Amazon use MySQL or PostgreSQL?

Amazon Aurora is a relational database management system (RDBMS) built for the cloud with full MySQL and PostgreSQL compatibility.

Can we import data into an RDS instance?

To import data from an existing database to an RDS DB instance: Export data from the source database. Upload the exported data. Import the uploaded data into an RDS DB instance.


2 Answers

Django also has the dumpdata and loaddata commands in manage.py. Process would be

  1. syncdb in MySQL to prepare the tables
  2. dumpdata from PostgreSQL
  3. loaddata from the previous command into the new MySQL instance
like image 134
John Paulett Avatar answered Oct 14 '22 04:10

John Paulett


If you backup your Postgresql database and choose the "Insert Commands" option you will end up with a text file of SQL insert statements that in theory you can run against a different SQL database, in this case MySQL. In practice it's going to depend on the database types that you are using since not all Postgresql and MySQL data types map directly to each other.

The gotcha for me with Amazon's RDS is the statement that there will be a 4 hour window each week where the database needs to be taken down for patching and maintenance. The hard thing with relational databases is making them fault tolerant with replication and clustering. At the moment Amazon's RDS doesn't solve either of those issues and looks like it's simply using an EC2 instance to host a MySQL server which is something that's been possible to do for a long time without RDS.

Personally I love the direction RDS is heading though and look forward to the day I can treat a relational database as a black box and not have to worry about fault tolerance and scalability, like the Amazon SimpleDB product is now.

like image 38
sipsorcery Avatar answered Oct 14 '22 06:10

sipsorcery