How to install production Metabase on Ubuntu

In this tutorial, you will install ** Metabase ** on your ** Alibaba Cloud ECS Ubuntu 16.04 ** server for data visualization.

Alibaba Cloud Tech Share Written by Liptan Biswas. Tech Share is an Alibaba Cloud incentive program that encourages sharing of technical knowledge and best practices within the cloud community.

Metabase is an open source application that allows you to visualize your database and gain insights from it. Metabase provides an intuitive and easy-to-use web-based interface for querying databases without writing SQL statements. It works with almost any General Database (https://www.alibabacloud.com/product/apsaradb-for-rds?spm=a2c65.11461447.0.0.1e0c17a2MfqgQT) such as MySQL/MariaDB, Postgres, Mongo, SQL Server, Druid, H2, SQLite, Oracle, etc. Data can be retrieved in tables, graphs and charts.

This tutorial is divided into two parts. The first part of the tutorial is to install Metabase on your Ubuntu 16.04 server. Use PostgreSQL to host the Metabase database. Also, configure Nginx as a reverse proxy and use Let's Encrypt SSL to protect your Metabase instance.

In Part 2 of the tutorial (https://www.alibabacloud.com/blog/database-visualization-using-metabase-part-1---install-metabase-on-ubuntu-1604_592155), you will learn the basics of using the platform. Also, set the mail to set the plus.

Required requirements

--Ubuntu 16.04 64-bit is installed on Alibaba Cloud ECS Instance. --Firewall or security group rules configured to allow ports "80", "443". -Point to ECS instance Must be Domain name.

Follow the Quick Start Guide (https://www.alibabacloud.com/help/doc-detail/25422.htm?spm=a2c65.11461447.0.0.1e0c17a2MfqgQT) for instructions on creating and connecting to your instance. This tutorial creates an Alibaba instance and assumes that "192.168.0.101" is the public IP address assigned to your Ubuntu instance. Also, "metabase.example.com" is set to point to the Ubuntu instance. After SSHing into the instance, run the following command to update the repository cache and base system.

apt update && apt -y upgrade & apt -y autoremove

Java installation

Since Metabase is written in Java, you need to install the Java runtime to run Metabase. Metabase supports both Oracle Java 8 and OpenJDK 8. This tutorial installs Java runtime version 8 of OpenJDK.

apt -y install openjdk-8-jre

Run java -version to see if Java is installed successfully.

root@aliyun:~# java -version
openjdk version "1.8.0_162"
OpenJDK Runtime Environment (build 1.8.0_162-8u162-b12-0ubuntu0.16.04.2-b12)
OpenJDK 64-Bit Server VM (build 25.162-b12, mixed mode)

Find the directory where Java is installed on your system.

readlink -f /usr/bin/java | sed "s:/jre/bin/java::”

You should see output similar to the following.

root@aliyun:~# readlink -f /usr/bin/java | sed "s:/jre/bin/java::"
/usr/lib/jvm/java-8-openjdk-amd64

Here, set the environment variable JAVA_HOME according to the path where Java is installed.

echo "export JAVA_HOME=/usr/lib/jvm/java-8-openjdk-amd64" | tee -a /etc/profile
source /etc/profile

Execute echo $ JAVA_HOME to check if the JAVA_HOME variable is set. Should be displayed.

root@aliyun:~# echo $JAVA_HOME
/usr/lib/jvm/java-8-openjdk-amd64

Set up ApsaraDB for PostgreSQL

By default, Metabase is configured to use the H2 database. The H2 database is a flat file based database and does not require any special software to run. However, using an H2 database in a multi-user production environment is not recommended as it degrades application performance. This tutorial uses the ApsaraDB for RDS (https://www.alibabacloud.com/product/apsaradb-for-rds?spm=a2c65.11461447.0.0.1e0c17a2MfqgQT) PostgreSQL server to host the Metabase database. Go to https://rdsnew.console.aliyun.com and create a new RDS instance for PostgreSQL. Select your subscription type and select your region. Creating an RDS instance in the same region where your ECS instance is located gives you a lot of leverage, including the ability for ECS and RDS instances to communicate using your intranet's private address. You do not need to apply for an internet address, and intranet data transfer is free. In this tutorial, we created an RDS instance in the Mumbai region, which also has an ECS instance.

Select DB Engine as PostgreSQL and select version 9.4. Select the zone and VPC where your ECS instance is located. Select the instance type and capacity. You can increase the instance type and capacity later if needed.

image.png

After purchasing an instance, it will take a few minutes for the instance to become active. Once the instance is active, click the "Manage" link to switch to the "Security" tab. Now add a new whitelist group, name the new group, and enter the private or intranet IP address of your ECS instance. Since we just whitelisted the ECS instance, the database server will only be accessible from the ECS instance.

image.png

Now, switch to the "Accounts" tab and create a new master account for your PostgreSQL server with the username "postgres". This master account is used to create new users and databases from the PSQL shell.

Finally, go to the Connection Options tab and find the intranet address and port assigned to your RDS instance. Make a note of the intranet address and port, as you will need them later in the tutorial.

image.png

Now that you have a PostgreSQL server instance ready, let's move on to installing Metabase.

Install Metabase

Metabase is cross-platform and provides Java executables that you can easily run in your terminal. Find the link to the latest release of your application on the Metabase download page (https://www.metabase.com/start/jar.html?spm=a2c65.11461447.0.0.1e0c17a2MfqgQT) and use the following command to download Metabase.

wget http://downloads.metabase.com/v0.28.6/metabase.jar

Create a new unprivileged user to run the Metabase Java executable. The Metabase executable also launches the built-in Jetty web server, so it's a good idea to use an unprivileged user to secure your system.

adduser --home /var/metabase --gecos="Metabase User" --disabled-login --disabled-password metabase

Move the downloaded executable to the "metabase" user's home directory.

mv metabase.jar /var/metabase

Before starting the Metabase service, let's check if the ECS instance can connect to the remote RDS instance. You also need to create a database and database user for Metabase.

Install the PostgreSQL client.

apt -y install postgresql-client

Use the following command to connect to the remote PostgreSQL instance you created. Replace the intranet address with the real address assigned to your instance. When prompted, enter the password for the "postgres" master user you created earlier.

psql -h rm-6gjlyl343w252w65g.pgsql.ap-south-1.rds.aliyuncs.com -p 3433 -U postgres -d postgres

If the connection is successful, you can log in to the "psql" shell and run queries on the database server. If you follow the tutorial correctly, you should have no problems connecting. You will get the following output.

root@aliyun:~# psql -h rm-6gjlyl343w252w65g.pgsql.ap-south-1.rds.aliyuncs.com -p 3433 -U postgres -d postgres
Password for user postgres: 
psql (9.5.12, server 9.4.10)
Type "help" for help.

postgres=> 

Now create a new database user for Metabase. Replace Strong Password with a very strong password.

CREATE USER metabase WITH PASSWORD ‘StrongPassword'

Create a database.

CREATE DATABASE metabasedb.

Grant all permissions to the "metabase" user for the database "metabasedb".

GRANT ALL PRIVILEGES ON DATABASE metabasedb to metabase;

Enter the q command to log out to the root user's shell. Since Metabase uses PostgreSQL as the database server instead of the default H2 database, you need to configure Metabase to use the PostgreSQL server. Metabase reads the configuration parameters from the environment variables. Now that you have Metabase installed for production, set up the systemd service unit to run your application. The systemd service can read environment variables from a file. Create a new file to store your Metabase environment variables.

nano /var/metabase/metabase.env

Fill the file with the following contents. Change the value according to your settings and preferences. Parameter descriptions are provided as comments.

# Password complexity for Metabase user, allowed values <weak|normal|strong>
MB_PASSWORD_COMPLEXITY=normal
# Password length for Metabase user
MB_PASSWORD_LENGTH=8

# Host and Port on which the inbuilt jetty server listens, 
# Leave it unchanged
MB_JETTY_HOST=localhost
MB_JETTY_PORT=3000

# Provide Intranet or Private IP address of PostgresSQL server
MB_DB_TYPE=postgres
MB_DB_HOST=rm-6gjlyl343w252w65g.pgsql.ap-south-1.rds.aliyuncs.com
MB_DB_PORT=3433

# Provide the database name
MB_DB_DBNAME=metabasedb
# Provide the username of database user
MB_DB_USER=metabase
# Provide the password of database user
MB_DB_PASS=StrongPassword

# Setting it true will include emojis in logs, to disable set it to false
MB_EMOJI_IN_LOGS=true

Save the file and exit the editor. Provides ownership of the file to the "metabase" user.

chown metabase:metabase -R /var/metabase

Managing Metabase through the systemd service simplifies the process of starting and stopping applications. It also ensures that the application starts automatically in the event of a system restart or process failure. Create a new systemd unit file.

nano /etc/systemd/system/metabase.service

Fill the file with the following contents.

[Unit]
Description=Metabase server
After=syslog.target
After=network.target

[Service]
EnvironmentFile=/var/metabase/metabase.env
User=metabase
Group=metabase
Type=simple
ExecStart=/usr/bin/java -jar /var/metabase/metabase.jar
Restart=always
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=metabase

[Install]
WantedBy=multi-user.target

You can start it by executing it.

systemctl start metabase

To have the Metabase server start automatically at startup, run it.

systemctl enable metabase

You can check the status of the service by executing it.

systemctl status metabase

The first time you start the Metabase server, your application writes the database to the PostgreSQL server. It may take a few minutes for the application to initialize for the first time. You can monitor the status of the application by checking the syslog with the following command.

journalctl -f -e -u metabase

If you see the following line in the log, your application is ready.

Apr 23 21:30:34 aliyun metabase[30486]: 04-23 21:30:34 INFO metabase.core :: Metabase Initialization COMPLETE

Install Nginx

Metabase has a built-in Jetty web server for servicing your applications, but it is not recommended to publish such a web server on the Internet in a production environment. The best way is to set up a production grade web server such as Nginx or Apache on the front and proxy the request to the Jetty server. In this tutorial, we will install the Nginx web server as a reverse proxy to the Metabase server.

Install the Nginx web server.

apt -y install nginx

Start Nginx so that the server starts automatically at startup.

systemctl start nginx
systemctl enable nginx

It is also important to protect the web server with SSL/TLS encryption, as logins and other important data are sent from the browser session to the web server and vice versa. If the data exchanged is not encrypted, the data in the network can be eavesdropped. This tutorial uses the Let's Encrypt CA's free SSL certificate. If you want to use more production-friendly and reliable SSL, you can Purchase SSL Certificate from Alibaba.

Let's Encrypt provides "Certbot", a tool that makes it easy to request and generate certificates. Add the Certbot repository and install Certbot.

apt -y install software-properties-common
add-apt-repository --yes ppa:certbot/certbot
apt update
apt -y install certbot

In order for Certbot to verify ownership of a domain, it is important that the domain is pointed to an ECS instance. Otherwise, no domain certificate will be generated. Use Certbot to request a certificate.

Certbot certonly --webroot -w /var/www/html -d metabase.example.com

When the certificate is generated, you should see output similar to the following:

Obtaining a new certificate
Performing the following challenges:
http-01 challenge for metabase.example.com
Using the webroot path /var/www/html for all unmatched domains.
Waiting for verification...
Cleaning up challenges

Notes:

 - Congratulations! Your certificate and chain have been saved at:
   /etc/letsencrypt/live/metabase.example.com/fullchain.pem
   Your key file has been saved at:
   /etc/letsencrypt/live/metabase.example.com/privkey.pem
   ...

Create a cron job for automatic renewal before the certificate expires.

{ crontab -l; echo '36 2 * * * * /usr/bin/certbot renew --post-hook "systemctl reload nginx"'; } } | crontab -l

Create a new Nginx server block configuration for the reverse proxy for Metabase.

nano /etc/nginx/sites-available/metabase

Enter the following settings in the editor. Replace all occurrences of the example domain with the actual domain.

server {
    listen 80;
    server_name metabase.example.com;
    return 301 https://$host$request_uri;
}

server {
    listen 443;
    server_name metabase.example.com;

    ssl_certificate           /etc/letsencrypt/live/metabase.example.com/fullchain.pem;
    ssl_certificate_key       /etc/letsencrypt/live/metabase.example.com/privkey.pem;

    ssl on;
    ssl_session_cache  builtin:1000  shared:SSL:10m;
    ssl_protocols  TLSv1 TLSv1.1 TLSv1.2;
    ssl_ciphers HIGH:!aNULL:!eNULL:!EXPORT:!CAMELLIA:!DES:!MD5:!PSK:!RC4;
    ssl_prefer_server_ciphers on;

    gzip  on;
    gzip_http_version 1.1;
    gzip_vary on;
    gzip_comp_level 6;
    gzip_proxied any;
    gzip_types text/plain text/html text/css application/json application/javascript application/x-javascript text/javascript text/xml application/xml application/rss+xml application/atom+xml application/rdf+xml;
    gzip_buffers 16 8k;
    gzip_disable “MSIE [1-6].(?!.*SV1)”;
    
    access_log  /var/log/nginx/metabase.access.log;
    
location / {
    proxy_pass            http://localhost:3000;        
    proxy_set_header    host $host;
    proxy_http_version  1.1;
    proxy_set_header upgrade $http_upgrade;         
    proxy_set_header connection "upgrade";     
    } 
}

Run to activate the config file.

ln -s /etc/nginx/sites-available/metabase /etc/nginx/sites-enabled/metabase

You can check the configuration file for errors by running nginx -t.

root@aliyun:~# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful

Restart the Nginx web server for the configuration changes to apply.

systemctl restart nginx

You can now access your Metabase instance by browsing "https://metabase.example.com" from your favorite browser. You should see a welcome screen from Metabase. Enter basic information about the administrator account and organization.

image.png

You will be prompted to add the database on the first run. Skip adding new databases. When you log in to Metabase, you will see a similar dashboard.

image.png

Conclusion

In this detailed tutorial, you installed the Metabase web application on an Ubuntu 16.04 server. We've seen how to create a PostgreSQL RDS instance on ApsaraDB for RDS (https://www.alibabacloud.com/product/apsaradb-for-rds?spm=a2c65.11461447.0.0.1e0c17a2MfqgQT). I set up Nginx as a reverse proxy and secured it with Let's Encrypt SSL. Your organization is now ready to use your Metabase instance.

In Part 2 of the tutorial (https://www.alibabacloud.com/blog/database-visualization-using-metabase-part-2---use-metabase-to-get-insights_592508?spm=a2c65.11461447.0.0.1e0c17a2MfqgQT), you will learn the basics of using Metabase while running sample queries on a sample database. You will also learn how to set up email and create pulses in Metabase. For information on how to use Metabase, please refer to the Official User Guide.

Recommended Posts

How to install production Metabase on Ubuntu
How to install WildFly on Ubuntu 18.04
How to install network drivers on standalone Ubuntu
How to install NVIDIA driver on Ubuntu 18.04 (Note)
How to install multiple JDKs on Ubuntu 18.04 LTS
How to install NVIDIA driver on Ubuntu ssh destination
How to use Bio-Formats on Ubuntu 20.04
How to install MariaDB 10.4 on CentOS 8
How to build vim on Ubuntu 20.04
How to Install Elixir and Phoenix Framework on Ubuntu 20.04 LTS
How to install java9 on elementaryOS Freya or Ubuntu 14.04 LTS
How to install Eclipse (Photon) on Mac
I want to install PHP 7.2 on Ubuntu 20.04.
How to install beta php8.0 on CentOS8
How to change the timezone on Ubuntu
How to install kafkacat on Amazon Linux2
How to install and configure the monitoring tool "Graphite" on Ubuntu
How to install Adopt OpenJDK on Debian, Ubuntu with apt (-get)
Install Ruby on Ubuntu 20.04
How to install Docker
How to install docker-machine
Install Autoware on Ubuntu 18.04.5
How to install MySQL
Install Homebrew on Ubuntu 20.04
How to install ngrok
How to configure ubuntu to be used on GCP
How to Install Oracle JDK 1.8 in Ubuntu 18.04 LTS?
[Ruby on Rails] How to install Bootstrap in Rails
How to build a Pytorch environment on Ubuntu
How to run NullpoMino 7.5.0 on Ubuntu 20.04.1 64bit version
How to install c2ffi on Ubuntu, which automatically parses C header files
How to install Ruby on an EC2 instance on AWS
Install OpenJDK7 (JAVA) on ubuntu 14.04
Install Cybozu Office 10 on Ubuntu 20.4
Install Docker on Ubuntu Server 20.04
[Rails] How to install devise
How to deploy on heroku
Install zabbix agent (5.0) on Ubuntu 18.04
Install MAV Proxy on Ubuntu 18.04
Install Arudino IDE on Ubuntu 20
Install Java on WSL Ubuntu 18.04
How to install Boots Faces
Install Ubuntu Desktop 20.10 on RaspberryPi4
Install Arduino IDE on Ubuntu 20.04
Install raspi-config on Ubuntu 20.04 (LTS)
Install WordPress 5.5 on Ubuntu 20.04 LTS
Install MySQL 5.6 on CentOS6 [How to specify the version]
Install PlantUML on Intellij on Ubuntu
Install Ubuntu Server 20.04 on Btrfs
Note: Install PostgreSQL 9.5 on Ubuntu 18.04
[Rails] How to install simple_calendar
[Rails] How to install reCAPTCHA
How to install JDK8-10 (Mac)
How to add HDD to Ubuntu
How to run npm install on all projects in Lerna
How to Burning a Install Disk of Windows from Ubuntu
How to install JDK 8 on Windows without using the installer
How to install GNOME as a desktop environment on CentOS 7
How to deploy jQuery on Rails
[Rails] How to install Font Awesome
How to deploy Laravel on CentOS 7