Migrating H2 to Postgres

Hi
I'm having a trouble in migrating my metabase database h2 to postgressql(RDS)
I'm using docker inside EC2.
When I try to migrate my db, the process will stop.
This is what I did.

(1)install psql (PostgreSQL) 11.5 in my EC2

(2)install docker and run metabase

(3)export
export MB_DB_TYPE=postgres
export MB_DB_DBNAME=dbname
export MB_DB_PORT=5432
export MB_DB_USER=user
export MB_DB_PASS=pass
export MB_DB_HOST=rds

(4)Dig inside the container
docker exec -it [container name] bash

(5)Move to the metabase.jar directory
cd app/

(6)Use the command
java -jar metabase.jar load-from-h2 /metabase.db

But it stops with this log
INFO metabase.util :: Maximum memory available to JVM: 237.8 MB

Could you tell me whats happening and how to solve this.
Thank you.

Hi @coro

This is likely because you are running out of entropy, since you are spinning up two java processes. If you wait 20-40 minutes, then it might finish, but given the very low amount of available RAM, then I think it will crash during the migration.

Normally I would recommend just extracting the H2 from the Docker and run the migration on your local computer, since it's a one-off process.

Otherwise you would have to edit the script in /app/run_metabase.sh so it does the migration - then spin up the container and let it migrate, and then you can destroy the container (always backup first!) and start a new container, which then points to your Postgress.

Thank you

How could I extract the H2 from the Docker and run the migration on my local computer?
Because to use the migration command
(java -jar metabase.jar load-from-h2 /metabase.db)
I thought that I have to be inside the container and at the directory where metabase.db is.

We are already using metabase and have several dashboards.
Don't want to lose these data.

@coro
Rule number #1 in computers: Make sure you have backups before doing anything big.

You would do docker cp metabase:/metabase.db/metabase.db.mv.db . to copy from the container to the directory you're in.

You need to point to the H2 file, but without the last .mv.db extensions.
If you run from inside the container, then you need to make sure that Metabase isn't starting normally, since that would use all the resources and also lock the H2.

Update

Fixed with java -jar metabase.jar load-from-h2 metabase.db/metabase.db


I'm also running into this issue and I followed the exact same steps:

(1) I have a backup of the database in my local computer.

(2) Exported:

export MB_DB_TYPE=postgres
export MB_DB_DBNAME=metabase
export MB_DB_PORT=5432
export MB_DB_USER=postgres
export MB_DB_PASS=123
export MB_DB_HOST=host
export MB_DB_FILE=metabase.db

I tried with:

  • java -jar metabase.jar load-from-h2
  • java -jar metabase.jar load-from-h2 metabase.db

But I'm seeing a couple of errors/info/warnings:

2022-08-12 02:13:07,195 INFO cmd.copy :: Set up h2 source database and run migrations... 
2022-08-12 02:13:07,198 INFO db.setup :: Verifying h2 Database Connection ...
2022-08-12 02:13:07,242 ERROR cmd.copy :: [FAIL]
clojure.lang.ExceptionInfo: ERROR Set up h2 source database and run migrations...: Unable to connect to Metabase h2 DB. {}
Caused by: org.h2.jdbc.JdbcSQLException: Database "/Users/ivor/Desktop/metabase-migration/metabase.db" not found [90013-197]

17

Complete logs: https://gist.github.com/ivorpad/7002a8b411013efb0b38e447340c2b82

As well as running (which is the same thing):

java -DMB_DB_TYPE=postgres -DMB_DB_CONNECTION_URI="jdbc:postgresql://<redacted>" -jar metabase.jar load-from-h2 metabase.db

With same results.

Note that I am running this command on my computer with 9GB of RAM available, not in EC2.

@ivor metabase.db is a directory, so your H2 path should be metabase.db/metabase.db
And either use load-from-h2 ... or MB_DB_FILE=... - not both.