Backing up ClickHouse to S3

Backing up ClickHouse to S3 with BACKUP and RESTORE: when teams need it

A self-managed ClickHouse cluster with replicated tables needs a separate backup if you want protection against deletes, bad migrations or corruption. ClickHouse replication keeps copies of data on healthy replicas, so when a node fails, a recovered replica syncs missing data from its peers. But replication also copies mistakes: a DROP TABLE, a faulty ALTER, or a corrupted part is applied to every replica. The ClickHouse backup documentation makes this point directly, noting that replication alone does not protect against human error or corruption. ClickHouse backup and restore documentation covers the mechanism in detail.

The BACKUP and RESTORE statements write tables, partitions or whole databases to local disk, S3-compatible storage or Azure Blob Storage. They support full backups and incremental backups, so a team can take a complete copy periodically and smaller increments in between. For S3 targets, the decision you face is whether the backup destination sits outside the failure domain of the cluster itself. A bucket in the same rack, same storage system or same account as the database offers little protection against site loss or a compromised administrator. Confirm with your storage team where the bucket lives, who can delete objects in it, and whether versioning or object lock is available on your platform.

Teams usually need this when the recovery objective cannot be met by replicas alone, when they must show auditors a restore path, or when a retention rule requires data to exist beyond what the cluster keeps. Before you commit, run a restore test on a spare cluster. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed. Ask your team to name the owner of backup jobs, the expected recovery point, and the steps for restoring a single table versus a full database. Those answers determine whether the S3 approach fits, or whether the work belongs with a partner to design and rehearse.

ClickHouse BACKUP options for S3 targets and when each fits

This table compares the decisions a team makes when writing ClickHouse backups to S3 or S3-compatible storage: scope, full versus incremental runs, execution mode, credentials and password protection. Read each row for when the option fits and what it adds to day-to-day operating effort, then confirm exact syntax and version support in the backup documentation for your release.

Decision	When it fits	Operating effort and trade-offs
Scope: whole database	Disaster recovery where every table in a database must be restorable as a unit, and the cluster has room for the full copy.	Largest backup and longest run. Restores cover everything, so a single bad table may still require a larger restore than the incident needs.
Scope: single table	Tables with distinct recovery needs, such as a compliance-critical table restored more often than the rest.	More backup jobs to schedule and monitor. Restore ordering across related tables must be planned by hand.
Scope: partition	Time-partitioned event data where older partitions rarely change and recent ones carry most of the risk.	Requires a consistent partition naming and retention scheme. Partial restores need checks that dependent partitions are present.
Full backups	The baseline for any schedule, and the safest choice for a first backup to a new bucket.	Each run copies all selected data, so storage use and transfer grow with dataset size. Keep at least one full backup you have test-restored.
Incremental backups	Large datasets where daily copies would be too heavy and you can keep a periodic full backup as a restore anchor.	Restores depend on the chain of earlier backups. A missing or damaged base in S3 can make later incrementals unusable, so retention and lifecycle rules must protect the whole chain.
Synchronous execution	Small databases, manual one-off backups, or runs where the operator needs the result before the next step.	The client session waits for the backup to finish. Long runs tie up connections and can time out in automation.
Asynchronous execution	Scheduled production backups where the job is started, then checked later.	Someone or something must poll system.backups for status and failure reasons. Alerting on failed or stalled backups becomes part of the runbook.
Credentials in named collections	Teams that want S3 keys kept out of query text, shell history and job definitions, with access limited to administrators.	Named collections must be created and rotated through a controlled process. Confirm how the release handles their access grants before relying on them.
Password-protected backups	Backups copied to storage shared with other teams, or where an assessor expects encryption of backup contents in addition to bucket controls.	Passwords become another secret to store, rotate and recover. A lost password can make the backup unreadable, so escrow must be tested.

S3, MinIO or Ceph: choosing the backup target

Amazon S3 is a managed object store, so the durability, replication and availability of the storage itself are run by AWS rather than by your team. For many organizations this is the simplest target to justify because the team already operates AWS accounts, IAM policies, encryption keys and audit logging for other workloads. Backups written there inherit those controls, which can be reused rather than rebuilt.

Managed durability: Your team does not patch, scale or replace storage hardware, but it still owns bucket configuration and retention settings.
Existing access controls: IAM roles, bucket policies and service control policies can restrict who writes, reads and deletes backup objects.
Object protection: Confirm whether versioning, object lock and lifecycle rules are enabled for the backup bucket, since they protect against accidental or malicious deletion.
Network path: Decide whether ClickHouse reaches the bucket over the public endpoint or a private endpoint, and confirm that path meets your network policy.
Cost model: Storage, request and data transfer charges recur and scale with backup size and frequency, so ask for an estimate based on your retention plan.

Confirm with your AWS account owners which controls apply to the backup bucket before the first backup runs.

Setting up a ClickHouse backup to S3 storage

This sequence takes a self-managed ClickHouse cluster from an unverified setup to a first small backup in S3 or S3-compatible storage that has been confirmed in the bucket and in system.backups. Each step ends with a check so the team knows whether to continue.

Confirm the ClickHouse version is supported
The platform engineer checks the version running on every node against the supported list in the ClickHouse security policy before any backup work starts. The policy lists the versions that currently receive security updates, and all other versions are unsupported. Run the version query on each node, not only on the first one, because mixed versions in a cluster can behave differently. Done means every node reports a version on the supported list, or the team has recorded an upgrade step before continuing. Confirm the current list with the security policy, since it changes over time.
Create a dedicated bucket and storage identity
The storage administrator creates a bucket used only for ClickHouse backups, with a name that identifies the cluster and environment. Next, create a storage identity that can write, list and read objects in that bucket and nothing else, with no administrative rights and no access to other buckets. Confirm the exact permission set your storage platform needs for backup writes and reads in the ClickHouse backup documentation and in your provider's documentation. Done means a test upload and download succeed with the new identity, and the same identity is denied access to a different bucket.
Store the credentials in a named collection
The database administrator defines a named collection that holds the bucket endpoint and the access key of the storage identity, so the secret never appears in query text, shell history or query logs. Restrict who can read or alter the collection through ClickHouse access control, and remove or restrict any default user privileges that would let other accounts reach it. Use the syntax in the backup documentation for named collections and S3 storage, as it varies with version. Done means the collection exists, a non-administrative account can use it to reference the bucket, and the secret does not appear in the ClickHouse query log for the test.
Run a test backup of one table
The database administrator runs a BACKUP statement for a single small table, pointing at the S3 destination through the named collection. Choose a table that can be restored quickly and whose contents the team can count, so the restore test later has a clear comparison. Run the backup asynchronously if the statement is large, and note the backup name returned by the server. Done means the statement returns a backup identifier without error, or the error is recorded and resolved before the next step. Keep the test scoped to one table; full database backups come after the restore test passes.
Check the backup in system.backups
The database administrator queries the system.backups table for the backup identifier from the previous step and reads its status, error field and size or file counts. A status showing that the backup finished is the expected result, while a failed or still-running status means the backup has not yet been proven. If the backup was asynchronous, keep querying until the status changes from running to a final state. Done means the status is complete and the error field is empty. Record the identifier and status for the evidence file.
Confirm objects are present in the bucket
The storage administrator lists the objects under the backup prefix in the bucket, using the storage platform's own tooling rather than ClickHouse. Check that the files appear under the expected path, that the object count and sizes are plausible for the test table, and that the identity used by ClickHouse can see them. This check confirms the data left the cluster and landed where the team expects, independently of the server's own status. Done means the listing shows the backup objects, and the team has noted the bucket path for the restore test. A successful listing does not prove the backup is restorable, so the restore test in the backup documentation remains required.

Proving the backup restores: restore tests and progress tracking

A backup that has never been restored is an assumption, not a recovery plan. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when you need it, and it recommends regular restore tests on spare clusters. Treat the first restore of a new BACKUP ... TO S3(...) target as a required acceptance step, and schedule the same test on a recurring basis after that. Restore into a separate cluster or a scratch database, never over live tables, so the test cannot damage the data it is meant to protect. The test should run with the same credentials, endpoint and bucket path that production restores would use, because a restore that works only from an operator's admin session proves less than one run with the restore role your team would actually hold.

Track every run through the system.backups table, which records the state of asynchronous BACKUP and RESTORE operations and is the first place to look while a long job is running. For each run, keep a record with the backup name, the operation type, the start and end times, the final status, the size of the backup and its destination, and any error text. Keep the record outside the cluster, in a ticket, runbook or log store, so it survives the loss of the server it describes. Compare the recorded restore duration with the recovery time objective your organization has agreed for that dataset. Restore duration is often the number that decides whether the objective can be met, because download from object storage, rebuilding parts and replicating data to other nodes all take time that a backup job never shows. Measure it in your own environment with a dataset of realistic size, and confirm the figures with your storage team, since network throughput to the bucket, its region and its load determine the result.

Securing S3 backups: credentials, passwords, network paths and access

A ClickHouse backup in object storage is only as protected as the keys, passwords and bucket settings around it. The cards below cover the controls that decide whether a copy of your data can be read, altered or deleted by someone who should not have access to it.

Least-privilege storage keys

Give ClickHouse a storage identity that can only write and read its own backup prefix, with no permission to list or delete other buckets. Decide who rotates the access key, how often, and how the rotation reaches the ClickHouse named collection without a server restart outage. Ask your storage team to confirm that deletion rights are held by a separate, rarely used identity.

Keeping credentials out of logs

Access keys written inline in a BACKUP statement can end up in query logs, shell history, CI output and shared scripts. Store them in a named collection defined by an administrator, and confirm that the query log and any tooling around the server do not echo the statement text. Review who can read the server configuration directory on each host.

Backup password protection

A password on a backup encrypts its contents, but the password is only useful if it is recovered when needed. Record where the password lives, who can retrieve it under which approval, and how it is rotated for future backups without losing the ability to restore older ones. Test a restore using only the documented recovery path, not a copy held on the original server.

Bucket versioning and object lock

Versioning keeps prior object versions when a backup is overwritten or deleted, and object lock can block deletion for a fixed retention period if the storage supports it. Confirm with your storage platform which of these features exist in your edition and version, and whether they apply to the backup prefix. Without them, a compromised credential can remove the only copy.

Encryption in transit to the endpoint

Backup traffic carries full table contents, so the connection to the S3 endpoint should use TLS with certificate validation enabled. For MinIO or Ceph RGW, confirm the endpoint certificate is issued by a CA the ClickHouse hosts trust, and that plain HTTP is not reachable from the backup network. Restrict the network path so backup traffic reaches only the storage endpoint.

Restore access and cluster permissions

A table backup does not automatically carry SQL-managed users, roles, row policies or quotas unless they are included in the backup scope, so restored data may be readable by identities that should not see it. Give the restore operator a separate, controlled identity with time-limited access, and verify access rules after each restore. Plan this together with the users and roles backup topic.

What teams ask before running ClickHouse backups to S3

What does a ClickHouse backup to S3 cost, and what drives it?

The cost comes from four places: object storage capacity, data transfer and request charges from your storage provider, the staff time to build and test the job, and any spare cluster you use for restore tests. Storage grows with the size of your data, how many full and incremental copies you keep, and how long you retain them. Ask any partner for a quote that separates one-time setup (configuration, credentials, first test restore, runbook) from recurring items (storage, scheduled backup runs, periodic restore tests, support). Ask which storage platform is in scope, since a self-hosted MinIO or Ceph cluster has hardware and operations costs that a managed bucket does not. No price is given here because the supplied facts do not include one.

How long does a first ClickHouse S3 backup setup take?

The time depends on how many clusters and tables are in scope, whether the bucket and identity already exist, how the storage target is provisioned, and how many restore tests are required before go-live. A single cluster with an existing bucket is far simpler than a multi-shard cluster that needs a new object store and a documented restore procedure. Ask for the duration as part of a written scope, with the milestones listed: access and credentials, first backup, first restore to a spare cluster, scheduled jobs and handover. The supplied facts do not give a duration, so treat any number you hear as something to verify against your own environment.

What does our team need to provide before the work starts?

Plan to provide the following before work begins:

Cluster access, including a maintenance account with the rights needed to run BACKUP and RESTORE and to read system tables
A target bucket or S3-compatible endpoint, with a naming and retention decision
An identity for the backup job, such as an access key scoped to that bucket
A test environment or spare cluster where restores can be run without touching production
Named decision owners for retention, encryption keys, restore acceptance and who is paged when a backup fails

Confirm these with your security team early, because credential handling and bucket policy often need approval.

Can our internal team set up ClickHouse S3 backups without a partner?

Yes, an experienced internal team can build this, because the work uses the built-in BACKUP and RESTORE statements and standard object storage. The risk is rarely the first backup; it is the untested restore, the unmonitored job and the credential that expires. Check whether your team has ClickHouse operations experience, someone who owns the storage platform, and time to run restore tests on a spare cluster. A partner is most useful for a fixed-scope build with written deliverables, an independent restore test and a runbook your staff can follow. Your internal team may still own operations after handover.

Does a backup stored in the same site protect us from site loss?

No. A backup in the same data center, cloud region or storage cluster protects against accidental deletion and some corruption, but it does not protect against losing the site, a region outage or a compromise that reaches the storage platform. Replication also does not replace backups, because the ClickHouse backup documentation notes that replication alone does not guard against human error or corruption. For site loss, ask for a second storage location in a separate failure domain, with its own access controls, and test restores from that location. Your recovery objectives should decide how far apart those copies must be.

How do we know the backup will restore when we need it?

You know only after restoring it. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly, and recommends regular restore tests on spare clusters. Ask for a restore test as a named deliverable, with the restore steps written down, row counts checked against the source and results recorded. Ask how often restore tests will repeat after handover, because the answer affects the recurring cost and the staff time your team carries.

How should we protect backup credentials and passwords?

Keep S3 credentials out of plain configuration files where possible, and store them as named collections so the access key is not repeated across queries. Use a backup password where the backup platform supports it, and store that password in your secrets manager, not in a runbook. Scope the bucket identity to write and read only the backup prefix, and rotate keys on a schedule. Confirm with your security team how key rotation will be done without breaking scheduled backups, and who holds the decryption passphrase if one is used.

Limits to confirm in your environment before you rely on S3 backups

An S3 backup is only as good as the assumptions behind it. The cards below list the points to confirm for your release, your object store and your network, and each should be tested on a spare cluster before a backup schedule is treated as protection.

Exact syntax for your release

Confirm the BACKUP and RESTORE syntax and settings for the ClickHouse release you run, not the one in an older blog post. Settings for S3 destinations, asynchronous execution and restore behavior can change between releases. Check the release notes for your version and run one full backup and one restore on a spare cluster using the exact statements your runbook will contain.

Object store support and licensing

Confirm that your S3-compatible product supports the API calls ClickHouse uses for multipart writes, listing and deletion, and that its license permits your intended use. MinIO's community server is distributed under AGPLv3, and its repositories are archived, so support and patch expectations differ from a commercially supported edition. Ceph RGW is a different project with a different license and release cadence. Record the version you tested.

Incremental chain dependencies

Confirm how an incremental backup depends on its base. A restore from an increment generally needs every backup earlier in the chain, so losing or deleting one base can make later increments unusable. Test a restore from the last increment after deleting an older object in a test bucket, and confirm which retention rules your lifecycle policy applies to chain members.

Backups of replicated tables during writes

Confirm what a backup of a ReplicatedMergeTree table captures when inserts and merges run during the backup. Ask whether the snapshot is consistent per table, per partition or per part, and how long the backup holds on to the data it reads. Measure how ingestion latency and disk usage change during a backup at your real write rate, not at a quiet test rate.

Restore size and duration in your network

Measure how long a full restore takes between your ClickHouse nodes and your object store, across the network path your production cluster will use. Restore time depends on data volume, part count, bandwidth between sites, object store throughput and the number of parallel readers. Use the measured figure, along with your recovery time objective, to decide whether restores need a dedicated network path or a second copy in a closer location.

Files outside the database

Confirm whether your backup covers users, roles, row policies, quotas and settings profiles stored in SQL, and whether it covers the config.d and users.d files on each server. Backups of database objects often do not include server configuration on disk, so a restore to a new host can lose authentication settings, TLS files or cluster definitions. Keep those files in version control and restore them in the same test.

How DeployOpen scopes, builds and supports ClickHouse backups to S3

DeployOpen plans, implements, tests and documents ClickHouse BACKUP and RESTORE jobs to S3 or S3-compatible storage inside infrastructure you control. Each engagement starts from a written scope, ends with a restore test you can rerun, and can continue under a retainer.

How we scope

Inventory which databases, tables and partitions the S3 backups must cover, and which ones they can leave out.
Agree the target bucket, endpoint, region and storage owner for S3, MinIO or Ceph RGW in writing.
Record the recovery point and recovery time the business expects, and the restore test cadence needed to prove them.

How we build

Create a dedicated storage user with bucket-scoped permissions and hold its keys in named collections, not in queries.
Configure password-protected backups and asynchronous BACKUP jobs, then check progress through system.backups.
Schedule full and incremental runs, with alerts on failed or stalled jobs and retention rules set for the bucket.

How we hand over

Run a documented restore onto a spare ClickHouse cluster and record row counts and checksums as evidence.
Deliver runbooks covering backup commands, restore order, credential rotation and how to read system.backups status.
Walk your operators through a live restore so the team can repeat it without DeployOpen present.

How we support

Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts.
Ongoing retainers can cover scheduled restore tests, ClickHouse version upgrades and backup job reviews.
Ask any proposal to confirm which S3 endpoint, versions and retention periods your environment actually uses.

Backing up ClickHouse to S3 with BACKUP and RESTORE