Migrate Metabase DB from MySQL to Postgres via pgloader

1

Goal: To migrate the current Metabase backend database from MySQL to PostgreSQL.

Current MySQL version: Server version: 8.0.37 MySQL Community Server - GPL

New PostgreSQL version: postgres (PostgreSQL) 15.12

I have been working through issues with the pgloader utility attempting to migrate our current Metabase backend db from MySQL to PostgreSQL. I have successfully migrated over all of the data but I am unable to properly bring over the foreign key constraints. I have tried many different variations of the pgloader configuration but all are unsuccessful. Looking for some guidance on how I can properly migrate from MySQL to PostgreSQL via pgloader.

2

The Metabase devs are going to have to help here.

Pretty sure you can’t port the whole database over as-is. You have to be careful not to modify any of the Liquibase data, or else it might try to run the wrong migrations and damage the install or prevent Metabase from starting.

Your better bet is to create a new Metabase instance on PostgreSQL then copy the data over and not the schema. I suspect there are differences that will have to be translated in the process, but what those are I don’t know.

Also, while you’re at it, can you upgrade to a newer PostgreSQL? 15.12 isn’t even the most recent on the 15 branch. 15.14 was released last month.

3

The supported way is to go via H2. Have patience and truncate the log and cache tables before you start.

4

The supported path would be to run the dump to H2 from MySQL then import into the new Postgres DB? Is there any further documentation on this process or guidance?

#Dump to H2 from Mysql:
java -jar metabase.jar dump-to-h2 Metabase_MySQL_Export.db

#Import the H2 dump into the new Postgres DB:

export MB_DB_TYPE=postgres
export MB_DB_CONNECTION_URI="jdbc:postgresql://:5432/metabase?user=&password="
java --add-opens java.base/java.nio=ALL-UNNAMED -jar metabase.jar load-from-h2 /path/to/Metabase_MySQL_Export.db # do not include .mv.db

5

I think that is the documentation! I’ve only done it once. Worked well enough, just took a long time.

6

I confirmed I can migrate from PostgreSQL to MySQL via H2 with Metabase 56. (0.56.5.1, to be precise.) The most difficult part was specifying the MySQL JDBC string as Metabase uses an ancient version of the MariaDB connector that uses the pre-version-3 SSL parameters.

The short, short version:

  1. Shut down existing Metabase instance.
  2. Dump the database from the old server: java … metabase.jar dump-to-h2 metabase.db
  3. Verify the H2 copy is valid (optional): java … metabase.jar
  4. Upload to the new database: java … metabase.jar load-from-h2 metabase.db
  5. Start up Metabase and verify everything migrated over okay.

Be sure to keep the doc page linked below nearby for reference. Important notes:

  • Delete any latent metabase.*.db files from the same directory the metabase.jar file is in, in case the dump trips over them (it will try to delete them without an option but may not have permission to do so).
  • You will have to remove the JDBC URI between steps 2-3 and change it to the new database JDBC URI between steps 3-4. Keep track of which is which. If you goof up you get a big stack of exceptions when the app db can’t be connected to (A ResourcePool could not acquire a resource from its primary factory or source. is usually one of them, just before the one that says why the connect failed).
  • In the new application database, before performing the load-from-h2 step, you must create the database and grant the metabase user permission to create database objects in it.
7

@dwhitemv I am unable to locate the metabase.jar file anywhere on the current MySQL host. Does this mean this migration method is not a viable option for my case?

8

Where are you running Metabase from? Are you running in a container?

Worst case, you can download it.

9

Openshift

10

Containers are a pain in the butt when you need to do manual stuff.

If you need to download older versions of the jars, they are linked off the GitHub releases page:

If your security allows it, and you don’t have a management host you can install OpenJDK 21 and the jar on, it might be easiest to launch a container or VM with a generic RHEL/Rocky/etc. install and ssh you can get into, and run the manual commands from there. If database access is locked down to that specific container, see if you can exec into the container or temporarily start an ssh server in it.

Ultimately, wherever you run the migration from needs access to both database servers and enough local storage to store a copy of the app database in H2.

11

Greatly appreciate the information! I am going to download the jar file and attempt to run it on the database server itself. When I run the dump-to-h2 will it have any impact on the live running system? This is our non prod env but I want to ensure I have a safe implementation plan for prod as well. Do I have the ability to perform a dry run of sorts before stopping the application and running the dump-to-h2 to test things out?

12

It’ll be about the same impact as a backup.

You can run the dump with the app running to get an idea of how taxing it is, just make sure you delete the file afterwards as it won’t be consistent.

13

So it looks like our installed openjdk is not the correct version to run the dump-to-h2.

java -version
openjdk version "1.8.0_462"
OpenJDK Runtime Environment (build 1.8.0_462-b08)
OpenJDK 64-Bit Server VM (build 25.462-b08, mixed mode)

My command was failing with similar errors reported in this discussion post:

It looks like openJDK version 17 is not support for RHEL7 which my MySQL db resides on. Is there any toolsets out there that Metabase would work well with the migrate the data? I am at the point of justifying a toolset purchase to get this work completed.

14

JDK 21 is required for modern Metabase. Instructions for installing it on RHEL7 are here:

Note that you will have to select the JDK version to use for Metabase, especially if other packages on the system are using the other JDK versions.

15

I was able to get the dump-to-h2 and load-from-h2 to run successfully however the row counts do not match between MySQL source and newly imported PostgreSQL target. There is much less data in the PostgreSQL tables but no errors reported on dump or load so I am not sure what to make of it.

16

Which tables?

17

#Missing table in imported Postgres database:
DATABASECHANGELOGLOCK
query_analysis

#New tables in imported Postgres database:
db_router
metabase_field_user_settings
metabot
metabot_conversation
metabot_entity
metabot_message
metabot_prompt
notification_card

I ended up making two hops, first I ran a mysqldump export of metabase database from the live RHEL7 MySQL 8.0.43 then imported it into a RHEL9 MySQL databases on the PostgreSQL server. After importing I can see all row counts match and look correct. Next I ran the following command to dump the new MySQL database on RHEL9 to H2 since I needed a server that supports openJDK21.

java -jar /tmp/metabase.jar dump-to-h2 /var/lib/mysql/data/Metabase_MySQL_Export.db

This completes successfully I then run the load from H2 into Postgres.

export MB_DB_TYPE=postgres
export MB_DB_CONNECTION_URI="jdbc:postgresql://<server_name>:5432/metabase?user=postgres&password="
java --add-opens java.base/java.nio=ALL-UNNAMED -jar /tmp/metabase.jar load-from-h2 /var/lib/mysql/data/Metabase_MySQL_Export.db

This runs successfully:

2025-09-30 15:12:06,547 INFO liquibase.util :: UPDATE SUMMARY
2025-09-30 15:12:06,547 INFO liquibase.util :: Run: 530
2025-09-30 15:12:06,547 INFO liquibase.util :: Previously run: 0
2025-09-30 15:12:06,547 INFO liquibase.util :: Filtered out: 56
2025-09-30 15:12:06,547 INFO liquibase.util :: -------------------------------
2025-09-30 15:12:06,548 INFO liquibase.util :: Total change sets: 586
2025-09-30 15:12:06,548 INFO liquibase.util :: FILTERED CHANGE SETS SUMMARY
2025-09-30 15:12:06,548 INFO liquibase.util :: DBMS mismatch: 56
2025-09-30 15:12:06,553 INFO liquibase.util :: Update summary generated
2025-09-30 15:12:06,558 INFO liquibase.command :: Update command completed successfully.
2025-09-30 15:12:06,559 INFO liquibase.logging :: Successfully released change log lock
2025-09-30 15:12:06,559 INFO liquibase.command :: Command execution complete
2025-09-30 15:12:06,680 INFO app-db.liquibase :: Migration complete in 42.7 s
2025-09-30 15:12:06,681 INFO app-db.setup :: Database Migrations Current ... :white_check_mark:
2025-09-30 15:12:06,684 INFO metabase.util :: Database setup took 43.7 s
2025-09-30 15:12:06,684 INFO cmd.copy :: [OK]
2025-09-30 15:12:06,685 INFO cmd.copy :: Testing if target postgres database is already populated...
2025-09-30 15:12:06,686 INFO cmd.copy :: [OK]
2025-09-30 15:12:06,687 INFO cmd.copy :: Clearing default entries created by Liquibase migrations...
2025-09-30 15:12:06,688 INFO cmd.copy :: Temporarily disabling DB constraints...
2025-09-30 15:12:06,923 INFO cmd.copy :: [OK]
2025-09-30 15:12:15,198 INFO cmd.copy :: Re-enabling DB constraints...
2025-09-30 15:12:15,198 INFO cmd.copy :: [OK]
2025-09-30 15:12:15,199 INFO cmd.copy :: [OK]
2025-09-30 15:12:15,200 INFO cmd.copy :: Temporarily disabling DB constraints...
2025-09-30 15:12:15,228 INFO cmd.copy :: [OK]
2025-09-30 15:12:15,497 INFO cmd.copy :: Copying instances of User...
2025-09-30 15:12:15,503 INFO cmd.copy :: copied 1 instances.
2025-09-30 15:12:15,505 INFO cmd.copy :: Copying instances of Setting...
2025-09-30 15:12:15,506 INFO cmd.copy :: copied 1 instances.
2025-09-30 15:12:15,511 INFO cmd.copy :: Copying instances of Collection...
2025-09-30 15:12:15,512 INFO cmd.copy :: copied 1 instances.
2025-09-30 15:12:15,518 INFO cmd.copy :: Copying instances of PermissionsGroup...
2025-09-30 15:12:15,520 INFO cmd.copy :: copied 2 instances.
2025-09-30 15:12:15,521 INFO cmd.copy :: Copying instances of PermissionsGroupMembership...
2025-09-30 15:12:15,522 INFO cmd.copy :: copied 1 instances.
2025-09-30 15:12:15,523 INFO cmd.copy :: Copying instances of Permissions...
2025-09-30 15:12:15,524 INFO cmd.copy :: copied 5 instances.
2025-09-30 15:12:15,566 INFO cmd.copy :: Re-enabling DB constraints...
2025-09-30 15:12:15,566 INFO cmd.copy :: [OK]
2025-09-30 15:12:15,570 INFO cmd.copy :: Setting Postgres sequence ids to proper values...
2025-09-30 15:12:15,595 INFO cmd.copy :: [OK]

The only thing I noticed was the filter out DBMS mismatch: 56 but it does not throw any error. Once complete I checked row counts between source MySQL and newly import Postgres.

The only tables which have row counts > 0 are the following:

collection 1

core_user 1

databasechangelog 530

metabot 2

permissions 5

permissions_group 2

permissions_group_membership 1

qrtz_locks 2

qrtz_scheduler_state 1

setting 1

I am leaning on my devs to look more into the databasechangelog table and determine if this is worth running through and pointing the app to the new site and see if Liquibase populated the missing rows on app startup but I am not overly confident it will. Besides the missing rows everything else looks good, views and constraints came over properly. I am just not sure why the rows were not populated as expected.

18

I do see several of these messages during the load:

2025-09-30 15:11:25,866 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-033::camsaul" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:25,876 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-035::camsaul" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:25,926 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-039::camsaul" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:25,936 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-041::camsaul" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:25,994 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-051::qnkhuat" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:25,997 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-052::qnkhuat" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:26,001 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-053::qnkhuat" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql

2025-09-30 15:11:26,004 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v45.00-054::qnkhuat" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql
2025-09-30 15:11:42,299 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v51.2024-09-26T03:01:00::escherize" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : SQL Precondition failed. Expected '1' got '0'
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql
liquibase.yaml : DBMS Precondition failed: expected h2, got postgresql

2025-09-30 15:11:42,302 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v51.2024-09-26T03:02:00::escherize" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : SQL Precondition failed. Expected '1' got '0'
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql
liquibase.yaml : DBMS Precondition failed: expected h2, got postgresql

2025-09-30 15:11:42,306 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v51.2024-09-26T03:03:00::escherize" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : SQL Precondition failed. Expected '1' got '0'
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql
liquibase.yaml : DBMS Precondition failed: expected h2, got postgresql

2025-09-30 15:11:42,308 INFO liquibase.changelog :: Marking ChangeSet: "migrations/001_update_migrations.yaml::v51.2024-09-26T03:04:00::escherize" as ran despite precondition failure due to onFail='MARK_RAN':
liquibase.yaml : SQL Precondition failed. Expected '1' got '0'
liquibase.yaml : DBMS Precondition failed: expected mysql,mariadb, got postgresql
liquibase.yaml : DBMS Precondition failed: expected h2, got postgresql

19

These liquibase messages are expected. MySQL and PostgreSQL have slightly different schemas due to per-engine differences. The messages are emitted for the schema updates for a different engine than the running one.

databasechangelog is for liquibase, it will be different between the two databases, version lineages, etc. You didn’t copy this from the old database. Don’t touch it.

20

Any explanation for the missing rows? I am tempted to run through the process again and have them point the app at the new site since I know we are able to go back to the old MySQL back end if needed but I am hesitant because of the missing rows.