drupal

Migrate postgres to mysql

Migrate postgres database to mysql/mariadb

Export data from postgres

1
2
pg_dump -U postgres --quote-all-identifiers --no-acl --no-owner --format p --inserts --schema-only mydatabase -f pgschema.sql
pg_dump -U postgres --quote-all-identifiers --no-acl --no-owner --format p --inserts --data-only mydatabase -f pgdata.sql

Convert sql dumps to mysql

Download https://github.com/timothysmith0609/pg2mysql/tree/patch-1 which is a fork of https://github.com/ChrisLundquist/pg2mysql originally posted at http://www.lightbox.ca/pg2mysql.php

1
2
php pg2mysql_cli.php pgschema.sql schema.sql
php pg2mysql_cli.php pgdata.sql data.sql

The following lines are specific for Drupal, feel free to ignore them

1
2
sed -i '/field_deleted_data/d' data.sql
sed -i '/field_deleted_revision/d' data.sql

Import into mysql/mariadb

  • truncate db
  • import schema.sql
  • import data.sql

Keep in mind that import will be (very) slow, it’s 1 insert per line.

Fix blob fields

1
select concat("update ", TABLE_NAME, " set ", COLUMN_NAME, " = unhex(substr(", COLUMN_NAME, ", 2));") from information_schema.columns where table_schema = 'default' and DATA_TYPE = 'longblob' order by table_name,ordinal_position;

Copy paste the output and execute it in the database.

Auto increment fields

Those are not migrated correctly, since there’s no easy way to detect them, so you need to manually run 

1
ALTER TABLE t MODIFY COLUMN c int auto_increment;