| Growth | Data and Analytics | How to setup Metabase
How to setup Metabase

Option 1) Expose Your Existing Postgres Database

Locate your postgresql.conf file by running sudo find / -type f -name "postgresql.conf"

Edit postgresql.conf. In my case, the path was sudo nano /etc/postgresql/9.5/main/postgresql.conf

Change your Postgres to listen to external addresses. Under CONNECTIONS AND AUTHENTICATION, change #listen_addresses = 'localhost' the IP address you're exposing it to.

To expose Postgres to all IPs use

#listen_addresses = '*'

To expose Postgres to specified IPs use a comma separated list:

#listen_addresses = 'localhost, ip_address, ip_address'

Next, configure the PostgreSQL Client Authentication Configuration File sudo nano /etc/postgresql/9.5/main/pg_hba.conf to allow incoming requests.

To allow Postgres Client for all IPs use:

host    all             all                           md5
host    all             all              ::/0                            md5

To allow Postgres Client for specified IPs use:

host    all             all                           md5
host    all             all              ::/0                            md5

Restart Postgres to apply changes

sudo service postgresql restart

Check Postgres is running.

sudo service postgresql status

You should see a timestamp showing it was recently restarted:

Active: active (exited) since Fri 2019-10-04 14:55:44 UTC; 1min 51s ago

Next, check that Postgres is listening on 5432 on the desired IPs

netstat -tulpn | grep LISTEN

Depending on your configuration, you should see Postgres is accepting connections from localhost but also on the IPs you specified, in this case, shows that Postgres is accepting connections from all IPs.

tcp        0      0  *               LISTEN      -    
tcp        0      0    *               LISTEN      -               

Setup UFW Firewall

Enable firewall

sudo ufw enable  

To allow incoming traffic from an IP

sudo ufw allow from your_ip_address to any port 5432

Disable firewall

sudo ufw disable    

Check firewall status

sudo ufw status

More reading: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-18-04

Setup a Digital Ocean Firewall

Try remoting in to Postgres

psql -h psql_ip_address -U psql_username

If you encounter a timeout error such as psql: could not connect to server: Operation timed out there's likely a firewall blocking the connection to Postgres.

If you encounter a connection refused error such as psql: could not connect to server: Connection refused Postgres is likely not listening to your specified IP.

For additional reading: https://blog.bigbinary.com/2016/01/23/configure-postgresql-to-allow-remote-connection.html

Option 2) Migrate to a Managed Database Service

From: https://www.digitalocean.com/docs/databases/postgresql/quickstart

1) Go to your Digital Ocean Databases https://cloud.digitalocean.com/databases
2) Select Create, PostgreSQL 11, your node size (1GB, 1 VCPU, 10GB), Choose your datacenter (choose the same datacenter as your droplet).
3) Create a Database Cluster

Add Trusted Sources

Select the droplets which you want to communicate with this database.

For more reading: https://www.digitalocean.com/docs/databases/postgresql/how-to/secure/

Make a dump of your existing Database

SSH into the server where your current postgres database is kept and dump the database into a file.

pg_dump -Fc your_database > your_database.pgsql

Restore the database back to your new managed cluster. Note that your_public_network_connection_string can be found on the Digital Ocean Databases Overview section under Connection Details.

pg_restore -d 'your_public_network_connection_string' --jobs 4 your_database.pgsql

Check the Digital Ocean Logs & Queries to see that your tables were added.

Configure Your Rails App

Edit your database config sudo nano ~/your_app/shared/config/database.yml

  adapter: postgresql
  host: your_host
  port: your_port
  database: your_database_name
  username: your_database_username
  password: your_password
  encoding: unicode
  pool: 5
  ssl_require: true

Restart Your Rails App

The easiest way to do this is to simply redeploy using cap production deploy

Deploy Metabase

We'll be using Digital Oceans One-Click Deploy App for Metabase: https://cloud.digitalocean.com/marketplace/5d49c3d4bdfa551d14d195a7?i=ded2ac

The droplet is deployed using the .sh scripts here: https://github.com/tradestatistics/metabase-droplet

Visit your_ip_address:3000. For your first visit, you'll need to wait ~5 minutes for the metabase app to load. If it doesn't load try refreshing.

1) Create a User,
2) Add your data => Select I'll add my data later
3) Usage data preferences
4) Take me to Metabase

Click the Gear Icon > Admin > Settings > General located at your_ip_address:3000/admin/settings/general

Modify the site url to https://metabase.my-company.com

Install SSL

We'll be following this guide: https://www.digitalocean.com/community/tutorials/how-to-secure-nginx-with-let-s-encrypt-on-ubuntu-18-04

ssh root@your_ip_address
sudo add-apt-repository ppa:certbot/certbot
sudo apt install python-certbot-nginx
sudo nano /etc/nginx/sites-available/default
server {
    listen 80 default_server;
    listen [::]:80 default_server;

    # SSL configuration
    # listen 443 ssl default_server;
    # listen [::]:443 ssl default_server;
    # Note: You should disable gzip for SSL traffic.
    # See: https://bugs.debian.org/773332
    # Read up on ssl_ciphers to ensure a secure configuration.
    # See: https://bugs.debian.org/765782
    # Self signed certs generated by the ssl-cert package
    # Don't use them in a production server!
    # include snippets/snakeoil.conf;

    root /var/www/html;

    # Add index.php to the list if you are using PHP
    index index.html index.htm index.nginx-debian.html;

    server_name _;

    location / {
        # First attempt to serve request as file, then
        # as directory, then fall back to displaying a 404.
        try_files $uri $uri/ =404;

    # pass PHP scripts to FastCGI server
    #location ~ \.php$ {
    #   include snippets/fastcgi-php.conf;
    #   # With php-fpm (or other unix sockets):
    #   fastcgi_pass unix:/var/run/php/php7.0-fpm.sock;
    #   # With php-cgi (or other tcp sockets):
    #   fastcgi_pass;

    # deny access to .htaccess files, if Apache's document root
    # concurs with nginx's one
    #location ~ /\.ht {
    #   deny all;

# Virtual Host configuration for example.com
# You can move that to a different file under sites-available/ and symlink that
# to sites-enabled/ to enable it.
#server {
#   listen 80;
#   listen [::]:80;
#   server_name example.com;
#   root /var/www/example.com;
#   index index.html;
#   location / {
#       try_files $uri $uri/ =404;
#   }

Update the server name


server_name _;

Replace it with :

server_name metabase.your_app.com, www.metabase.your_app.com;

Restart nginx

sudo systemctl reload nginx

Obtain an SSL

sudo certbot --nginx -d metabase.your_app.com -d www.metabase.your_app.com


2: Redirect - Make all requests redirect to secure HTTPS access. Choose this for
new sites, or if you're confident your site works on HTTPS. You can undo this
change by editing your web server's configuration.
Let’s Encrypt’s certificates are only valid for ninety days. This is to encourage users to automate their certificate renewal process. The certbot package we installed takes care of this for us by adding a renew script to /etc/cron.d. This script runs twice a day and will automatically renew any certificate that’s within thirty days of expiration.

To test the renewal process, you can do a dry run with certbot:

sudo certbot renew --dry-run

Set A records in DNS.

For example, Namecheap settings:

A record    metabase            your_ip_address         1 min
A record    www.metabase        your_ip_address         1 min

Test DNS

Open browser and navigate to https://metabase.your_domain.com. You should see a welcome to Nginx page

Add Nginx Proxy to port 3000

The reason why you see an Nginx page instead of the metabase application is because Nginx is currently pointing you to an html page. You need to update your Nginx settings to point to the specific port.

Re-Open Nginx Settings

sudo nano /etc/nginx/sites-available/default

In your server blocks, comment out:

        #location / {
        #       # First attempt to serve request as file, then
        #       # as directory, then fall back to displaying a 404.
        #       try_files $uri $uri/ =404;

Replace with:

        location / {

Restart nginx

sudo systemctl reload nginx


Open browser and navigate to https://metabase.your_domain.com. You should see the metabase app

Past Blockers

Previously in v0.32.10 JS and CSS assets wouldn't load behind a subdomain or subdirectory. This has now been solved in v0.34.3

Unfortunately at this time a Metabase jar application behind a proxy such as nginx fails to load assets:

Connecting to Local Postgres from a Docker Container

Set the database host to host.docker.internal to forward the host the docker container is running on to your local host.