Deploy QuestDB on a VPS

sudo apt update && sudo apt upgrade -y
sudo apt install -y curl wget gnupg ca-certificates ufw nginx certbot \
  python3-certbot-nginx openjdk-17-jre-headless

# /etc/security/limits.d/questdb.conf
questdb soft nofile 1048576
questdb hard nofile 1048576
questdb soft memlock unlimited
questdb hard memlock unlimited

# /etc/sysctl.d/99-questdb.conf
vm.max_map_count=1048576
fs.file-max=1048576

sudo sysctl --system

sudo useradd --system --shell /usr/sbin/nologin --home-dir /var/lib/questdb --create-home questdb
sudo mkdir -p /opt/questdb /var/log/questdb
sudo chown -R questdb:questdb /var/lib/questdb /var/log/questdb /opt/questdb

cd /tmp
QDB_VERSION="8.2.3"
wget "https://github.com/questdb/questdb/releases/download/${QDB_VERSION}/questdb-${QDB_VERSION}-no-jre-bin.tar.gz"
tar -xzf "questdb-${QDB_VERSION}-no-jre-bin.tar.gz"
sudo mv "questdb-${QDB_VERSION}-no-jre-bin"/* /opt/questdb/
sudo chown -R questdb:questdb /opt/questdb

sudo -u questdb /opt/questdb/questdb.sh start -d /var/lib/questdb
sudo -u questdb /opt/questdb/questdb.sh stop

# HTTP REST + console
http.bind.to=127.0.0.1:9000
http.min.bind.to=127.0.0.1:9003

# Postgres wire
pg.enabled=true
pg.net.bind.to=0.0.0.0:8812
pg.user=admin
pg.password=<strong-password-here>

# InfluxDB Line Protocol (ILP) over TCP
line.tcp.net.bind.to=0.0.0.0:9009
line.tcp.auth.db.path=conf/auth.txt

# Threads
shared.worker.count=4
http.worker.count=2

telemetry.enabled=false

sudo -u questdb bash -c 'cd /var/lib/questdb/conf && \
  openssl ecparam -genkey -name prime256v1 -noout -out ilp-key.pem && \
  openssl ec -in ilp-key.pem -pubout -out ilp-pub.pem'

[Unit]
Description=QuestDB
After=network-online.target
Wants=network-online.target

[Service]
Type=simple
User=questdb
Group=questdb
Environment="JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64"
Environment="QDB_PACKAGE=tar"
ExecStart=/opt/questdb/questdb.sh start -d /var/lib/questdb -n
ExecStop=/opt/questdb/questdb.sh stop
Restart=on-failure
RestartSec=10
LimitNOFILE=1048576
LimitMEMLOCK=infinity

NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/var/lib/questdb /var/log/questdb

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl enable --now questdb
sudo systemctl status questdb

sudo ufw default deny incoming && sudo ufw default allow outgoing
sudo ufw allow OpenSSH
sudo ufw allow 80/tcp && sudo ufw allow 443/tcp

# Postgres wire from app servers only
sudo ufw allow from 203.0.113.10 to any port 8812 proto tcp
# ILP from collector hosts only
sudo ufw allow from 203.0.113.20 to any port 9009 proto tcp

sudo ufw enable

sudo apt install -y apache2-utils
sudo htpasswd -c /etc/nginx/.questdb-htpasswd admin

server {
  listen 80;
  server_name questdb.example.com;
  return 301 https://$host$request_uri;
}
server {
  listen 443 ssl http2;
  server_name questdb.example.com;
  auth_basic "QuestDB Console";
  auth_basic_user_file /etc/nginx/.questdb-htpasswd;
  client_max_body_size 512m;
  proxy_read_timeout 600s;

  location / {
    proxy_pass http://127.0.0.1:9000;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_buffering off;
  }
}

sudo ln -s /etc/nginx/sites-available/questdb /etc/nginx/sites-enabled/
sudo nginx -t && sudo systemctl reload nginx
sudo certbot --nginx -d questdb.example.com --redirect --agree-tos -m admin@example.com

#!/bin/bash
set -euo pipefail
TS=$(date +%Y%m%d-%H%M%S)
DEST="/var/backups/questdb/${TS}"
mkdir -p "${DEST}"

psql "host=127.0.0.1 port=8812 user=admin password=$PGPASSWORD dbname=qdb" \
  -c "SNAPSHOT PREPARE;"

rsync -a --delete /var/lib/questdb/db/ "${DEST}/db/"
cp -a /var/lib/questdb/conf "${DEST}/conf"

psql "host=127.0.0.1 port=8812 user=admin password=$PGPASSWORD dbname=qdb" \
  -c "SNAPSHOT COMPLETE;"

scrape_configs:
  - job_name: questdb
    static_configs:
      - targets: ['127.0.0.1:9003']