Project

General

Profile

Support #609

Migrating a MySQL Database To a PostgreSQL Database

Added by Daniel Curtis almost 9 years ago. Updated almost 9 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Category:
Database Server
Target version:
Start date:
04/30/2015
Due date:
% Done:

100%

Estimated time:
1.00 h
Spent time:

Description

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.

Prepare the Environment

Debian/Ubuntu

  • Make sure the system is up to date:
    apt-get update && apt-get upgrade -y
    
  • Install a few dependencies:
    apt-get install virtualenv python-dev
    
  • Setup the python virtual environment:
    virtualenv --no-site-packages ~/envs/py-mysql2pgsql
    source ~/envs/py-mysql2pgsql/bin/activate
    
  • Install py-mysql2pgsql
    pip install py-mysql2pgsql
    

FreeBSD

  • Make sure the system is up to date:
    portsnap fetch extract && portmaster -a
    
  • Install py-mysql2pgsql:
    portmaster databases/py-mysql2pgsql
    

Migrate The Database

  • Generate a new config file:
    py-mysql2pgsql
    
    • Example output:
      No configuration file found.
      A new file has been initialized at: mysql2pgsql.yml
      Please review the configuration and retry...
      
  • Edit the mysql2pgsql config file:
    nano mysql2pgsql.yml
    
    • 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
      
  • Now run the migration:
    py-mysql2pgsql -v
    

Resources

Also available in: Atom PDF