Support #609
Updated by Daniel Curtis almost 9 years ago
This is a guide for migrating a MySQL database to a PostgreSQL database using Debian 8, Ubuntu 14.04, or FreeBSD 9. This process was done using a VM and access granted to the VM for the MySQL database and the PostgreSQL database, this may not be ideal for all use cases.
h2. Prepare the Environment
h3. Debian/Ubuntu
* Make sure the system is up to date:
<pre>
apt-get update && apt-get upgrade -y
</pre>
* Install a few dependencies:
<pre>
apt-get install virtualenv python-dev
</pre>
* Setup the python virtual environment:
<pre>
virtualenv --no-site-packages ~/envs/py-mysql2pgsql
source ~/envs/py-mysql2pgsql/bin/activate
</pre>
* Install py-mysql2pgsql
<pre>
pip install py-mysql2pgsql
</pre>
h3. FreeBSD
* Make sure the system is up to date:
<pre>
portsnap fetch extract && portmaster -a
</pre>
* Install py-mysql2pgsql:
<pre>
portmaster databases/py-mysql2pgsql
</pre>
h2. Migrate The Database
* Generate a new config file:
<pre>
py-mysql2pgsql
</pre>
#* _Example output_:
<pre>
No configuration file found.
A new file has been initialized at: mysql2pgsql.yml
Please review the configuration and retry...
</pre>
* Edit the mysql2pgsql config file:
<pre>
nano mysql2pgsql.yml
</pre>
<pre>
#* And modify the config file to connect to both the mysql and postgresql databases:
mysql:
hostname: mysql.example.com
port: 3306
username: exampleuser
password: SuperSecretPassword
database: exampledb
compress: false
destination:
file:
postgres:
hostname: pgsql.example.com
port: 5432
username: exampleuser
password: SuperSecretPassword
database: exampledb
# 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
</pre>
* Now run the migration:
<pre>
py-mysql2pgsql -v
</pre>
h2. Resources
* https://github.com/philipsoutham/py-mysql2pgsql