How should a bundled-ClickHouse package back up: raw /app/data snapshot vs logical dump via backupCommand?

[LoudLemur]

Context

Following on from the backup crash thread (now fixed, thanks @girish), there is a related but separate question on the packaging side where we would like some input.

Even with the readTree null guard, backing up a bundled ClickHouse by letting the platform rsync its live store under /app/data can capture an inconsistent set of parts. ClickHouse merges in the background throughout the snapshot, and its own guidance is to back up via ALTER TABLE ... FREEZE or clickhouse-backup rather than copying the live data directory, because a raw copy can catch a merge in flight. So the crash is solved, but backup correctness for ClickHouse-bundling packages (Langfuse, Plausible, PostHog, SigNoz) is still worth nailing down.

The mechanism that looks right

The newer manifest fields (box 9.1.0+) look purpose-built for this:

• persistentDirs keeps a directory out of the filesystem backup entirely.
• backupCommand runs during backup and is meant to write a portable logical dump into /app/data.
• restoreCommand runs before the app starts to repopulate from that dump.

The intended shape for a ClickHouse package would then be:

{
  "manifestVersion": 2,
  "minBoxVersion": "9.1.0",
  "addons": { "localstorage": {} },
  "persistentDirs": [ "/var/lib/clickhouse" ],
  "backupCommand": "/app/pkg/backup-clickhouse.sh",
  "restoreCommand": "/app/pkg/restore-clickhouse.sh"
}

The ClickHouse store lives in /var/lib/clickhouse (a persistentDir, so it is never walked by the filesystem backup and never racy), and backup-clickhouse.sh writes a consistent dump into /app/data, which is what actually gets backed up.

The open question

The documented backupCommand example is pg_dump, which works because PostgreSQL is a shared addon the command can reach over the network. A bundled ClickHouse is different: the docs say backupCommand runs via docker run in a temporary container from the app image, so as far as we can tell, it does not share a network with the running app, and the live ClickHouse server is presumably still running against /var/lib/clickhouse at that moment.

That leaves a few unknowns I have not been able to settle from the docs:

1. Is the app quiesced while backupCommand runs, or is the live container still up and writing?
2. Can the backupCommand container reach the running app (socket or network) to issue a consistent BACKUP or FREEZE? Or is the expectation that it operates purely on the mounted files?
3. If it operates on the files, what is the intended way to get a consistent export without two servers touching one data directory at once? A FREEZE-based hardlink snapshot into /app/data seems safest, but I would like to confirm the intended pattern before shipping it.

If anyone has a working backupCommand for a bundled database that is not a Cloudron addon, I would love to see the shape of it. We have a live ClickHouse app under merge load and a throwaway test instance, so are happy to prototype, write it up, and feed a working pattern back to the community packages.

[girish]

@loudlemur the backupCommand is run in it's own networking and pid namespace. But it does share the filesystem mounts (persistentDirs and /app/data) with the app.

Not a clickhouse expert but AI suggests that you can make clickhouse listen in a path like this (along with your any existing network bind):

    <native_socket_path>/var/run/clickhouse-server/clickhouse.sock</native_socket_path>
    <http_socket_path>/var/run/clickhouse-server/clickhouse-http.sock</http_socket_path>

You can put /var/run/clickhouse-server as another persistentDir and then the backupCommand will have access to it.

[girish]

I have fixed the backupCommand to share the networking namespace with the app - https://git.cloudron.io/platform/box/-/commit/90a8ba1a4915dfbbb30f1073c4f75cd99635ae3d . This is consistent with how the scheduler/cron commands also run. Thanks for reporting!