How to migrate from H2 to postgres

zig · June 24, 2021, 1:36am

Hi team

I tried to migrate from H2 to postgres, following Migrating from using the H2 database to MySQL or Postgres.

After

export MB_DB_TYPE=postgres
export MB_DB_DBNAME=test
export MB_DB_PORT=10245
export MB_DB_USER=test_user
export MB_DB_PASS=test@123
export MB_DB_HOST=10.90.56.36
java -jar metabase.jar load-from-h2 /mnt/data3/suzixin/metabase/metabase.db

The output was shown below.

Warning: environ value /mnt/data3/package/jdk-8u201 for key :java-home has been overwritten with /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-1.h5.oe1.aarch64/jre
2021-06-23 17:31:05,402 INFO metabase.util :: Maximum memory available to JVM: 26.6 GB
2021-06-23 17:31:15,697 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-06-23 17:31:22,197 INFO metabase.core :: 
Metabase v0.39.4 (f538050 release-x.39.x) 

Copyright © 2021 Metabase, Inc. 

Metabase Enterprise Edition extensions are NOT PRESENT.
2021-06-23 17:31:22,207 WARN metabase.core :: WARNING: You have enabled namespace tracing, which could log sensitive information like db passwords.
2021-06-23 17:31:22,259 INFO cmd.copy :: Set up h2 source database and run migrations... 
2021-06-23 17:31:22,261 INFO db.setup :: Verifying h2 Database Connection ...
2021-06-23 17:31:22,628 INFO db.setup :: Successfully verified H2 1.4.197 (2018-03-18) application database connection. ✅
2021-06-23 17:31:22,629 INFO db.setup :: Running Database Migrations...
2021-06-23 17:31:22,631 INFO db.setup :: Setting up Liquibase...
2021-06-23 17:31:22,691 INFO db.setup :: Liquibase is ready.
2021-06-23 17:31:22,691 INFO db.liquibase :: Checking if Database has unrun migrations...
2021-06-23 17:31:24,164 INFO db.setup :: Database Migrations Current ...  ✅
2021-06-23 17:31:24,164 INFO db.data-migrations :: Running all necessary data migrations, this may take a minute.
2021-06-23 17:31:24,175 INFO db.data-migrations :: Finished running data migrations.
Database setup took 1.9 s
2021-06-23 17:31:24,177 INFO cmd.copy :: [OK]
2021-06-23 17:31:24,177 INFO cmd.copy :: Set up postgres target database and run migrations... 
2021-06-23 17:31:24,178 INFO db.setup :: Verifying postgres Database Connection ...
2021-06-23 17:31:24,257 INFO db.setup :: Successfully verified PostgreSQL 10.5 application database connection. ✅
2021-06-23 17:31:24,257 INFO db.setup :: Running Database Migrations...
2021-06-23 17:31:24,262 INFO db.setup :: Setting up Liquibase...
2021-06-23 17:31:24,277 INFO db.setup :: Liquibase is ready.
2021-06-23 17:31:24,278 INFO db.liquibase :: Checking if Database has unrun migrations...
2021-06-23 17:31:24,732 INFO db.liquibase :: Database has unrun migrations. Waiting for migration lock to be cleared...
2021-06-23 17:31:24,781 INFO db.liquibase :: Migration lock is cleared. Running migrations...
2021-06-23 17:31:26,445 INFO db.setup :: Database Migrations Current ...  ✅
Database setup took 2.3 s
2021-06-23 17:31:26,447 INFO cmd.copy :: [OK]
2021-06-23 17:31:26,447 INFO cmd.copy :: Testing if target postgres database is already populated... 
2021-06-23 17:31:26,452 INFO cmd.copy :: [OK]
2021-06-23 17:31:26,457 INFO cmd.copy :: Temporarily disabling DB constraints... 
2021-06-23 17:31:26,483 INFO cmd.copy :: [OK]
2021-06-23 17:31:26,489 INFO cmd.copy :: Copying instances of Database...
2021-06-23 17:31:26,501 INFO cmd.copy ::  copied 2 instances.
2021-06-23 17:31:26,502 INFO cmd.copy :: Copying instances of User...
2021-06-23 17:31:26,503 INFO cmd.copy ::  copied 1 instances.
2021-06-23 17:31:26,504 INFO cmd.copy :: Copying instances of Setting...
2021-06-23 17:31:26,506 INFO cmd.copy ::  copied 9 instances.
2021-06-23 17:31:26,508 INFO cmd.copy :: Copying instances of Table...
2021-06-23 17:31:26,511 INFO cmd.copy ::  copied 5 instances.
2021-06-23 17:31:26,515 INFO cmd.copy :: Copying instances of Field...
2021-06-23 17:31:26,525 INFO cmd.copy ::  copied 37 instances.
2021-06-23 17:31:27,218 INFO cmd.copy :: Copying instances of FieldValues...
2021-06-23 17:31:27,222 INFO cmd.copy ::  copied 5 instances.
2021-06-23 17:31:27,224 INFO cmd.copy :: Copying instances of Revision...
2021-06-23 17:31:27,225 INFO cmd.copy ::  copied 2 instances.
2021-06-23 17:31:27,226 INFO cmd.copy :: Copying instances of ViewLog...
2021-06-23 17:31:27,228 INFO cmd.copy ::  copied 5 instances.
2021-06-23 17:31:27,228 INFO cmd.copy :: Copying instances of Session...
2021-06-23 17:31:27,230 INFO cmd.copy ::  copied 1 instances.
2021-06-23 17:31:27,230 INFO cmd.copy :: Copying instances of Collection...
2021-06-23 17:31:27,232 INFO cmd.copy ::  copied 3 instances.
2021-06-23 17:31:27,233 INFO cmd.copy :: Copying instances of Dashboard...
2021-06-23 17:31:27,236 INFO cmd.copy ::  copied 1 instances.
2021-06-23 17:31:27,238 INFO cmd.copy :: Copying instances of Card...
2021-06-23 17:31:27,240 INFO cmd.copy ::  copied 1 instances.
2021-06-23 17:31:27,241 INFO cmd.copy :: Copying instances of DashboardCard...
2021-06-23 17:31:27,242 INFO cmd.copy ::  copied 2 instances.
2021-06-23 17:31:27,243 INFO cmd.copy :: Copying instances of Activity...
2021-06-23 17:31:27,245 INFO cmd.copy ::  copied 4 instances.
2021-06-23 17:31:27,247 INFO cmd.copy :: Copying instances of PermissionsGroup...
2021-06-23 17:31:27,248 INFO cmd.copy ::  copied 3 instances.
2021-06-23 17:31:27,249 INFO cmd.copy :: Copying instances of PermissionsGroupMembership...
2021-06-23 17:31:27,250 INFO cmd.copy ::  copied 2 instances.
2021-06-23 17:31:27,250 INFO cmd.copy :: Copying instances of Permissions...
2021-06-23 17:31:27,252 INFO cmd.copy ::  copied 9 instances.
2021-06-23 17:31:27,254 INFO cmd.copy :: Copying instances of DataMigrations...
2021-06-23 17:31:27,256 INFO cmd.copy ::  copied 14 instances.
2021-06-23 17:31:27,257 INFO cmd.copy :: Re-enabling DB constraints... 
2021-06-23 17:31:27,257 INFO cmd.copy :: [OK]
2021-06-23 17:31:27,267 INFO cmd.copy :: Setting Postgres sequence ids to proper values... 
2021-06-23 17:31:27,289 INFO cmd.copy :: [OK]

After open OUR DATA -> postgres database, I haven't found tables in SAMPLE DATASET, such as Orders/People/Products/Reviews.

Is there something wrong while migration?

thanks

flamber · June 24, 2021, 9:59am

Hi @zig
I think you are misunderstanding what is being migrated.
When you migrate from H2 to Postgres/MySQL, then Metabase will use that as your application database, where it stores all it's information.
If you are looking to enable the Sample Dataset, then you do that in Admin > Databases > click the greyed link in the bottom.

zig · June 25, 2021, 9:41am

Hi @flamber

Do you have any idea about migrate data from h2 to Postgres?

Such as migrate Orders/People/Products/Reviews tables in h2 to Postgres, not just the Metabase info.

flamber · June 25, 2021, 9:43am

@zig Okay, that's a completely different question. Not sure why you want to migrate the Sample Dataset, but use another tool like DBeaver.io to export the H2 data and then import it into Postgres.