Error Migrating from H2 running in Azure ACI to Azure Postgres

michel.skladman · February 25, 2020, 3:52pm

Hi all
currently running metabase on docker in Azure ACI (Container instances).
DB is running locally inside the container in H2,
trying to run a migration docker on a stand alone Linux machine as described in documentation,
verified connectivity using tcpping on port 5432 successful, and running the following docker command:
docker run --name metabase-migration -e “MB_DB_FILE=/mnt/acishareprodncus/metabase.db” -e “MB_DB_TYPE=postgres” -e “MB_DB_DBNAME=.postgres.database.azure.com" -e “MB_DB_PORT=5432” -e "MB_DB_USER=” -e “MB_DB_PASS=****” -e “MB_DB_HOST=my-database-host” metabase/metabase load-from-h2

and getting:

mkdir: can’t create directory ‘/mnt/acishareprodncus/metabase.db’: No such file or directory
02-25 15:43:28 DEBUG plugins.classloader :: Using NEWLY CREATED classloader as shared context classloader: clojure.lang.DynamicClassLoader@6e041285
02-25 15:43:29 INFO metabase.util :: Loading Metabase…
02-25 15:43:29 INFO metabase.util :: Maximum memory available to JVM: 462.1 MB
02-25 15:43:45 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
02-25 15:43:55 INFO metabase.db :: Verifying postgres Database Connection …
02-25 15:43:55 INFO driver.impl :: Registered abstract driver :sql
Load driver :sql took 168.0 ms
02-25 15:43:55 INFO driver.impl :: Registered abstract driver :sql-jdbc (parents: [:sql])
Load driver :sql-jdbc took 191.9 ms
02-25 15:43:55 INFO driver.impl :: Registered driver :postgres (parents: [:sql-jdbc])
Load driver :postgres took 378.9 ms
02-25 15:43:55 INFO driver.impl :: Initializing driver :sql…
02-25 15:43:55 INFO driver.impl :: Initializing driver :sql-jdbc…
02-25 15:43:55 INFO driver.impl :: Initializing driver :postgres…
02-25 15:43:55 ERROR driver.util :: Database connection error
org.postgresql.util.PSQLException: The connection attempt failed.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:292)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:458)
at org.postgresql.Driver.connect(Driver.java:260)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
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:1093)
at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1075)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1142)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invokeStatic(connection.clj:159)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invoke(connection.clj:154)
at metabase.driver.sql_jdbc$fn__71310.invokeStatic(sql_jdbc.clj:37)
at metabase.driver.sql_jdbc$fn__71310.invoke(sql_jdbc.clj:35)
at clojure.lang.MultiFn.invoke(MultiFn.java:234)
at metabase.driver.util$can_connect_with_details_QMARK_$fn__22311.invoke(util.clj:32)
at metabase.util$do_with_timeout$fn__9155.invoke(util.clj:301)
at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)
at clojure.lang.AFn.call(AFn.java:18)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.net.UnknownHostException: my-database-host
at java.base/java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.base/java.net.SocksSocketImpl.connect(Unknown Source)
at java.base/java.net.Socket.connect(Unknown Source)
at org.postgresql.core.PGStream.(PGStream.java:75)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
… 29 more
java.lang.Exception: The connection attempt failed.
at metabase.driver.util$can_connect_with_details_QMARK_.invokeStatic(util.clj:37)
at metabase.driver.util$can_connect_with_details_QMARK_.doInvoke(util.clj:20)
at clojure.lang.RestFn.invoke(RestFn.java:442)
at clojure.lang.Var.invoke(Var.java:393)
at metabase.db$fn__21258$verify_db_connection__21267$fn__21270$fn__21271.invoke(db.clj:275)
at metabase.db$fn__21258$verify_db_connection__21267$fn__21270.invoke(db.clj:274)
at metabase.db$fn__21258$verify_db_connection__21267.invoke(db.clj:266)
at metabase.db$fn__21258$verify_db_connection__21267$fn__21268.invoke(db.clj:269)
at metabase.db$fn__21258$verify_db_connection__21267.invoke(db.clj:266)
at metabase.db$setup_db_BANG__STAR_$fn__21310.invoke(db.clj:328)
at metabase.util$do_with_us_locale.invokeStatic(util.clj:656)
at metabase.util$do_with_us_locale.invoke(util.clj:642)
at metabase.db$setup_db_BANG__STAR_.invokeStatic(db.clj:327)
at metabase.db$setup_db_BANG__STAR_.invoke(db.clj:322)
at metabase.db$setup_db_from_env_BANG__STAR_.invokeStatic(db.clj:337)
at metabase.db$setup_db_from_env_BANG__STAR_.invoke(db.clj:334)
at metabase.db$setup_db_BANG_$fn__21321.invoke(db.clj:351)
at metabase.db$setup_db_BANG_.invokeStatic(db.clj:349)
at metabase.db$setup_db_BANG_.invoke(db.clj:344)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:240)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invoke(load_from_h2.clj:234)
at clojure.lang.Var.invoke(Var.java:384)
at metabase.cmd$load_from_h2.invokeStatic(cmd.clj:37)
at metabase.cmd$load_from_h2.invoke(cmd.clj:30)
at metabase.cmd$load_from_h2.invokeStatic(cmd.clj:33)
at metabase.cmd$load_from_h2.invoke(cmd.clj:30)
at clojure.lang.AFn.applyToHelper(AFn.java:152)
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__66719.invoke(cmd.clj:127)
at metabase.cmd$run_cmd.invokeStatic(cmd.clj:127)
at metabase.cmd$run_cmd.invoke(cmd.clj:123)
at clojure.lang.Var.invoke(Var.java:388)
at metabase.core$run_cmd.invokeStatic(core.clj:132)
at metabase.core$run_cmd.invoke(core.clj:130)
at metabase.core$main.invokeStatic(core.clj:141)
at metabase.core$main.doInvoke(core.clj:137)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at metabase.core.main(Unknown Source)
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:292)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:195)
at org.postgresql.Driver.makeConnection(Driver.java:458)
at org.postgresql.Driver.connect(Driver.java:260)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
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:1093)
at clojure.java.jdbc$db_query_with_resultset_STAR.invoke(jdbc.clj:1075)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1164)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at clojure.java.jdbc$query.invokeStatic(jdbc.clj:1142)
at clojure.java.jdbc$query.invoke(jdbc.clj:1126)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invokeStatic(connection.clj:159)
at metabase.driver.sql_jdbc.connection$can_connect_QMARK_.invoke(connection.clj:154)
at metabase.driver.sql_jdbc$fn__71310.invokeStatic(sql_jdbc.clj:37)
at metabase.driver.sql_jdbc$fn__71310.invoke(sql_jdbc.clj:35)
at clojure.lang.MultiFn.invoke(MultiFn.java:234)
at metabase.driver.util$can_connect_with_details_QMARK_$fn__22311.invoke(util.clj:32)
at metabase.util$do_with_timeout$fn__9155.invoke(util.clj:301)
at clojure.core$binding_conveyor_fn$fn__5754.invoke(core.clj:2030)
at clojure.lang.AFn.call(AFn.java:18)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.net.UnknownHostException: my-database-host
at java.base/java.net.AbstractPlainSocketImpl.connect(Unknown Source)
at java.base/java.net.SocksSocketImpl.connect(Unknown Source)
at java.base/java.net.Socket.connect(Unknown Source)
at org.postgresql.core.PGStream.(PGStream.java:75)
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
… 29 more
Command failed with exception: The connection attempt failed.

can anyone please help? thanks

flamber · February 25, 2020, 4:06pm

Hi @michel.skladman

It looks like the container doesn’t have access to the H2:
mkdir: can’t create directory ‘/mnt/acishareprodncus/metabase.db’: No such file or directory
And I cannot see any mounting/volumes in your docker command, so the container wouldn’t be able to see anything outside itself.

Are you using the same version of JAR and Docker?

Why are you using Docker for migrating instead of just using the JAR?

Where have you tested the tcpping? Meaning, have you tested from within the container via bash?

michel.skladman · February 25, 2020, 4:15pm

you are correct this is the command i ran after this:

docker run --name metabase-migration -v /mnt/acishareprodmcus/ -e "MB_DB_FILE=/metabase-data/metabase.db" -e "MB_DB_TYPE=postgres" -e "MB_DB_DBNAME=.database.azure.com" -e "MB_DB_PORT=5432" -e "MB_DB_USER=" -e "MB_DB_PASS=**" -e "MB_DB_HOST=my-database-host" metabase/metabase load-from-h2

michel.skladman · March 4, 2020, 1:00pm

bump…

flamber · March 4, 2020, 1:05pm

@michel.skladman
Are you using the same version of JAR and Docker?
Why are you using Docker for migrating instead of just using the JAR?
Where have you tested the tcpping? Meaning, have you tested from within the container via bash?

michel.skladman · March 4, 2020, 2:37pm

Hi flamber thanks for the quick response,
I’m using docker and not JAR for the migration because my current running instance is on docker , and have tried both running a dedicated migration container and both running the migration from withing the docker ,
thanks

flamber · March 4, 2020, 2:42pm

@michel.skladman

Are you using the same version of JAR and Docker?
Where have you tested the tcpping? Meaning, have you tested from within the container via bash?

Okay, but since this is only about exporting the current content in the H2 (of your current JAR setup) to Postgres, then you can simply just run the migration with the JAR, since it will only be done once.

And then you only setup your new Docker to use the Postgres as application database - so no migration at all.

That way, you’re splitting up where problems could be and making it easier for yourself to troubleshoot.