Amazon DMS

The easiest they to migrate data from MySQL to PostgreSQL is to use Amazon DMS. It helps to migrate data using standalone DBs as endpoints as easy as Amazon RDS instances.

Configuring of Amazon DMS is easy enough and can be described in three steps:

  1. Create Replication Instances, EC2 which will do the job
  2. Configuring of Endpoints, source and target
  3. Create Task which will use our endpoints and will be run on created replication instance

Amazon DMS task creates the schema and tables in PostgreSQL and moves our data.

Task can be run only once or used as a mechanism of continuous replication.

Sequences

DMS does not create sequences for our primary ID column. So we have to create sequences, alter them to be used in tables and restart for least value.

Next plsql request will generate SQL request to do the job:

SELECT 'DROP SEQUENCE IF EXISTS my_schema.' || quote_ident(t.table_name) || '_id_seq CASCADE; '
  || 'CREATE SEQUENCE my_schema.'|| quote_ident(table_name) || '_id_seq; ' ||
  'ALTER TABLE my_schema.' || quote_ident(t.table_name) || ' ALTER COLUMN id SET DEFAULT nextval(''my_schema.' || quote_ident(t.table_name) || '_id_seq''::regclass); ' ||
  'ALTER SEQUENCE my_schema.' || quote_ident(t.table_name) || '_id_seq owned by my_schema.' || quote_ident(t.table_name) || '.id; ' ||
  'SELECT SETVAL(''my_schema.' || quote_ident(t.table_name) ||'_id_seq'', (SELECT MAX(id) FROM my_schema.' || quote_ident(t.table_name) ||') +1 );'
FROM information_schema.tables AS t
  WHERE t.table_catalog = 'my_database'
    AND t.table_schema = 'my_schema'
  order by t.table_name

In output we have something like this:

DROP SEQUENCE IF EXISTS my_schema.ad_caches_id_seq CASCADE;
CREATE SEQUENCE my_schema.ad_caches_id_seq;
ALTER TABLE my_schema.ad_caches ALTER COLUMN id SET DEFAULT nextval('my_schema.ad_caches_id_seq'::regclass);
ALTER SEQUENCE my_schema.ad_caches_id_seq owned by my_schema.ad_caches.id;
SELECT SETVAL('my_schema.ad_caches_id_seq', (SELECT MAX(id) FROM my_schema.ad_caches) +1 );

...

Just run it.

tinyints to booleans

MySQL uses the tinyint type of columns for boolean columns. We have to find these columns and change their type:

SELECT 'alter table '
  || quote_ident(t.table_schema) || '.'
  || quote_ident(t.table_name)
  || ' alter column ' || quote_ident(t.column_name) || ' drop default, '
  || ' alter column ' || quote_ident(t.column_name) || ' type boolean '
    || ' using ' || quote_ident(t.column_name) || ' != 0;'
FROM information_schema.columns AS t
  WHERE table_catalog = 'my_database'
    AND table_schema = 'my_schema'
    AND data_type = 'smallint';

In output we have:

alter table sp_placement_caches
  alter column close_button drop default,
  alter column close_button type boolean
    using close_button!=0,
  alter column close_button set default true;

...

Just run it.

Epilogue

There are some other issues that may appear. Just be careful and run tests.