[H2 to Postgres] ERROR Set up h2 source database and run migrations

adidab · November 25, 2021, 6:50am

Hi Guys, I have followed the migration guide on this page https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html.

Unfortunately, I found this error while I trying to migrate from H2 to Postgres and I used to Ubuntu 20.04 to do this task, here my step and logs:
$ root: ls /var/lib/postgresql/backup/
metabase.db metabase.jar

export MB_DB_TYPE=postgres
export MB_DB_DBNAME=metabase
export MB_DB_PORT=5432
export MB_DB_USER=metabase_user
export MB_DB_PASS=metbase
export MB_DB_HOST=localhost
export MB_DB_FILE=metabase.db

java -jar metabase.jar load-from-h2 metabase.db
Picked up _JAVA_OPTIONS: -Xmx2048m
WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance.
2021-11-25 06:21:28,006 INFO metabase.util :: Maximum memory available to JVM: 1.9 GB
2021-11-25 06:21:44,464 INFO util.encryption :: Saved credentials encryption is DISABLED for this Metabase instance. 🔓
 For more information, see https://metabase.com/docs/latest/operations-guide/encrypting-database-details-at-rest.html
2021-11-25 06:21:53,569 INFO metabase.core ::
Metabase v0.40.3.1 (0550b86 release-x.40.x)

Copyright © 2021 Metabase, Inc.

Metabase Enterprise Edition extensions are NOT PRESENT.
2021-11-25 06:21:53,586 WARN metabase.core :: WARNING: You have enabled namespace tracing, which could log sensitive information like db passwords.
2021-11-25 06:21:53,687 INFO cmd.copy :: Set up h2 source database and run migrations...
2021-11-25 06:21:53,691 INFO db.setup :: Verifying h2 Database Connection ...
2021-11-25 06:21:53,757 ERROR cmd.copy :: [FAIL]

clojure.lang.ExceptionInfo: ERROR Set up h2 source database and run migrations... {}
	at metabase.cmd.copy$do_step$fn__77867.invoke(copy.clj:33)
	at metabase.cmd.copy$do_step.invokeStatic(copy.clj:29)
	at metabase.cmd.copy$do_step.invoke(copy.clj:27)
	at metabase.cmd.copy$fn__78028$copy_BANG___78033$fn__78034.invoke(copy.clj:263)
	at metabase.cmd.copy$fn__78028$copy_BANG___78033.invoke(copy.clj:256)
	at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:35)
	at metabase.cmd.load_from_h2$load_from_h2_BANG_.invoke(load_from_h2.clj:25)
	at clojure.lang.Var.invoke(Var.java:384)
	at metabase.cmd$load_from_h2.invokeStatic(cmd.clj:45)
	at metabase.cmd$load_from_h2.invoke(cmd.clj:39)
	at clojure.lang.AFn.applyToHelper(AFn.java:154)
	at clojure.lang.AFn.applyTo(AFn.java:144)
	at clojure.core$apply.invokeStatic(core.clj:665)
	at clojure.core$apply.invoke(core.clj:660)
	at metabase.cmd$run_cmd$fn__77585.invoke(cmd.clj:191)
	at metabase.cmd$run_cmd.invokeStatic(cmd.clj:191)
	at metabase.cmd$run_cmd.invoke(cmd.clj:187)
	at clojure.lang.Var.invoke(Var.java:388)
	at metabase.core$run_cmd.invokeStatic(core.clj:141)
	at metabase.core$run_cmd.invoke(core.clj:139)
	at metabase.core$_main.invokeStatic(core.clj:163)
	at metabase.core$_main.doInvoke(core.clj:158)
	at clojure.lang.RestFn.applyTo(RestFn.java:137)
	at metabase.core.main(Unknown Source)
Caused by: clojure.lang.ExceptionInfo: Unable to connect to Metabase h2 DB. {}
	at metabase.db.setup$fn__35708$verify_db_connection__35713$fn__35714$fn__35715.invoke(setup.clj:102)
	at metabase.db.setup$fn__35708$verify_db_connection__35713$fn__35714.invoke(setup.clj:100)
	at metabase.db.setup$fn__35708$verify_db_connection__35713.invoke(setup.clj:94)
	at metabase.db.setup$setup_db_BANG_$fn__35743$fn__35744.invoke(setup.clj:142)
	at metabase.util$do_with_us_locale.invokeStatic(util.clj:683)
	at metabase.util$do_with_us_locale.invoke(util.clj:669)
	at metabase.db.setup$setup_db_BANG_$fn__35743.invoke(setup.clj:141)
	at metabase.db.setup$setup_db_BANG_.invokeStatic(setup.clj:140)
	at metabase.db.setup$setup_db_BANG_.invoke(setup.clj:136)
	at metabase.cmd.copy$fn__78028$copy_BANG___78033$fn__78034$fn__78035.invoke(copy.clj:264)
	at metabase.cmd.copy$do_step$fn__77867.invoke(copy.clj:30)
	... 23 more
Caused by: org.h2.jdbc.JdbcSQLException: Database "/var/lib/postgresql/backup/metabase.db" not found [90013-197]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.engine.Engine.openSession(Engine.java:64)
	at org.h2.engine.Engine.openSession(Engine.java:179)
	at org.h2.engine.Engine.createSessionAndValidate(Engine.java:157)
	at org.h2.engine.Engine.createSession(Engine.java:140)
	at org.h2.engine.Engine.createSession(Engine.java:28)
	at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:351)
	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:124)
	at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:103)
	at org.h2.Driver.connect(Driver.java:69)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at clojure.java.jdbc$get_driver_connection.invokeStatic(jdbc.clj:271)
	at clojure.java.jdbc$get_driver_connection.invoke(jdbc.clj:250)
	at clojure.java.jdbc$get_connection.invokeStatic(jdbc.clj:411)
	at clojure.java.jdbc$get_connection.invoke(jdbc.clj:274)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1111)
	at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1182)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1144)
	at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1160)
	at clojure.java.jdbc$query.invoke(jdbc.clj:1144)
	at metabase.driver.sql_jdbc.connection$can_connect_with_spec_QMARK_.invokeStatic(connection.clj:201)
	at metabase.driver.sql_jdbc.connection$can_connect_with_spec_QMARK_.invoke(connection.clj:198)
	at metabase.db.setup$fn__35708$verify_db_connection__35713$fn__35714$fn__35715.invoke(setup.clj:100)
	... 33 more
Command failed with exception: ERROR Set up h2 source database and run migrations...

Any concerns for this case? Anyway I ran that command on the same place as the directory of metabase.jar and metabase.db

Existing Metabase H2 v0.40.3.1
Metabase.jar v0.40.3.1
Java 11.0.11
Posgresql version 11.13 Ubuntu 20.04

Thank you in advanced

flamber · November 25, 2021, 10:22am

Hi @adidab
H2 is a special database, so the actual file has to have the extension .mv.db, meaning you need to rename the file metabase.db to metabase.db.mv.db, but everything else (MB_DB_FILE etc) should not include the extension.

adidab · November 25, 2021, 1:01pm

Hi @flamber

It's already the name that you have mentioned at this directory:

/var/lib/postgresql/backup# ls metabase.db.mv.db/metabase.db.mv.db
metabase.db.mv.db/metabase.db.mv.db

I mean metabase.db.mv.db/ a folder yet, still digging, anyway I can run this file with docker image metabase using existing H2 file and it's working normally as the existing state but I still cant migrate it to postgres

flamber · November 25, 2021, 1:05pm

@adidab Then you should give the path to the file without the .mv.db extension, so:
export MB_DB_FILE=metabase.db.mv.db/metabase.db
or
java -jar metabase.jar load-from-h2 metabase.db.mv.db/metabase.db

adidab · November 25, 2021, 1:28pm

Cool @flamber you saved my time, it's migrated to Postgres but I also want to migrate it from VM to Kubernetes, it's deployed but initially running on an H2 database again inside the container, my deployment using helm chart, any concern for this? Does Metabase also have its own helm chart for this case? Thank you

Note: for the Postgres will be still on VM environment

flamber · November 25, 2021, 1:36pm

@adidab You need to configure the application database, so it points to the Postgres. We're working on a Helm, but it has been given lower priority, since it takes a lot more resources to provide something like that (since we need to support it officially).
https://github.com/metabase/metabase/pull/16603

But you should absolutely not run with H2 on Kubernetes - that's a recipe for disaster.

adidab · November 25, 2021, 1:41pm

So, Metabase will provide the helm chart too? but it's still in development ya? How long does it take time? if you don't mind @flamber

Thanks

flamber · November 25, 2021, 1:49pm

@adidab I cannot say. It's not even guaranteed that we will provide this. The problem with Helm charts is that it makes it "easy" for people to do things on Kubernetes, but Kubernetes is very complicated, so if people are not very skilled, then they might end up losing data.

adidab · November 25, 2021, 1:55pm

I see @flamber will figure it out or we have to build own chart for this case. Anyway, thank you for your assist

pavanD945 · August 23, 2022, 9:33am

I had the same issue and it is resolved by this approach.

Copy your metabase.jar file into the /metabase-data/metabase.db/ directory, same level as metabase.db.mv.db metabase.db.trace.db metabase.db2.mv.db

and run this:

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

I have no idea what is the use of giving "metabase.db". But, it works!

flamber · December 23, 2022, 11:49am

A post was split to a new topic: H2 to Postgres - "Error copying instances of Field"

brucewayne_now · February 19, 2023, 8:07pm

This one worked for me. Thanks a lot.