MySQL to Postgres (psql) migration

There is a gem called mysql2psql, originally by Max Lapshin, to help migrate a db from MySQL to Postgres. However, as a beginner programmer, I found it quite difficult to set up correctly.

So I took a different approach: with a simple shell script, the migration takes just 11 lines of code. The shell script is only concerned with migrating the data – not the structure. It basically just uses grep and sed to massage the mysqldump into something that postgres can understand.

To run it:

  cd lib/scripts/

Here is the shell script:

# in lib/scrips/

echo -----------------Dumping out of mysql--------------------
mysqldump legacy_db_name -u root --no-create-info --compatible=postgresql > dump.sql

echo -------Removing unecessary lines from dump file-----------
more dump.sql | grep INSERT | grep -v 'INSERT INTO "schema_migrations' > filtered_dump.sql

echo -----Fixing single quote escaping in dump file------------
sed "s/\\\'/''/g" filtered_dump.sql > final_dump.sql

echo ----------Removing all data from postgres db--------------
dropdb development_db_name
createdb development_db_name
psql development_db_name < ../../db/structure.sql -q

echo -------Allowing implicit integer::boolean casting---------
psql development_db_name < int_to_bool.sql

echo -------Loading data from dump file into postgres----------
psql development_db_name < final_dump.sql

echo -----------------Removing dump files-----------------------
rm dump.sql
rm filtered_dump.sql
rm final_dump.sql

And here is a supporting SQL file the script uses:

-- in lib/scripts/int_to_bool.sql
update pg_cast set castcontext = 'i' where oid in (
select c.oid
from pg_cast c
inner join pg_type src on src.oid = c.castsource
inner join pg_type tgt on tgt.oid = c.casttarget
where src.typname like 'int%' and tgt.typname like 'bool%'

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s