Migrating Metabase Application DB from MySQL to Postgres

1

I am migrating my Metabase server from one AWS region to another. The Metabase application is running using Docker Compose, and the application database is hosted on an AWS RDS MySQL Community DB (version 8.0.35). I have attempted multiple methods for migrating both the Metabase application database and the server itself but am encountering issues related to table migration.

Steps I Have Tried:
AWS DMS Migration: I have tried migrating the Metabase application database using the AWS DMS service but encountered errors. I also, I have read somewhere that AWS DMS won't migrate Foreign Key constraints and other things, so stopped this.

Metabase CLI Command (migrate print): I used the Metabase CLI command migrate print which provided me with the script and used that script to run on the target database server to apply migrations and pointed the metabase application database to this new db endpoint but running the SQL scripts skips SQL queries since some of the relations are not in place.

Dump and Load Method: I used the dump-to-h2 (for MySQL) method to dump the database. Then, I used the load-from-h2 (for PostgreSQL) to load it onto the target server, but I received errors related to tables during the load process.

Current Setup:
Docker Compose: I am launching the Metabase server using Docker Compose.
Metabase Version: v0.51.3 (released on 2024-11-11)
Application Database: MySQL (version 8.0.35) on AWS RDS Community Edition

Target Setup:
Docker Compose
Metabase Version: v0.51.3
Application Database: PostgreSQL on AWS RDS

Current Metabase Server Info:
"metabase-info": {
"databases": [
"h2",
"mongo",
"postgres",
"clickhouse"
],
"run-mode": "prod",
"plan-alias": "",
"version": {
"date": "2024-11-11",
"tag": "v0.51.3",
"hash": "d757d0b"
},
"settings": {
"report-timezone": "UTC"
},
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "8.0.35"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.10"
}
}
}

Request:
Can you provide guidance or best practices for migrating the Metabase database from MySQL to PostgreSQL in a different region without encountering table issues?
Are there any known issues with the migration process, particularly when using AWS DMS or Metabase CLI tools?
Suggestions for troubleshooting the table errors encountered during the migration process.

2

I moved from MySQL to Postgres via the dump to H2 without any problems. I think it's the only supported way.
Before you start, make sure you truncate the query and cache tables so you're not migrating a stack of rubbish.
Make sure your Metabase version is consistent throughout.

3

Hi Adrew,
What do you mean by truncate the query and cache tables?
I am using/pulling latest metabase image from docker.

4

I got below error after dumping mysql data to H2 and then using load-from-H2

Logs for Dump and Load Method ->
Metabase Enterprise Edition extensions are NOT PRESENT. 2024-11-20 11:18:40,289 INFO driver.impl :: Registered abstract driver :metabase.driver.sql-jdbc.execute.legacy-impl/use-legacy-classes-for-read-and-set 🚚 2024-11-20 11:18:40,298 INFO driver.impl :: Registered abstract driver :metabase.driver.sql.query-processor.empty-string-is-null/empty-string-is-null 🚚 2024-11-20 11:18:40,715 INFO cmd.copy :: Set up h2 source database and run migrations... 2024-11-20 11:18:40,717 INFO db.setup :: Verifying h2 Database Connection ... 2024-11-20 11:18:42,020 INFO db.setup :: Successfully verified H2 2.1.214 (2022-06-13) application database connection. ✅ 2024-11-20 11:18:42,490 INFO db.setup :: Checking if a database downgrade is required... 2024-11-20 11:18:42,645 INFO liquibase.database :: Set default schema name to PUBLIC 2024-11-20 11:18:43,486 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:44,072 INFO db.setup :: Running Database Migrations... 2024-11-20 11:18:44,182 INFO db.setup :: Setting up Liquibase... 2024-11-20 11:18:44,190 INFO liquibase.database :: Set default schema name to PUBLIC 2024-11-20 11:18:44,459 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:44,482 INFO db.liquibase :: Updating liquibase table to reflect consolidated changeset filenames 2024-11-20 11:18:44,528 INFO liquibase.lockservice :: Successfully acquired change log lock 2024-11-20 11:18:44,530 INFO db.liquibase :: No migration lock found. 2024-11-20 11:18:44,531 INFO db.liquibase :: Migration lock acquired. 2024-11-20 11:18:44,592 INFO liquibase.lockservice :: Successfully released change log lock 2024-11-20 11:18:44,593 INFO db.setup :: Liquibase is ready. 2024-11-20 11:18:44,594 INFO db.liquibase :: Checking if Database has unrun migrations... 2024-11-20 11:18:44,632 INFO liquibase.database :: Set default schema name to PUBLIC 2024-11-20 11:18:44,976 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:45,002 INFO liquibase.changelog :: Reading from "PUBLIC"."DATABASECHANGELOG" 2024-11-20 11:18:45,325 INFO db.liquibase :: No unrun migrations found. 2024-11-20 11:18:45,906 INFO db.setup :: Database Migrations Current ... ✅ 2024-11-20 11:18:45,909 INFO metabase.util :: Database setup took 5.2 s 2024-11-20 11:18:45,910 INFO cmd.copy :: [OK] 2024-11-20 11:18:45,912 INFO cmd.copy :: Set up postgres target database and run migrations... 2024-11-20 11:18:45,912 INFO db.setup :: Verifying postgres Database Connection ... 2024-11-20 11:18:46,345 INFO db.setup :: Successfully verified PostgreSQL 14.12 application database connection. ✅ 2024-11-20 11:18:46,346 INFO db.setup :: Checking if a database downgrade is required... 2024-11-20 11:18:46,385 INFO liquibase.database :: Set default schema name to metabase 2024-11-20 11:18:46,610 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:46,620 INFO liquibase.changelog :: Creating database history table with name: metabase.databasechangelog 2024-11-20 11:18:46,687 INFO db.setup :: Running Database Migrations... 2024-11-20 11:18:46,688 INFO db.setup :: Setting up Liquibase... 2024-11-20 11:18:46,698 INFO liquibase.database :: Set default schema name to metabase 2024-11-20 11:18:46,859 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:46,894 INFO db.liquibase :: Updating liquibase table to reflect consolidated changeset filenames 2024-11-20 11:18:46,907 INFO liquibase.lockservice :: Successfully acquired change log lock 2024-11-20 11:18:46,909 INFO db.liquibase :: No migration lock found. 2024-11-20 11:18:46,909 INFO db.liquibase :: Migration lock acquired. 2024-11-20 11:18:46,917 INFO liquibase.lockservice :: Successfully released change log lock 2024-11-20 11:18:46,919 INFO db.setup :: Liquibase is ready. 2024-11-20 11:18:46,919 INFO db.liquibase :: Checking if Database has unrun migrations... 2024-11-20 11:18:46,937 INFO liquibase.database :: Set default schema name to metabase 2024-11-20 11:18:47,098 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:47,134 INFO liquibase.changelog :: Reading from metabase.databasechangelog 2024-11-20 11:18:47,531 INFO db.liquibase :: Database has unrun migrations. Checking if migration lock is taken... 2024-11-20 11:18:47,544 INFO liquibase.lockservice :: Successfully acquired change log lock 2024-11-20 11:18:47,546 INFO db.liquibase :: No migration lock found. 2024-11-20 11:18:47,546 INFO db.liquibase :: Migration lock acquired. 2024-11-20 11:18:47,553 INFO liquibase.database :: Set default schema name to metabase 2024-11-20 11:18:47,688 INFO liquibase :: Parsed changelog file 'liquibase.yaml' 2024-11-20 11:18:47,708 INFO liquibase.changelog :: Reading from metabase.databasechangelog 2024-11-20 11:18:47,789 INFO db.liquibase :: Running 420 migrations ... 2024-11-20 11:18:47,903 INFO liquibase.changelog :: Reading from metabase.databasechangelog 2024-11-20 11:18:48,038 INFO liquibase.command :: Using deploymentId: 2101528038 2024-11-20 11:18:48,042 INFO liquibase.changelog :: Reading from metabase.databasechangelog 2024-11-20 11:18:48,181 INFO liquibase.snapshot :: Creating snapshot 2024-11-20 11:18:48,527 WARN liquibase.executor :: extension "citext" already exists, skipping 2024-11-20 11:18:50,127 INFO liquibase.changelog :: SQL in file initialization/metabase_postgres.sql executed 2024-11-20 11:18:50,133 INFO liquibase.changelog :: ChangeSet migrations/001_update_migrations.yaml::v00.00-000::qnkhuat ran successfully in 1954ms 2024-11-20 11:18:50,166 INFO liquibase.changelog :: Table action created 2024-11-20 11:18:50,169 INFO liquibase.changelog :: ChangeSet migrations/001_update_migrations.yaml::v45.00-001::snoe ran successfully in 26ms 2024-11-20 11:18:50,190 ERROR liquibase.changelog :: ChangeSet migrations/001_update_migrations.yaml::v45.00-002::snoe encountered an exception. liquibase.exception.DatabaseException: ERROR: relation "metabase.report_card" does not exist [Failed SQL: (0) CREATE TABLE "metabase"."query_action" ("action_id" INTEGER NOT NULL, "card_id" INTEGER NOT NULL, CONSTRAINT "fk_query_action_ref_card_id" FOREIGN KEY ("card_id") REFERENCES "metabase"."report_card"("id") ON DELETE CASCADE, CONSTRAINT "fk_query_action_ref_action_id" FOREIGN KEY ("action_id") REFERENCES "metabase"."action"("id") ON DELETE CASCADE)]

5

I see that there are two tables query and query_cache, I am not sure that if these tables contains actually queries which are used to create dashboards/questionnaire on metabase front end. So, I have not truncated these tables.

6

both tables can be truncated if needed

7

Those tables can contain a lot of data. You don't want to be migrating that via H2.
One is the queries that have run, the other is cached data from previous queries.

8

Thanks Andrew for the info. I will truncate these tables before running load-from-H2.

9

I am able to migrate the metabase app database from mysql to postgres using dump-to-H2 and load-from-H2 method. In my case, the issue was, at postgres side, I had to simply create empty database without creating any schema since DATABASECHANGELOG and DATABASECHANGELOGLOCK tables from mysql side will create the queries which has to be run against public schema at postgres side.