i tried to migrate away from H2 to postgresql but getting error
Command failed with exception: Row {1} not found in primary index “PUBLIC.SYS_DATA: 17753” [90143-197]
i used following script
java -DMB_DB_TYPE=postgres -DMB_DB_DBNAME=metabase -DMB_DB_PORT=5432 -DMB_DB_USER=meta -DMB_DB_PASS=base -DMB_DB_HOST=localhost -jar metabase.jar load-from-h2 path\to\metabase.db
please help
java -DMB_DB_TYPE=postgres -DMB_DB_DBNAME=metabase -DMB_DB_PORT=5432 -DMB_DB_USER=metabase -DMB_DB_PASS=metabase -DMB_DB_HOST=localhost -DMB_DB_AUTOMIGRATE=true -jar metabase.jar load-from-h2 D:\Development\metabase\metabase.db
07-12 02:36:49 e[1mDEBUG plugins.classloadere[0m :: Using NEWLY CREATED classloader as shared context classloader: clojure.lang.DynamicClassLoader@6c000e0c
07-12 02:36:49 e[1mDEBUG plugins.classloadere[0m :: Setting current thread context classloader to shared classloader clojure.lang.DynamicClassLoader@6c000e0c…
07-12 02:36:49 e[1mINFO metabase.utile[0m :: Loading Metabase…
07-12 02:36:49 e[1mINFO metabase.utile[0m :: Maximum memory available to JVM: 1.8 GB
07-12 02:36:52 e[1mINFO util.encryptione[0m :: 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
07-12 02:36:56 e[1mINFO metabase.drivere[0m :: Registered abstract driver :sql
07-12 02:36:59 e[1mINFO metabase.dbe[0m :: Verifying postgres Database Connection …
07-12 02:36:59 e[1mINFO metabase.drivere[0m :: Registered abstract driver :sql-jdbc (parents: [:sql])
Load driver :sql-jdbc took 20.6 ms
07-12 02:36:59 e[1mINFO metabase.drivere[0m :: Registered driver :postgres (parents: [:sql-jdbc])
Load driver :postgres took 85.2 ms
07-12 02:36:59 e[1mINFO metabase.drivere[0m :: Initializing driver :sql…
07-12 02:36:59 e[1mINFO metabase.drivere[0m :: Initializing driver :sql-jdbc…
07-12 02:36:59 e[1mINFO metabase.drivere[0m :: Initializing driver :postgres…
07-12 02:37:00 e[1mINFO metabase.dbe[0m :: Verify Database Connection …
07-12 02:37:00 e[1mINFO metabase.dbe[0m :: Running Database Migrations…
07-12 02:37:00 e[1mINFO metabase.dbe[0m :: Setting up Liquibase…
07-12 02:37:01 e[1mINFO metabase.dbe[0m :: Liquibase is ready.
07-12 02:37:01 e[1mINFO metabase.dbe[0m :: Checking if Database has unrun migrations…
07-12 02:37:02 e[1mINFO metabase.dbe[0m :: Database Migrations Current …
Database setup took 2.9 s
Testing if target DB is already populated…
e[32m[OK]e[0m
Temporarily disabling DB constraints…
e[32m[OK]e[0m
org.h2.jdbc.JdbcSQLException: Row {1} not found in primary index “PUBLIC.SYS_DATA: 17753” [90143-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.mvstore.db.MVPrimaryIndex.getRow(MVPrimaryIndex.java:202)
at org.h2.mvstore.db.MVTable.getRow(MVTable.java:492)
at org.h2.mvstore.db.MVSecondaryIndex$MVStoreCursor.get(MVSecondaryIndex.java:493)
at org.h2.engine.Database.open(Database.java:768)
at org.h2.engine.Database.openDatabase(Database.java:286)
at org.h2.engine.Database.(Database.java:280)
at org.h2.engine.Engine.openSession(Engine.java:66)
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.DriverManager.getConnection(Unknown Source)
at 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 metabase.cmd.load_from_h2$load_data_BANG_.invokeStatic(load_from_h2.clj:161)
at metabase.cmd.load_from_h2$load_data_BANG_.invoke(load_from_h2.clj:160)
at metabase.cmd.load_from_h2$load_from_h2_BANG_$fn__65660.invoke(load_from_h2.clj:257)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:799)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:769)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:834)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:769)
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:782)
at clojure.java.jdbc$db_transaction_STAR_.invoke(jdbc.clj:769)
at metabase.cmd.load_from_h2$load_from_h2_BANG_.invokeStatic(load_from_h2.clj:245)
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:38)
at metabase.cmd$load_from_h2.invoke(cmd.clj:31)
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__64891.invoke(cmd.clj:128)
at metabase.cmd$run_cmd.invokeStatic(cmd.clj:128)
at metabase.cmd$run_cmd.invoke(cmd.clj:124)
at clojure.lang.Var.invoke(Var.java:388)
at metabase.core$run_cmd.invokeStatic(core.clj:133)
at metabase.core$run_cmd.invoke(core.clj:131)
at metabase.core$_main.invokeStatic(core.clj:142)
at metabase.core$_main.doInvoke(core.clj:138)
at clojure.lang.RestFn.applyTo(RestFn.java:137)
at metabase.core.main(Unknown Source)
Command failed with exception: Row {1} not found in primary index “PUBLIC.SYS_DATA: 17753” [90143-197]
please help me i am really desperate, my mv.db size grown up to 25GBs…!!!
I cannot see which version of Metabase you’re using, nor the version of Java.
I think you need to compact your H2 before trying to migrate, since it seems like it has grown to a size, where Windows doesn’t handle it correctly anymore.
Metabase DB cleanup?
Upgrade Java - version 7 is EOL and not supported anymore. I would recommend AdoptOpenJDK 11:
https://adoptopenjdk.net/releases.html?variant=openjdk11&jvmVariant=hotspot
Remember to backup before doing anything, since you might risk corrupting your database.
Before migrating, then upgrade to latest release, 0.35.4
And then shutdown Metabase, which should normally run some parts of the cleanup process.
You might want to check which tables are huge in size - and perhaps truncate them, if they’re not needed - https://razorsql.com/
And then try to run the migration process.
i took a safest path i could,
first i install postgres on my local pc and took backup of complete backup of metabase folder including metabase.db.mv.db and metabase.db.trace.db (this is 24GBs)
then i made a user in postgres for metabase and a blank db for metabase
uninstall any java i have and then install only https://adoptopenjdk.net/releases.html?variant=openjdk11&jvmVariant=hotspot
and downloaded updated metabase.jar file
use following command:
java -DMB_DB_TYPE=postgres -DMB_DB_DBNAME=metabase -DMB_DB_PORT=5432 -DMB_DB_USER=metabase -DMB_DB_PASS=metabase -DMB_DB_HOST=localhost -jar metabase.jar load-from-h2 D:<path>\metabase\metabase.db
this migrate from H2 to my local postgres DB successfully
then i use same command and point it to my production PostgreSQL server, when it successfully migrate, i stoped production server (which is on ubuntu 16.04) edit my metabase.service file and provide following environment variables in my systemctl service file
MB_DB_TYPE=postgres
MB_DB_DBNAME=metabase
MB_DB_PORT=5432
MB_DB_USER=XXX
MB_DB_PASS=XXX
MB_DB_HOST=localhost
reload systemctl daemon and start metabase.service
and it worked…!!!
then i manually delete metabase.db.mv.db and metabase.db.trace.db
