Migrate to Postgres from H2

nikolac · October 14, 2021, 6:41pm

We're trying to migrate our H2-based, docker-deployed, prototype instance (version 0.41.0) to Postgres. However we're getting this error when following the migration guide for docker:

2021-10-14 17:19:05,826 INFO cmd.copy :: Set up h2 source database and run migrations...                                 
2021-10-14 17:19:05,828 INFO db.setup :: Verifying h2 Database Connection ...
2021-10-14 17:19:05,857 ERROR cmd.copy :: [FAIL]

clojure.lang.ExceptionInfo: ERROR Set up h2 source database and run migrations... {}
        at metabase.cmd.copy$do_step$fn__81957.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__82118$copy_BANG___82123$fn__82124.invoke(copy.clj:264)
        at metabase.cmd.copy$fn__82118$copy_BANG___82123.invoke(copy.clj:257)
        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 metabase.cmd$load_from_h2.invokeStatic(cmd.clj:42)
        at metabase.cmd$load_from_h2.invoke(cmd.clj:39)
        at clojure.lang.AFn.applyToHelper(AFn.java:152)
        at clojure.lang.AFn.applyTo(AFn.java:144)
        at clojure.core$apply.invokeStatic(core.clj:667)
        at clojure.core$apply.invoke(core.clj:662)
        at metabase.cmd$run_cmd$fn__82901.invoke(cmd.clj:190)
        at metabase.cmd$run_cmd.invokeStatic(cmd.clj:190)
        at metabase.cmd$run_cmd.invoke(cmd.clj:186)
        at clojure.lang.Var.invoke(Var.java:388)
        at metabase.core$run_cmd.invokeStatic(core.clj:145)
        at metabase.core$run_cmd.invoke(core.clj:143)
        at metabase.core$_main.invokeStatic(core.clj:167)
        at metabase.core$_main.doInvoke(core.clj:162)
        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__33711$verify_db_connection__33716$fn__33717$fn__33718.invoke(setup.clj:102)
        at metabase.db.setup$fn__33711$verify_db_connection__33716$fn__33717.invoke(setup.clj:100)
        at metabase.db.setup$fn__33711$verify_db_connection__33716.invoke(setup.clj:94)
        at metabase.db.setup$setup_db_BANG_$fn__33746$fn__33747.invoke(setup.clj:142)
        at metabase.util$do_with_us_locale.invokeStatic(util.clj:694)
        at metabase.util$do_with_us_locale.invoke(util.clj:680)
        at metabase.db.setup$setup_db_BANG_$fn__33746.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__82118$copy_BANG___82123$fn__82124$fn__82125.invoke(copy.clj:265)
        at metabase.cmd.copy$do_step$fn__81957.invoke(copy.clj:30)
        ... 25 more
Caused by: org.h2.jdbc.JdbcSQLException: Invalid database name: "/" [90138-197]
...

We archived the metabase.db.mv.db file before we started (106MB size), but we didn't stop the docker container before doing so and I suspect we might have a corrupt file.

I tried migrating outside of Docker using:

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

but this time the error was:

2021-10-14 20:22:36,483 INFO db.setup :: Verifying h2 Database Connection ...
2021-10-14 20:22:36,579 ERROR cmd.copy :: [FAIL]

clojure.lang.ExceptionInfo: ERROR Set up h2 source database and run migrations... {}
        at metabase.cmd.copy$do_step$fn__81957.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__82118$copy_BANG___82123$fn__82124.invoke(copy.clj:264)
        at metabase.cmd.copy$fn__82118$copy_BANG___82123.invoke(copy.clj:257)
        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:667)
        at clojure.core$apply.invoke(core.clj:662)
        at metabase.cmd$run_cmd$fn__82901.invoke(cmd.clj:190)
        at metabase.cmd$run_cmd.invokeStatic(cmd.clj:190)
        at metabase.cmd$run_cmd.invoke(cmd.clj:186)
        at clojure.lang.Var.invoke(Var.java:388)
        at metabase.core$run_cmd.invokeStatic(core.clj:145)
        at metabase.core$run_cmd.invoke(core.clj:143)
        at metabase.core$_main.invokeStatic(core.clj:167)
        at metabase.core$_main.doInvoke(core.clj:162)
        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__33711$verify_db_connection__33716$fn__33717$fn__33718.invoke(setup.clj:102)
        at metabase.db.setup$fn__33711$verify_db_connection__33716$fn__33717.invoke(setup.clj:100)
        at metabase.db.setup$fn__33711$verify_db_connection__33716.invoke(setup.clj:94)
        at metabase.db.setup$setup_db_BANG_$fn__33746$fn__33747.invoke(setup.clj:142)
        at metabase.util$do_with_us_locale.invokeStatic(util.clj:694)
        at metabase.util$do_with_us_locale.invoke(util.clj:680)
        at metabase.db.setup$setup_db_BANG_$fn__33746.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__82118$copy_BANG___82123$fn__82124$fn__82125.invoke(copy.clj:265)
        at metabase.cmd.copy$do_step$fn__81957.invoke(copy.clj:30)
        ... 23 more
Caused by: org.h2.jdbc.JdbcSQLException: File corrupted while reading record: null. Possible solution: use the recovery tool [90030-197]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
        at org.h2.message.DbException.get(DbException.java:168)
...

My questions at this point are:

Could we be having a corrupt H2 file backup and if so could we use a recovery tool (couldn't find any?) that might help us recover?
Why do the docker migration command and the java one throw different errors?
We had created quite a few dashboards content already on the H2 backend, what are our chances to not have to manually redo this work?

I'd appreciate any feedback that might help us get out of where we are with this.

flamber · October 14, 2021, 6:56pm

Hi @nikolac
Yes, the H2 is likely corrupted. Have a look here:
https://www.metabase.com/docs/latest/troubleshooting-guide/loading-from-h2.html#is-the-app-database-corrupted

It is impossible to tell you which corruption state you might be in. But it sounds very bad if you have just started testing Metabase 0.41.0 and the file is already 106MB.
I have some H2 files that have been around for years and upgraded, and with hundreds of questions and many databases. And my files are below 35MB.

I don't know which versions you're running, but it sounds like it's different versions, since the JAR file is exactly the same instead of the Docker container.
Anyways, I would highly recommend that you do not use Docker while migrating. It's a one-off process, so do it in JAR and then do a proper Docker setup referencing the Postgres databases.

nikolac · October 14, 2021, 7:18pm

@flamber your answer was very helpful. It turned out that the 106MB H2 file was indeed corrupt and somehow wrongly copied. The real instance was at 17MB so running the migration outside of docker on the correct file worked. Thanks again!