py-mysql2pgsql - A tool for migrating/converting/exporting data from MySQL to PostgreSQL

Attention!

Currently there is no support for importing Installation:

If you're like me you don't like random stuff polluting your python install. Might I suggest installing this in an virtualenv?

> virtualenv --no-site-packages ~/envs/py-mysql2pgsql
> source ~/envs/py-mysql2pgsql/bin/activate

Requirements:

• MySQL-python
• PyYAML
• pytz

On Windows

I have only done limited testing on this platform using Python 2.7. Here are the driver dependencies for windows, install these before attempting to install py-mysql2pgsql or it will fail.

• MySQL-python for Windows

From PyPI:

All dependencies should be automatically installed when installing the app the following ways

> pip install py-mysql2pgsql

From source:

> git clone git://github.com/philipsoutham/py-mysql2pgsql.git
> cd py-mysql2pgsql
> python setup.py install

Usage:

Looking for help?

> py-mysql2pgsql -h
usage: py-mysql2pgsql [-h] [-v] [-f FILE]

Tool for migrating/converting data from mysql to postgresql.

optional arguments:
 -h, --help show this help message and exit
 -v, --verbose Show progress of data migration.
 -f FILE, --file FILE Location of configuration file (default:
 mysql2pgsql.yml). If none exists at that path,
 one will be created for you.

Don't worry if this is your first time, it'll be gentle.

> py-mysql2pgsql
No configuration file found.
A new file has been initialized at: mysql2pgsql.yml
Please review the configuration and retry...

As the output suggests, a file was created at mysql2pgsql.yml for you to edit. For the impatient, here is what the file contains.

# a socket connection will be selected if a 'socket' is specified
# also 'localhost' is a special 'hostname' for MySQL that overrides the 'port' option
# and forces it to use a local socket connection
# if tcp is chosen, you can use compression

mysql:
 hostname: localhost
 port: 3306
 socket: /tmp/mysql.sock
 username: mysql2psql
 password:
 database: mysql2psql_test
 compress: false
destination:
 # if file is given, output goes to file, else postgres
 file:
 postgres:
 hostname: localhost
 port: 5432
 username: mysql2psql
 password:
 database: mysql2psql_test

# if only_tables is given, only the listed tables will be converted. leave empty to convert all tables.
#only_tables:
#- table1
#- table2
# if exclude_tables is given, exclude the listed tables from the conversion.
#exclude_tables:
#- table3
#- table4

# if supress_data is true, only the schema definition will be exported/migrated, and not the data
supress_data: false

# if supress_ddl is true, only the data will be exported/imported, and not the schema
supress_ddl: false

# if force_truncate is true, forces a table truncate before table loading
force_truncate: false

# if timezone is true, forces to append/convert to UTC tzinfo mysql data
timezone: false

# if index_prefix is given, indexes will be created whith a name prefixed with index_prefix
index_prefix:

Pretty self explanatory right? A couple things to note, first if destination -> file is populated all output will be dumped to the specified location regardless of what is contained in destination -> postgres. So if you want to dump directly to your server make sure the file value is blank.

Say you have a MySQL db with many, many tables, but you're only interested in exporting a subset of those table, no problem. Add only the tables you want to include in only_tables or tables that you don't want exported to exclude_tables.

Other items of interest may be to skip moving the data and just create the schema or vice versa. To skip the data and only create the schema set supress_data to true. To migrate only data and not recreate the tables set supress_ddl to true; if there's existing data that you want to drop before importing set force_truncate to true. force_truncate is not necessary when supress_ddl is set to false.

Note that when migrating, it's sometimes possible to knock your sequences out of whack. When this happens, you may get IntegrityErrors about your primary keys saying things like, "duplicate key value violates unique constraint." See Data Type Conversion Legend

Since there is not a one-to-one mapping between MySQL and PostgreSQL data types, listed below are the conversions that are applied. I've taken some liberties with some, others should come as no surprise.

Conversion caveats:

Not just any valid MySQL database schema can be simply converted to the PostgreSQL. So when you end with a different database schema please note that:

• Most MySQL versions don't enforce NOT NULL constraint on date and enum fields. Because of that NOT NULL is skipped for this types. Here's an excuse for the dates: About:

  I ported much of this from an existing project written in Ruby by Max Lapshin over at