Site administration
Back up the analytics database
If the analytics server loses its data, the events are gone unless you have a backup. Set up a nightly backup of the database to R2, once on each new server.
3 min read
You need:
- To be signed in to the server as root. See Sign in to the server.
- Access to the Cloudflare dashboard of the deploy, to make an R2 token.
- The Source repository with wrangler signed in, on your computer, for step 4.
1. Make an R2 token
In the Cloudflare dashboard, create an R2 user API token:
- Permission: Object Read & Write.
- Bucket:
gitflare-analyticsonly. - Client IP address filter: the IPv4 address of the server.
Copy the access key id and the secret access key of the token.
2. Write the config files
On the server, write the two config files. Replace ACCOUNT_ID, ACCESS_KEY_ID and SECRET_ACCESS_KEY with your values:
cat > /etc/clickhouse-server/config.d/r2-backups.xml <<'EOF'
<clickhouse>
<s3>
<r2>
<endpoint>https://ACCOUNT_ID.r2.cloudflarestorage.com/gitflare-analytics/</endpoint>
<access_key_id>ACCESS_KEY_ID</access_key_id>
<secret_access_key>SECRET_ACCESS_KEY</secret_access_key>
</r2>
</s3>
</clickhouse>
chown root:clickhouse /etc/clickhouse-server/config.d/r2-backups.xml
chmod 640 /etc/clickhouse-server/config.d/r2-backups.xml
cat > /etc/clickhouse-server/config.d/dns-ipv4.xml <<'EOF'
<clickhouse>
<dns_allow_resolve_names_to_ipv6>false</dns_allow_resolve_names_to_ipv6>
</clickhouse>
systemctl restart clickhouse-server
3. Write the script and its cron file
On the server, replace ACCOUNT_ID here too:
cat > /usr/local/bin/clickhouse-backup-r2 <<'EOF'
#!/bin/sh
set -eu
day=$(date -u +%F)
clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "BACKUP DATABASE analytics TO S3('https://ACCOUNT_ID.r2.cloudflarestorage.com/gitflare-analytics/backups/$day/')"
chmod 700 /usr/local/bin/clickhouse-backup-r2
echo '17 3 * * * root /usr/local/bin/clickhouse-backup-r2 >> /var/log/clickhouse-backup.log 2>&1' > /etc/cron.d/clickhouse-backup
4. Add the lifecycle rule
If the bucket does not have the lifecycle rule yet, add it. Run this on your computer, from the root of the Source repository:
pnpm -C apps/web exec wrangler r2 bucket lifecycle add gitflare-analytics delete-old-backups backups/ --expire-days 8
5. Run the first backup
On the server, run the script once:
/usr/local/bin/clickhouse-backup-r2
6. See the last backup
On the server, run:
clickhouse-client --password "$(cat /root/.clickhouse-admin)" --query "SELECT name, status, error, end_time FROM system.backups ORDER BY start_time DESC LIMIT 3"
A good backup has the status BACKUP_CREATED. The table shows only the backups since ClickHouse last started. For older backups, read /var/log/clickhouse-backup.log.
Done
The newest row has the status BACKUP_CREATED, so your events have a copy that you can restore. Restore an analytics backup shows how.
The nightly backup
Every night at 03:17 UTC, the server saves the database analytics to the R2 bucket gitflare-analytics, under backups/<day>/. <day> is the UTC day, for example 2026-11-04.
/etc/cron.d/clickhouse-backupruns the script/usr/local/bin/clickhouse-backup-r2as root. The script writes to the log/var/log/clickhouse-backup.log.- The script runs one query:
BACKUP DATABASE analytics TO S3('https://<account id>.r2.cloudflarestorage.com/gitflare-analytics/backups/<day>/'). - ClickHouse signs in to R2 with an R2 token. The token has Object Read & Write on the bucket
gitflare-analyticsonly, and accepts only the IPv4 address of the server. - The S3 keys of the token are in
/etc/clickhouse-server/config.d/r2-backups.xml. ClickHouse uses them for each URL in the bucket, so no key is in a query. /etc/clickhouse-server/config.d/dns-ipv4.xmlmakes ClickHouse connect to R2 over IPv4 only. R2 also has IPv6 addresses, but the token does not accept the IPv6 address of the server.
The R2 lifecycle rule delete-old-backups deletes the files under backups/ after 8 days. It does not delete the files under dead/.