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 0.0.0.0/0 md5
host all all ::/0 md5
To allow Postgres Client for specified IPs use:
host all all 0.0.0.0/0 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 127.0.0.1 but also on the IPs you specified, in this case, 0.0.0.0:5432 shows that Postgres is accepting connections from all IPs.
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN -
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
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
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
Select the droplets which you want to communicate with this database.
For more reading: https://www.digitalocean.com/docs/databases/postgresql/how-to/secure/
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.
Edit your database config sudo nano ~/your_app/shared/config/database.yml
production:
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
The easiest way to do this is to simply redeploy using cap production deploy
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
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 127.0.0.1:9000;
#}
# 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
Find
server_name _;
Replace it with :
server_name metabase.your_app.com, www.metabase.your_app.com;
Restart nginx
sudo systemctl reload nginx
sudo certbot --nginx -d metabase.your_app.com -d www.metabase.your_app.com
admin@your_domain.com
Select
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
For example, Namecheap settings:
A record metabase your_ip_address 1 min
A record www.metabase your_ip_address 1 min
Open browser and navigate to https://metabase.your_domain.com. You should see a welcome to Nginx page
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 / {
proxy_pass http://127.0.0.1:3000;
}
Restart nginx
sudo systemctl reload nginx
Open browser and navigate to https://metabase.your_domain.com. You should see the metabase app
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:
https://github.com/metabase/metabase/issues/2206
https://github.com/tlrobinson/metabase-proxy-examples
https://discourse.metabase.com/t/dashboards-not-loading-when-using-https-nginx-proxy-solved/4161/2
https://discourse.metabase.com/t/nginx-metabase/554
Set the database host to host.docker.internal to forward the host the docker container is running on to your local host.