Share this page: Follow me to be notified of new articles:

PHP_my2pg

What is it?

At my company, we have about 12Gb of data in our database. That's about 50,000,000 SQL entries. I created this script to migrate our data from MySQL to PostgreSQL in order to test our websites with PostgreSQL. This is not the script we used in the production environment and it was not created for that purpose. However, we did not encounter any issues using it.
Out of security, you should back up your MySQL and your PostgreSQL database before using this tool or any other migration tool.
This script is intended to be launched from a terminal (CLI) and not a browser.
Again, I do not have the pretension of creating a migration tool suitable for production; please be careful using this tool.

What do I need?

  1. PHP 4/5
  2. A MySQL database
  3. A PostgreSQL database

What can I do with the source code?

What ever you want. (learn more)
Would you like to improve it? If you want, send me the updated version and if you want, I'll publish it here. (contact me)

Changelog

Source code

Output example

Here is an output example:

* MySQL connection... * PostgreSQL connection... * Selection of MySQL database "my_db"... * Connection of the PostgreSQL database "my_db"... * List of MySQL tables in "my_db"... ... ** Table "my_table_1" *** There is 1,216 entries in this MySQL table. *** The table exists in the PostgreSQL db... *** There is 15,114 entries in the PostgreSQL table. *** Truncate of the PostgreSQL table... *** There is 1,216 entries to import... *** Retrieve the content from the MySQL table... *** Starting import entries from "my_table_1"... *** ...100 lines inserted in "my_table_1" *** ...200 lines inserted in "my_table_1" *** ...300 lines inserted in "my_table_1" *** ...400 lines inserted in "my_table_1" *** ...500 lines inserted in "my_table_1" *** ...600 lines inserted in "my_table_1" *** ...700 lines inserted in "my_table_1" *** ...800 lines inserted in "my_table_1" *** ...900 lines inserted in "my_table_1" *** ...1,000 lines inserted in "my_table_1" *** ...1,100 lines inserted in "my_table_1" *** ...1,200 lines inserted in "my_table_1" *** There is now 1,216 entries in the PostgreSQL table. *** We are done with the table "my_table_1"! How cool is that? ... ** Table "my_table_2" *** There is 572,493 entries in this MySQL table. *** The table exists in the PostgreSQL db... *** There is 572,493 entries in the PostgreSQL table. *** ...the tables seems identical! *** ...we SKIP this table! ... ** Table "my_table_3" *** There is 558 entries in this MySQL table. *** The table exists in the PostgreSQL db... *** There is 558 entries in the PostgreSQL table. *** ...the tables seems identical! *** ...we SKIP this table! ... ** Table "my_table_4" *** There is 26,555 entries in this MySQL table. *** The table does not exists in the PostgreSQL db. *** Creation of the table in PostgreSQL... *** There is 26,555 entries to import... *** Retrieve the content from the MySQL table... *** Starting import entries from "my_table_4"... *** ...1,000 lines inserted in "my_table_4" *** ...2,000 lines inserted in "my_table_4" *** ...3,000 lines inserted in "my_table_4" *** ...4,000 lines inserted in "my_table_4" *** ...5,000 lines inserted in "my_table_4" *** ...6,000 lines inserted in "my_table_4" *** ...7,000 lines inserted in "my_table_4" *** ...8,000 lines inserted in "my_table_4" *** ...9,000 lines inserted in "my_table_4" *** ...10,000 lines inserted in "my_table_4" *** ...11,000 lines inserted in "my_table_4" *** ...12,000 lines inserted in "my_table_4" *** ...13,000 lines inserted in "my_table_4" *** ...14,000 lines inserted in "my_table_4" *** ...15,000 lines inserted in "my_table_4" *** ...16,000 lines inserted in "my_table_4" *** ...17,000 lines inserted in "my_table_4" *** ...18,000 lines inserted in "my_table_4" *** ...19,000 lines inserted in "my_table_4" *** ...20,000 lines inserted in "my_table_4" *** ...21,000 lines inserted in "my_table_4" *** ...22,000 lines inserted in "my_table_4" *** ...23,000 lines inserted in "my_table_4" *** ...24,000 lines inserted in "my_table_4" *** ...25,000 lines inserted in "my_table_4" *** ...26,000 lines inserted in "my_table_4" *** There is now 26,555 entries in the PostgreSQL table. *** We are done with the table "my_table_4"! How cool is that? ... ... ** Table "my_table_5" *** There is 112,403 entries in this MySQL table. *** The table does not exists in the PostgreSQL db. *** Creation of the table in PostgreSQL... *** There is 112,403 entries to import... *** Retrieve the content from the MySQL table... *** Starting import entries from "my_table_5"... *** ...10,000 lines inserted in "my_table_5" *** ...20,000 lines inserted in "my_table_5" *** ...30,000 lines inserted in "my_table_5" *** ...40,000 lines inserted in "my_table_5" *** ...50,000 lines inserted in "my_table_5" *** ...60,000 lines inserted in "my_table_5" *** ...70,000 lines inserted in "my_table_5" *** ...80,000 lines inserted in "my_table_5" *** ...90,000 lines inserted in "my_table_5" *** Retrieve more content from the MySQL table to limit memory usage... *** ...100,000 lines inserted in "my_table_5" *** There is now 112,403 entries in the PostgreSQL table. *** We are done with the table "my_table_5"! How cool is that? ... * Closing PostgreSQL connection... * Closing MySQL connection... * Done.
Last update of this page: October 24, 2014
Please do not copy any of the content of this website without asking me first.