Migrating metadata from Postgres to MySQL

OBJECTIVE:

To migrate the metadata database from Postgres to MySQL.

ADDITIONAL INFORMATION:

This article is specifically about migrating metadata from Postgres to MySQL.  Overall steps for migrating a cluster can be found elsewhere, eg https://support.imply.io/hc/en-us/articles/115004960053-Migrate-existing-Druid-Cluster-to-a-new-Imply-cluster.

Some of the tables have binary "payload" columns.  These can't be directly dumped from Postgres and loaded in to MySQL.  Following is an example, for druid_config, of how to dump output that can be loaded into MySQL.  We log in to psql, set some output formats, and run a command to output SQL that we can run in the mysql client to load the data.

PROCEDURE:

note: other guides convert using UTF8.  This didn't work for some of the payloads in a customer's druid_config, so we had to use base64 encoding instead.  This is more versatile, and should work for any table.

psql -U metadataUserNameHere -d metaDataSchemaHere

metaDataSchema# \a
Output format is unaligned.
metaDataSchema# \pset pager off
Pager usage is off.
metaDataSchema# \pset tuples_only
Tuples only is on.

metaDataSchema#\o /path/to/output/file/out.txt
metaDataSchema# select concat('insert into druid_config values (''',name, ''',from_base64(''',translate(encode(payload::bytea, 'base64'), E'\n', ''),'''));') from druid_config;

metaDataSchema#\o

This creates the output file, with rows like

insert into druid_config values ('coordinator.compaction.config',from_base64('eyJjb...');

We should now be able to import the druid_config data using something like this:

mysql -h <host_name> -u <user_name> -p <db_name>  < /path/to/output/file/out.txt

NOTE:

There is also a tool, ps-tool-druid-migration, in github, that may help.  For Postgres -> MySQL, it works for some tables, such as druid_segments.  It doesn't work for druid_config (for example), which is why this article was created.