Skip to content

ClickHouse Setup

Install ClickHouse from the official apt repository (LTS channel) on Ubuntu, with UFW firewall restricting access to allowlisted IPs only.

Prerequisites

  • Ubuntu 24.04 (also works on 22.04)
  • /var on the large encrypted volume (data stored at /var/lib/clickhouse)
  • Known IP addresses for SSH admin access and ClickHouse client access

Variables

Replace these placeholders throughout:

Placeholder Description
ADMIN_IPS Office/VPN public IPs for SSH access
ALLOWED_IPS IPs that may access ClickHouse (ETL servers, tooling)
CH_PUBLIC_IP Public IP of this ClickHouse server

Ports used:

Port Protocol Purpose
22 TCP SSH
8123 TCP ClickHouse HTTP
9000 TCP ClickHouse native protocol

1. Prepare the Server

sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg ufw
sudo apt-get upgrade -y
sudo reboot

Reconnect after reboot. Optionally install NTP:

sudo apt-get install -y chrony
sudo systemctl enable --now chrony

2. Add the ClickHouse Apt Repository

# Install signing key
curl -fsSL 'https://packages.clickhouse.com/rpm/lts/repodata/repomd.xml.key' | \
  sudo gpg --dearmor -o /usr/share/keyrings/clickhouse-keyring.gpg

# Add repo
ARCH=$(dpkg --print-architecture)
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg arch=${ARCH}] \
  https://packages.clickhouse.com/deb lts main" | \
  sudo tee /etc/apt/sources.list.d/clickhouse.list

sudo apt-get update

3. Install ClickHouse

sudo apt-get install -y clickhouse-server clickhouse-client
sudo systemctl enable --now clickhouse-server
sudo systemctl status clickhouse-server --no-pager

4. Confirm Data Directory

sudo ls -la /var/lib/clickhouse | head

Should show directories owned by the clickhouse user.

5. Enable Remote Access

Edit /etc/clickhouse-server/config.xml and set:

<listen_host>0.0.0.0</listen_host>

Restart and verify:

sudo systemctl restart clickhouse-server
curl -s "http://127.0.0.1:8123/?query=SELECT%201"
clickhouse-client -q "SELECT version()"

6. Configure UFW Firewall

Default policies

sudo ufw default deny incoming
sudo ufw default allow outgoing

Allow SSH from admin IPs only

sudo ufw allow from ADMIN_IP_1 to any port 22 proto tcp
sudo ufw allow from ADMIN_IP_2 to any port 22 proto tcp

Allow ClickHouse from allowed IPs only

sudo ufw allow from ALLOWED_IP_1 to any port 8123 proto tcp
sudo ufw allow from ALLOWED_IP_1 to any port 9000 proto tcp

sudo ufw allow from ALLOWED_IP_2 to any port 8123 proto tcp
sudo ufw allow from ALLOWED_IP_2 to any port 9000 proto tcp

Enable and verify

sudo ufw enable
sudo ufw status numbered

7. Validate Remote Access

From an allowed IP:

clickhouse-client --host CH_PUBLIC_IP --ask-password -q "SELECT 1"

From a non-allowed IP, the connection should be refused.

Operational Notes

Upgrades

You are on the ClickHouse LTS channel. Do not upgrade casually. Review release notes and upgrade during a planned maintenance window.

Monitoring

At minimum, monitor:

  • Disk usage on /var/lib/clickhouse
  • CPU iowait
  • ClickHouse service status
  • Error log growth (/var/log/clickhouse-server/)

Troubleshooting

ClickHouse not reachable remotely:

  • Check UFW rules: sudo ufw status numbered
  • Confirm ClickHouse is listening: sudo ss -lntp | grep -E '8123|9000'
  • Confirm listen_host is set and service was restarted

Local queries work but remote fails:

  • Almost always a firewall allowlist mismatch (wrong source IP or missing rule)