Does anyone know how to save Metabase queries and dashboards? I am currently hosting Metabase on an instance on AWS Cloud Server and would like to know how I can save the queries in the event where if my instance stops, I can retrieve my dashboards and queries created.
Would appreciate any inputs provided!
Hi @hyo
I’m guessing you mean EC2 on AWS? It depends on how you did the setup.
Post “Diagnostic Info” from Admin > Troubleshooting, since that will tell a lot about your setup.
Everything is stored in the Metabase application database:
https://www.metabase.com/docs/latest/operations-guide/backing-up-metabase-application-data.html
Yep, I meant EC2 on AWS. Where would I find the file named metabase.db.h2.db or metabase.db.mv.db?
Here’s the Diagnostic Info:
{
“browser-info”: {
“language”: “en-US”,
“platform”: “Win32”,
“userAgent”: “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_271-b09”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_271”,
“java.vm.name”: “Java HotSpot™ Client VM”,
“java.vm.version”: “25.271-b09”,
“os.name”: “Windows Server 2019”,
“os.version”: “10.0”,
“user.language”: “en”,
“user.timezone”: “UTC”
},
“metabase-info”: {
“databases”: [
“h2”,
“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”: {
“tag”: “v0.37.2”,
“date”: “2020-11-16”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: null
}
}
}
@hyo The H2 file metabase.db.mv.db is usually located in the same directory as metabase.jar on your Windows Server 2019. I don’t know where you have placed that.
But you should migrate away from H2 if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
@flamber does that mean that currently all my Metabase queries are stored in the H2 database? How do I then restore the data from the metabase.db.mv.db file?
Would anything happen if I’m using Metabase in production but did not migrate from the H2 database?
@hyo
Yes, you are using H2 for the application database - look in your “Diagnostic Info”.
If you use H2, then you “restore” by replacing the file with one from your backup and starting Metabase.
Yes, you will likely end up with a corrupted H2 database at some point - if you search the forum, you’ll find many posts about that.
Thanks @flamber for your assistance! I should definitely migrate from H2 to prevent a me from getting a corrupted database…
I have been looking into migrating into Postgres but I’m really new to Metabase and databases in general. Also, there isn’t much information on that link you previously provided: https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
Do you know if I have to create a new cloud database instance (eg: Amazon RDS) for me to migrate my data in the H2 database into the RDS Postgresql instance? Is there any documentation on how can I do so?
@hyo You can create a RDS and then migrate to that by inputting the database credentials as noted in the migration guide.
I would highly recommend that you consider Metabase Cloud, where operations are handled for you:
https://www.metabase.com/start/hosted/
https://www.metabase.com/blog/migrating-to-metabase-cloud/index.html
Hi @flamber, I have created a Postgres RDS instance but how should I migrate to it? Do I need to create a new database in the instance first before the migration? In the migration guide, it mentions that I have to run the data migration command:
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
java -jar metabase.jar load-from-h2 /path/to/metabase.db
Do you know what do the username and password refer to? I entered the RDS database username and password but there was a prompt saying that it is an incorrect password.
EDIT: I created a separate instance to test out the migration and managed to migrate it to postgres but I’m not sure if I did it correctly because I followed the exact commands as above. Should the export MB_DB_HOST=localhost or should the host be my RDS host?
Do I have to run the migration command everytime I start Metabase? In other words, do I run this every time I reload Metabase (eg: If I do an update etc):
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
java -jar metabase.jar load-from-h2 /path/to/metabase.db
Because I noticed that if I run java -jar metabase.jar, the application database changes to H2 again even though I have already migrated it to postgres
@hyo You migrate only once. Yes, you need to use the exact same credentials in the environment variables for your RDS as you would if you tried to connect to your RDS with any other program, so you need to change host.
After you have migrated, then you need to start Metabase with variables, so it points to your new application database:
https://www.metabase.com/docs/latest/operations-guide/configuring-application-database.html
Thanks @flamber. I have successfully migrated to postgresql but I’m not sure if I did it correctly as I am still facing 2 issues:
Immediately after the migration to postgres is completed, when I run java -jar metabase.jar, Metabase starts up correctly and under the diagnostic info, the application database is postgres.
But if I shutdown Metabase and restart Metabase using export MB_DB_CONNECTION_URI="postgres://localhost:5432/metabase?user=<username>&password=<password>", the application database changes back to h2…
How should I solve this?
@hyo Then use Java parameters, when you start Metabase if you are having problems with environment variables.
java -DMB_DB_CONNECTION_URI="postgres://localhost:5432/metabase?user=<username>&password=<password>" -jar metabase.jar
For reference: https://www.metabase.com/docs/latest/operations-guide/environment-variables.html
Hi @flamber, should I enter the “<>” for my username and password as well?
I am getting an error that tells me that Metabase fails to start and it shuts down
@hyo No, it’s placeholders. If your username/password is THIS-IS-MY-INPUT, then it would look like this:
java -DMB_DB_CONNECTION_URI="postgres://localhost:5432/metabase?user=THIS-IS-MY-INPUT&password=THIS-IS-MY-INPUT" -jar metabase.jar