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.
- This blog is a translation from the English version. You can check the original from here. We use some machine translation. We would appreciate it if you could point out any translation errors. *
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.
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.
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.
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.
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.
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.
- Alibaba Cloud is the No. 1 (2019 Gartner) cloud infrastructure operator in the Asia-Pacific region with two data centers in Japan and more than 60 availability zones in the world. Click here for more information on Alibaba Cloud. Alibaba Cloud Japan Official Page *
Recommended Posts