pivotal-cf/pg2mysql

Name: pg2mysql

Owner: Pivotal Cloud Foundry

Description: Tool for safely migrating from PostgreSQL to MySQL

Created: 2017-04-12 21:46:28.0

Updated: 2018-05-24 06:29:36.0

Pushed: 2018-03-19 20:04:35.0

Homepage:

Size: 142

Language: Go

GitHub Committers

User	Most Recent Commit	# Commits

Other Committers

User	Email	Most Recent Commit	# Commits

README

pg2mysql

pg2mysql was created to facilitate migrating data from PostgreSQL to MySQL given mostly equivalent schemas.

In PostgreSQL it is common to use the text datatype for character data, which the PostgreSQL documentation describes as having effectively no limit. In MySQL this is not the case, as the datatype with the same name (text) is limited to 65535, and the more common datatype, varchar, is defined with an explicit limit (e.g. varchar(255)).

This means that, given a column with text datatype in PostgreSQL, there must be enough room in the equivalent MySQL column for the data in PostgreSQL to be safely migrated over. This tool can be used to validate the target MySQL schema against a populated PostgreSQL database and, provided the data in the PostgreSQL database is compatible, the migration to move the data from PostgreSQL to MySQL.

Install from source

et github.com/pivotal-cf/pg2mysql/cmd/pg2mysql

Usage

Create a config:

t > config.yml <<EOF
l:
tabase: some-dbname
ername: some-user
ssword: some-password
st: 192.168.10.1
rt: 3306

gresql:
tabase: some-dbname
ername: some-user
ssword: some-password
st: 192.168.10.2
rt: 5432
l_mode: disable

Note: See PostgreSQL documentation for valid SSL mode values.

Run the validator:

2mysql -c config.yml validate
d incompatible rows in apps with IDs [2]
d incompatible rows in app_usage_events with IDs [9 10 11 12]
d incompatible rows in events with IDs [16 17 18]

If there are any incompatible rows, as in above, they will need to be modified before proceeding with a migration.

Run the migrator:

2mysql -c config.yml migrate --truncate
rted 1 records into spaces_developers
rted 0 records into security_groups_spaces
rted 0 records into service_bindings
rted 2 records into droplets
rted 2 records into organizations
rted 3 records into lockings
rted 0 records into service_dashboard_clients
rted 0 records into route_bindings

Note: The --truncate flag will truncate each table prior to copying data over.

Run the verifier after migration to confirm the data has been migrated as expected:

2mysql -c config.yml verify
fying table spaces_developers...OK
fying table security_groups_spaces...OK
fying table service_bindings...OK
fying table droplets...
ILED: 1 row missing
ssing IDs: 1,3,5
fying table organizations...OK
fying table lockings...OK
fying table service_dashboard_clients...OK
fying table route_bindings...OK

Verify does an exact comparison (except for timestamps; see Note) of the contents of each row of each table in PostgreSQL to see that a matching row exists in MySQL.

Note: The verify command assumes that the precise PostgreSQL timestamps are truncated when doing the migration over to MySQL. However, it has been found that this behavior is not consistent with all forms of MySQL. Official MySQL rounds the timestamps whereas MariaDB truncates. A PR to intelligently support both would be happily received.