Metabase provides limited support for migrating from H2 to Postgres or MySQL if you decide to upgrade to a more production-ready database. See Migrating from H2 to MySQL or Postgres for more details. H2 (default) For production installations of Metabase we recommend that users replace the H2 database with a more robust option such as Postgres. I am doing a transition from H2 database to PostgreSQL. As I found out - the SQL dump which is created when doing SCRIPT TO command on H2 contains several inappropriate constructions: Unicode data. My understanding is that metabase by default uses a H2 database to store its configuration. In order to not lose your config on restart you need to mount a volume to store it. The next step up is to move the config to an external database by setting the environment variables like in this compose file. H2: PostgreSQL; DB-Engines blog posts: PostgreSQL is the DBMS of the Year 2020 4 January 2021, Paul Andlinger, Matthias Gelbmann. PostgreSQL is the DBMS of the Year 2018 2 January 2019, Paul Andlinger, Matthias Gelbmann. PostgreSQL is the DBMS of the Year 2017 2 January 2018, Paul Andlinger, Matthias Gelbmann. Show all; Recent citations in the news.
Convert H2 to PostgreSQL All source tables are selected for conversion by default. In case you only need some tables, simply deselect the ones you don't need - or deselect all, then select just the tables you do need. Click the Copy your database button to get started.
At Clever Cloud we manage most of our own data and when we want to gather a particular information, we open our SQL interpreter and query all the things manually. This somehow worked because most of us are technical but it's not necessary the case anymore. So we want a nice dashboarding solution to make data available in a nicer way. This is how we came upon Metabase.
What is Metabase? In their own words:
The fastest, easiest way to share data and analytics inside your company.
You can configure different questions/queries/visualisations and organize them in dashboard. Take a look at their documentation to grasp the full extend of what you can do.
How to deploy Metabase
Metabase is written in java and available as a jar you can download and requires a PostgreSQL database. In Clever Cloud terms it means you will need a Java runtime and a PostgreSQL addon. It's dead easy to setup. Here's what you need to do to deploy it:
Now you should see the logs showing up. Once it's over you can type clever open and it will take you straight to your Metabase instance. You should see a wizard to help you create your admin user and connect a first database. If you ave set the MB_ENCRYPTION_SECRET_KEY variable, don't wory the databases credentials you enter in the Metabase will be encrypted.
Now you have a lot of things to do. And there is a lot more you can configure as you will see in their admin guide: Enable Emailing, Slack integration, add new authentication connectors... With that you should be ready to setup and query most of the datasources available to you and produce awesome dahsboards. Happy hacking!
By clicking 'Get started' I agree to Clever Cloud's Terms and Conditions
Migrating an existing GoCD Server installation to Postgres
Step 1: Upgrade, backup and stop the GoCD Server
Step 1.1: Upgrade the GoCD Server to the latest version.
Step 1.2: Then, backup the H2 database and configuration using the One Click Backup feature of GoCD. During the migration process from H2 to Postgres, data from the backed-up H2 database will be exported into CSV files and no modifications will be done on the H2 database.
Step 1.3: Stop the GoCD Server and ensure that the cruise.lock.db file does not exist under the db/h2db directory, indicating a clean shutdown has finished.
Step 2: Get everything ready for migration
2.1 Create a new directory for migration. In this document, the chosen directory will be /tmp/migration and it will be referred to as the “migration location” for the rest of this document.
2.2 Place the PostgreSQL add-on for GoCD into the migration location. Copy cruise.h2.db (from the backup or from /var/lib/go-server/db/h2db) to the migration location.
2.3 Create a directory called “config” in the migration location (if the migration location is /tmp/migration, the config directory should be /tmp/migration/config). In that directory, place all the configuration files needed to tell GoCD about the Postgres instance it needs to use. At a minimum, this needs to contain a file called “postgresqldb.properties”. More information about the format of these files and valid keys in them can be found in the section “Add-on configuration reference”.
2.4 Create an empty database on the Postgres instance. This needs to have the same name as the db.name key in the postgresqldb.properties file from Step 2.3. Here is an example:
Please ensure PostgreSQL database supports recommended UTF-8 encoding.Step 3: Run the migration!
Make sure that you’ve created an empty database on the Postgres instance as mentioned in Step 2.4 above.
At this point, the migration directory should look like the one below:
The migration can now be run from the command-line, in the migration directory:
On Windows, the arguments to the command will be the same. The locations will need to be Windows-specific (like C:tmpmigration).
Step 4: Use GoCD with the newly migrated database
Metabase Migrate H2 To Postgresql Server
After a successful migration, the steps mentioned in Enabling GoCD to use Postgres section can now be followed to setup GoCD to use Postgres. “Step 1: Initialize Postgres with an empty database” can be ignored, since Postgres is already initialized with the migrated data.
Migrating an existing GoCD Server Installation to use PostgreSQL Database on Amazon RDS
During the database migrations process, since the rds_superuser isn’t a full superuser, it can cause the migrations to fail. To avoid this the users can follow the below mentioned steps
Bring up a local PostgreSQL server. Using a database superuser account, perform the migration on this local PostgreSQL server.
Once the migration is successful, take a database dump of the migrated DB:
Create a PostgreSQL database on RDS and make the superuser (who is part of rds_superuser group), the owner of that database.
Using the
<db-dump-file-name>.sqlcreated in step 2 above, restore the PostgreSQL database on your Amazon RDS instance
The above command will prompt for <rds-pg-db-user-password>.
Once the database is restored you can point your GoCD server to that Amazon RDS instance.
Metabase Migrate H2 To Postgresql Database
Please contact support for any queries or issues.