How to setup Metabase with PostgreSQL and Docker Compose
Metabase is a wonderful SQL tool for relational data analysis. Here is how to set up a stable instance of Metabase using PostgreSQL and Docker Compose.
What is this Metabase thing about?
Have you ever had a huge load of data lying under-utilized in some database somewhere? Well, I have.
Any attempt to try to derive useful information from the data would mean I had to put in 16-plus hours of development work.
What made things worse was that I had to do all sorts of DevOps work on top of my pandas/numpy data science stuff. It was stressful.
In came Metabase!
Metabase is an open-source java-based software that enables users to analyse data from a range of data sources including relational databases (e.g. PostgreSQL, MySQL etc.), NoSQL databases etc.
Analyzing data in Metabase is simple. One can choose to simply click a few aggregations like sort by, filter by etc.
For power users who know their way around SQL, there is an SQL editor for any data science question they might have.
Metabase has so many fancy features. I don’t wish to sell it short so let me direct you to its home page.
Okay, What of this PostgreSQL thing?
When we talk of data, it can be structured or unstructured. Structured data is that which is formatted in a consistent way that makes it addressable for more processing.
In simple English, structured data is the kind composed of smaller blocks that can easily be uniquely identified e.g. using an ID.
Such data is usually stored in a database.
There are many kinds of databases and PostreSQL is one of them. Others may include MySQL, Oracle, MongoDB and the like.
Going into more detail, you will find that there are types of databases; relational and NoSQL but I don’t want to get into that right now.
Check out the PostgreSQL website for more information.
And Docker Compose?
One of the commonest buzz words in the tech space is containerization.
According to the Docker website, a container is a ‘standardized unitof software’.
A container is a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another — Docker website
Docker Compose is a tool for defining and running multi-container Docker applications. — Docker Compose page
In our case, we will have a Docker container containing Metabase and another one containing PostgreSQL.
These two containers need to both run at the same time thus the need for Docker Compose.
Do I have to use PostgreSQL?
By default, Metabase stores application-specific data e.g. users, stored queries, configurations etc. on an H2 database on the same file system where it (Metabase) is installed.
However, it is difficult to retrieve this data once the application crushes.
I saw this happen to me. It wasn’t pretty.
After spending more than three hours configuring my Metabase server, I wrote one terribly wrong SQL query and asked Metabase to calculate.
It tried, failed and crushed!
When I attempted to restart it, there was no trace of all my hard work. I had to start over again.
I decided to configure Metabase to save its application data on a PostgreSQL instance and set the Metabase Container to restart always.
I no longer notice the server crushes.
They probably happen but Docker restarts my Metabase container and it uses the data in the PostgreSQL database to recover to its original state.
I’m all caught up, let’s do this.
Let’s now setup Metabase with PostgreSQL and Docker Compose as promised.
I already configured the PostgreSQL and Metabase services on a github repository. The only thing that one has to do is to modify a few environment variables.
Here are the steps. The assumption is that we are running Ubuntu.
I am not worried though. The steps can easily be replicated on other operating systems too.
Here goes.
First, ensure docker is installed. If it is not installed, install it. Here are the instructions.
We also need docker compose installed on our system. Here are the installation instructions
Next, we clone the git repository from Github.
git clone https://github.com/Tinitto/compose-postgres-metabase.gitThen we enter the compose-postgres-metabase folder
cd compose-postgres-metabaseWe rename config/metabase_database.env.example to config/metabase_database.env.
mv config/metabase_database.env.example config/metabase_database.envAnd open the config/metabase_database.env file in a text editor like Visual Studio Code and update the environment variables MB_DB_PASS, MB_ENCRYPTION_SECRET_KEY.
MB_DB_PASS=<put_here_the_password_for_the_metabase_user>
MB_ENCRYPTION_SECRET_KEY=<Add a random string here as the secret>We do the same thing for the config/postgres.env.example file.
Rename it.
mv config/postgres.env.example config/postgres.envOpen it in a text editor and update the environment variables POSTGRES_PASSWORD , METABASE_PASSWORD.
POSTGRES_PASSWORD=<put_here_the_password_for_the_postgres_user>
METABASE_PASSWORD=<put_here_the_password_for_the_metabase_user>That is all for the configuration, we just need to start the services using a single Docker Compose command
sudo docker-compose up -dThe services may take a few seconds to warm up.
We can now set up our Metabase instance by visiting http://<server IP>:3000 and doing the necessary configuration.
