Migrating application database from H2 to Postgres

MichaelU · September 23, 2020, 3:49pm

Hello,
I would like to migrate the application database from H2 to postgres.
If I understand well, I need :

To configure environment variables as such: export MB_DB_TYPE=mysql export MB_DB_DBNAME=metabase export MB_DB_PORT=3306 export MB_DB_USER=<username> export MB_DB_PASS=<password> export MB_DB_HOST=localhost
To create a new empty postgres db : createdb --encoding=UTF8 -e metabase-db-name
Then to configure metabase to work with it with environment variables: export MB_DB_TYPE=postgres export MB_DB_DBNAME=metabase export MB_DB_PORT=5432 export MB_DB_USER=<username> export MB_DB_PASS=<password> export MB_DB_HOST=localhost text
finally, to migrate h2 to postgres with java -jar metabase.jar load-from-h2 /path/to/metabase.db

I have setup the environment variables, but when I run: createdb --encoding=UTF8 -e metabase I get createdb: command not found.
What do I do wrong ?
Thanks !

flamber · September 23, 2020, 4:01pm

Hi @MichaelU

You need to create a database on Postgres.
https://www.guru99.com/postgresql-create-database.html

And you should really consider using a service instead of running it manually, once you have migrated:
https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-debian.html

MichaelU · September 23, 2020, 4:23pm

Thanks !
Yes I am actually hosting a DB on DigitalOcean.
so I have set up the environment variables as such:
MB_DB_TYPE=postgres
bash-5.0# export MB_DB_DBNAME=metabase
bash-5.0# export MB_DB_TYPE=postgres
bash-5.0# export MB_DB_PORT=25060
bash-5.0# export MB_DB_USER=“user”
bash-5.0# export MB_DB_PASS=“pswd”

So I just need to run the migration command ?
java -jar metabase.jar load-from-h2 /path/to/metabase.db
If yes, then what should I put in /path/to/metabase.db ?

And we already use Docker to take care of Metabase. I just want to migrate the application database.

flamber · September 23, 2020, 4:30pm

@MichaelU

That doesn’t matter where you’re hosting it. You’re still running it manually instead of via a service.

And you’re defining MB_DB_TYPE multiple times to different values? What should it be defined as?

Where is your existing H2 database (metabase.db.mv.db)? You should point to that.

MichaelU · September 23, 2020, 4:33pm

Sorry corrected my post, I set MB_DB_TYPE to postgres.

So I run
java -jar metabase.jar load-from-h2 /metabase-data/metabase.db
and now get:
Error: Unable to access jarfile metabase.jar

Maybe I need to understand where from I need to run this command ?

flamber · September 23, 2020, 4:37pm

@MichaelU Let’s start completely over.

Please post “Diagnostic Info” from Admin > Troubleshooting.

MichaelU · September 23, 2020, 4:38pm

{
“browser-info”: {
“language”: “en-gb”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1.2 Safari/605.1.15”,
“vendor”: “Apple Computer, Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “4.19.0-0.bpo.5-amd64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-05-28”,
“tag”: “v0.35.4”,
“branch”: “release-0.35.x”,
“hash”: “b3080fa”
},
“settings”: {
“report-timezone”: “Europe/London”
}
}
}

flamber · September 23, 2020, 4:43pm

@MichaelU

You need to shutdown Metabase before you can do migration.
Then you run the migration. If you’re executing from a different directory than where metabase.jar and metabase.db.mv.db is located, then you need to refer to those files by full path.
I would highly suggest that you consider our hosted solution instead of trying to run it yourself:
https://www.metabase.com/start/hosted/

Surge · July 22, 2021, 10:51am

Hi

I am pretty new to metabase and finding my feet. I have managed to setup metabase on an ubuntu server and now I am looking at migrating my H2 database into mysql. I have spent quite some time reading different articles on the forum and have also followed the directions in this article.

When I run the command:
java -jar metabase.jar load-from-h2 /path/to/metabase.db it just seems to hang and nothing happens
root@test-metabase:~# java -jar /opt/metabase/metabase.jar load-from-h2 metabase.db
WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance.
2021-07-22 09:12:10,661 INFO metabase.util :: Maximum memory available to JVM: 481.4 MB
2021-07-22 09:12:28,923 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

I did set the local environment variable as follows
root@test-metabase:~# export MB_DB_TYPE=mysql
root@test-metabase:~# export MB_DB_DBNAME=metabase
root@test-metabase:~# export MB_DB_PORT=3006
root@test-metabase:~# export MB_DB_USER=metabase
root@test-metabase:~# export MB_DB_PASS=mypasword
root@test-metabase:~# export MB_DB_HOST=localhost

I did stop the metabase.service before running the import command.
Is there a log file I can view to see any errors ?

flamber · July 22, 2021, 11:28am

Hi @Surge
You are running with very limited RAM - at least 1GB is recommended.
My initial thought is that you are running low on entropy, which means the startup will take much longer (up to 20 minutes). See this issue for workarounds:
https://github.com/metabase/metabase/issues/10175

Also, there's a very big difference with the command you say you're using and the command you're actually using. Relative paths are great if you're in the right working directory - otherwise it's just going to cause more problems.
Either:

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

Or:

cd /opt/metabase
export ...all the MB_DB_* environment variables...
java -jar metabase.jar load-from-h2 metabase.db

After you have migrated, then setup Metabase as a service. Remember to confirm that Metabase is using your MySQL and not H2 after setup.
https://www.metabase.com/docs/latest/operations-guide/running-metabase-on-debian.html

Surge · July 22, 2021, 12:09pm

Hi @flamber

Thanks for the prompt answer. I was actually just reading this article on memory as i did not know why it started with such low memory and yes it was taking forever to start up.

Now I have ExecStart=/usr/bin/java -Xms1g -Xms2g -jar /opt/metabase/metabase.jar.

Still it starts up pretty slow... Actually started it about 10 minutes ago and still not up, I do not see any messages in the log that will show me if it is up or not, I basically check lsof -i tcp:3000 to see the port is listening and then sit and refresh my browser... very frustrating and I wish i could see some log or something to tell me if it is up and working or if there are issues.

Jul 22 12:04:45 test-metabase metabase[1244]: WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance.
Jul 22 12:04:48 test-metabase metabase[1244]: 2021-07-22 12:04:48,015 INFO metabase.util :: Maximum memory available to JVM: 1.9 GB
Jul 22 12:05:05 test-metabase metabase[1244]: 2021-07-22 12:05:05,031 INFO util.encryption :: Saved credentials encryption is DISABLED for this Metabase instance.
Jul 22 12:05:05 test-metabase metabase[1244]: For more information, see https://metabase.com/docs/latest/operations-guide/encrypting-database-details-at-rest.html

I already have followed the instructions in the last link you have sent and it is setup as a service.

I did not fully understand the article on PRNG and the link provided: https://ruleoftech.com/2016/avoiding-jvm-delays-caused-by-random-number-generation

Surge · July 22, 2021, 12:39pm

Just a small update regarding speed and log, after I finally found where the java.security file and made the changes recommended in the article and now I am seeing a full log and metabase is starting up very fast.

Now I am moving onto the import... will keep you updated

Surge · July 22, 2021, 1:04pm

So I am making progress, but still not working well.

What I did was I had metabase installed from a image from digital ocean market place, where i played around with it.

I then went and setup a new server and installed metabase as a service mysql etc... and all is working now.

I then went and copied the metabase.db.mv.db file to my new server and renamed it as metabase.db.

copied this to /opt/metabase

and did the following with the following results:
root@test-metabase:/opt/metabase# java -Xms1g -Xms2g -jar /opt/metabase/metabase.jar load-from-h2 /opt/metabase/metabase.db
WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance.
2021-07-22 12:49:42,024 INFO metabase.util :: Maximum memory available to JVM: 1.9 GB
2021-07-22 12:49:58,815 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-07-22 12:50:08,993 INFO metabase.core ::
Metabase v0.40.1 (ed8f9c8 release-x.40.x)
Copyright © 2021 Metabase, Inc.
Metabase Enterprise Edition extensions are NOT PRESENT.
2021-07-22 12:50:09,015 WARN metabase.core :: WARNING: You have enabled namespace tracing, which could log sensitive information like db passwords.
2021-07-22 12:50:09,084 INFO cmd.copy :: Set up h2 source database and run migrations...
2021-07-22 12:50:09,087 INFO db.setup :: Verifying h2 Database Connection ...
2021-07-22 12:50:09,163 ERROR cmd.copy :: [FAIL]
clojure.lang.ExceptionInfo: ERROR Set up h2 source database and run migrations... {}
at metabase.cmd.copy$do_step$fn__77870.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__78031$copy_BANG___78036$fn__78037.invoke(copy.clj:263)
at metabase.cmd.copy$fn__78031$copy_BANG___78036.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__77588.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__35702$verify_db_connection__35707$fn__35708$fn__35709.invoke(setup.clj:102)
at metabase.db.setup$fn__35702$verify_db_connection__35707$fn__35708.invoke(setup.clj:100)
at metabase.db.setup$fn__35702$verify_db_connection__35707.invoke(setup.clj:94)
at metabase.db.setup$setup_db_BANG$fn__35737$fn__35738.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__35737.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__78031$copy_BANG___78036$fn__78037$fn__78038.invoke(copy.clj:264)
at metabase.cmd.copy$do_step$fn__77870.invoke(copy.clj:30)
... 23 more
Caused by: org.h2.jdbc.JdbcSQLException: Database "/opt/metabase/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.(JdbcConnection.java:124)
at org.h2.jdbc.JdbcConnection.(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__35702$verify_db_connection__35707$fn__35708$fn__35709.invoke(setup.clj:100)
... 33 more
Command failed with exception: ERROR Set up h2 source database and run migrations...

I saw this error: Caused by: org.h2.jdbc.JdbcSQLException: Database "/opt/metabase/metabase.db" not found [90013-197]
which is strange as the file is there

I then went and tried to change the owner of the metabase.db to metabase:metabase - but this did not help

flamber · July 22, 2021, 1:11pm

@Surge You should not rename the metabase.db.mv.db - that is the correct file name. But you only reference it without .mv.db - as noted in the documentation:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html#notes

Surge · July 22, 2021, 2:29pm

@flamber - AMAZING. Thank you so much for the support. I now have everything populated and up and running!

Very much appreciate all the assistance.

flamber · July 22, 2021, 2:36pm

@Surge Please make sure to double-check Admin > Troubleshooting > "Diagnostic Info", which should say "application-database": "mysql"
I have just seen too many who went through migration problems and using a mix of relative/absolute paths and then they ended up using H2 at some point after they restarted and lost all their Metabase data, since they didn't have backups of the H2.

Surge · July 22, 2021, 3:00pm

Thanks, this is what the diagnostic info is showing:
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",

Is this correct ? Or have i missed a step

flamber · July 22, 2021, 3:04pm

@Surge It's correct. I would still recommend restarting, since I have seen incorrect setup of the SystemD service too. Then you know that it starts up correctly and everything is working even after a reboot.

Surge · July 22, 2021, 4:16pm

Thanks restarted and the settings are the same.

Am I able to remove the metabase.db.mv.db file now ?

flamber · July 22, 2021, 4:17pm

@Surge Yes, make sure you have a backup of it - just for safe keeping.