Space requirement for migration from h2 to rds/mysql

Get Help
1

Hi,

I wish to migrate the metabase db from H2 to mysql in AWS RDS.
Currently, the metabase.db.mv.db file is 4.2G and I have only 2.9G on this server.
I have of course plenty of space in rds, but what are the local space requirements for this migration?

java -jar /usr/local/bin/metabase/metabase.jar load-from-h2 /path/metabase.db

Am I right to assume that there wil remain a local metabase.db.mv.db file after the migration?
So that the link my users use to access metabase will remain the same?

Thanks,
Marc

2

Hi @mgirod
I highly doubt that you actually have 4.2GB worth of data in H2, but likely it has never been optimized and compacted.
Metabase tries to run a compact and defrag on shutdown, but there's a timeout on those commands, which your setup might have exceeded, so it won't be able to finish those anymore, which is why your H2 file has grown in size.

The load-from-h2 is a one-off process and will not change the H2 - it will just send the data from H2 to your MySQL/Postgres, so the H2 file will still exist, meaning you will have to manually delete it after if you want.
You can even copy the H2 to your local computer and do the migration there if you wanted.

Migrating the application database does not change anything besides making sure Metabase is running on a production ready database.
For reference: https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html

1 Like
3

Thank You flamber.

I did read the reference you gave me.
This host with the large h2 file, is the one my users access:

[root@ip-172-31-55-45 www]# lsof -i:3000
COMMAND   PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    13874 root   23u  IPv6 8954801      0t0  TCP *:hbci (LISTEN)

So, if not in the db file, where will metabase record the fact that the data will after the migration be found in RDS (if I delete the original db file)?
How will the migration be rollbackable?

Thanks!
Marc

4

@mgirod I don't understand. After you have migrated the data from H2 to RDS, then you setup Metabase to use that RDS as the application database:
https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html

There is no roll-back, since the RDS could just be deleted and redone, and the H2 is preserved if you wanted to roll-back.

But as with any big changes - make sure you have made backups first.

1 Like
5

Thanks again flamber. OK, hopefully now I got it. All the configuration is in those environment variables. So the rollback is just to switch them back to point to the local h2 default. Any way to produce a smaller backup? By setting large timeouts at shutdown?

6

@mgirod I use H2 for a lot of test instances, and when I need to clean them up, then I run the following via DBeaver (make sure to use the exact same driver version as Metabase, which is currently 1.4.197, otherwise you'll end up corrupting the H2!)

SCRIPT TO 'h2export.sql';
DROP ALL OBJECTS;
RUNSCRIPT FROM 'h2export.sql';
SET WRITE_DELAY 30000;
SHUTDOWN COMPACT;

# open the H2 connection again
SET WRITE_DELAY 30000;
SHUTDOWN DEFRAG;

Again, let me make it perfectly clear: You should never use H2 in production.

1 Like
7

Thanks. Conversion/migration successful.
As a side-effect, I lost the http://...:3000 access -- leaving https://...:8443 which is fine.
Was it to be expected?

8

@mgirod I don't know how you are running Metabase, but you define the ports available:
https://www.metabase.com/docs/latest/operations-guide/environment-variables.html#mb_jetty_port
I would generally recommend handling certificates in a reverse-proxy instead of via Java.
But there should also only be a single way into Metabase to avoid other problems, since the Site URL you define in Admin > Settings > General is a critical setting.

1 Like