Project

General

Profile

Support #609

Updated by Daniel Curtis over 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: 
 <pre> 
 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

Back