Support #609: Migrating a MySQL Database To a PostgreSQL Database - GNU/Linux Administration - ALT Project Management

Support #609

Migrating a MySQL Database To a PostgreSQL Database

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

Status:

Closed

Priority:

Normal

Assignee:

Daniel Curtis

Category:

Database Server

Target version:

Debian

Start date:

04/30/2015

Due date:

% Done:

100%

Estimated time:

1.00 h

Spent time:

5.00 h

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