To migrate the metadata database from Postgres to MySQL.
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.
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
Output format is unaligned.
metaDataSchema# \pset pager off
Pager usage is off.
metaDataSchema# \pset tuples_only
Tuples only is on.
metaDataSchema# select concat('insert into druid_config values (''',name, ''',from_base64(''',translate(encode(payload::bytea, 'base64'), E'\n', ''),'''));') from druid_config;
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