Overview

This guide covers how to back up a ClickHouse instance to Amazon S3 and restore the backup to another ClickHouse instance. This is useful for migrating data between LangSmith self-hosted environments or for disaster recovery purposes.

Prerequisites

Before you begin, make sure you have the following:

1. S3 bucket: An existing S3 bucket in your AWS account where the backup will be stored.

2. AWS credentials: One of the following:

• An IAM Access Key ID and Secret Access Key with permissions to the target S3 bucket.

• Temporary credentials (Access Key, Secret Key, and Session Token) from AWS STS.

• An IAM role attached to your EKS node group that grants S3 access (simplest for Kubernetes).

3. ClickHouse client access: Ability to run SQL queries against both the source and destination ClickHouse instances via kubectl exec.

Required S3 Permissions

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ClickHouseBackupS3",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:DeleteObject"
      ],
      "Resource": [
        "arn:aws:s3:::<YOUR_BUCKET_NAME>",
        "arn:aws:s3:::<YOUR_BUCKET_NAME>/*"
      ]
    }
  ]
}

Choosing an authentication method

ClickHouse's BACKUP/`RESTORE` SQL commands support two ways to authenticate with S3:

Option 1: Inline credentials (Access Key + Secret Key)

Pass the credentials directly in the SQL statement. This is the simplest approach if you have long-lived IAM access keys:

BACKUP ALL TO S3(
'https://<BUCKET>.s3.<REGION>.amazonaws.com/<PATH>',
'<ACCESS_KEY_ID>',
'<SECRET_ACCESS_KEY>'
);

Limitation: This syntax only accepts 1 argument (URL only) or 3 arguments (URL + key + secret). It does not accept a session token as a 4th argument.

Option 2: Environment variables (supports session tokens)

If you're using temporary STS credentials (which include a session token), pass them as environment variables to the clickhouse-client process. The AWS SDK inside ClickHouse will read them automatically:

kubectl exec <CLICKHOUSE_POD> -- env \
AWS_ACCESS_KEY_ID="<ACCESS_KEY_ID>" \
AWS_SECRET_ACCESS_KEY="<SECRET_ACCESS_KEY>" \
AWS_SESSION_TOKEN="<SESSION_TOKEN>" \
clickhouse-client --query "BACKUP ALL TO S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/<PATH>')"

Tip: Store your credentials in a Kubernetes secret and pull them at exec time:

1. Create the secret

kubectl create secret generic clickhouse-s3-backup \
--from-literal=ACCESS_KEY='<YOUR_ACCESS_KEY>' \
--from-literal=SECRET_KEY='<YOUR_SECRET_KEY>' \
--from-literal=SESSION_TOKEN='<YOUR_SESSION_TOKEN>'

2. Use it in the backup command

ACCESS_KEY=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.ACCESS_KEY}' | base64 -d)
SECRET_KEY=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.SECRET_KEY}' | base64 -d)
SESSION_TOKEN=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.SESSION_TOKEN}' | base64 -d)

kubectl exec <CLICKHOUSE_POD> -- env \
AWS_ACCESS_KEY_ID="$ACCESS_KEY" \
AWS_SECRET_ACCESS_KEY="$SECRET_KEY" \
AWS_SESSION_TOKEN="$SESSION_TOKEN" \
clickhouse-client --query "BACKUP ALL TO S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/<PATH>')"

Option 3: Node IAM role (no credentials needed)

If your EKS node role has the required S3 permissions (see Prerequisites above), ClickHouse will use the instance metadata credentials automatically. No access keys or env vars needed:

kubectl exec <CLICKHOUSE_POD> -- \
clickhouse-client --query "BACKUP ALL TO S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/<PATH>')"

This is the cleanest approach for production, but requires the IAM policy to be attached to the node role.

Backup procedure

Run the backup

Choose one of the authentication methods above and run the backup. These examples use the env var approach with a Kubernetes secret, but substitute whichever method works for your setup.

Backing up everything (all databases and tables):

ACCESS_KEY=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.ACCESS_KEY}' | base64 -d)
SECRET_KEY=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.SECRET_KEY}' | base64 -d)
SESSION_TOKEN=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.SESSION_TOKEN}' | base64 -d)

kubectl exec <SOURCE_CLICKHOUSE_POD> -- env \
AWS_ACCESS_KEY_ID="$ACCESS_KEY" \
AWS_SECRET_ACCESS_KEY="$SECRET_KEY" \
AWS_SESSION_TOKEN="$SESSION_TOKEN" \
clickhouse-client --query "BACKUP ALL TO S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/clickhouse-backup/<TIMESTAMP>')"

Or back up just the LangSmith database (smaller and faster):

kubectl exec <SOURCE_CLICKHOUSE_POD> -- env \
AWS_ACCESS_KEY_ID="$ACCESS_KEY" \
AWS_SECRET_ACCESS_KEY="$SECRET_KEY" \
AWS_SESSION_TOKEN="$SESSION_TOKEN" \
clickhouse-client --query "BACKUP DATABASE default TO S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/clickhouse-backup/<TIMESTAMP>')"

Tip: Always include a timestamp or unique identifier in the S3 path (e.g., clickhouse-backup/2026-04-02). ClickHouse will fail if a backup already exists at the same path.

Verify the backup

A successful backup returns output like:

┌─id─────────────┬─status───────────────────────────────┐
│ 3f5bb1c2-6c9b-4f24-993f-be4312a9f615 │ BACKUP_CREATED │
└───────────────────────────────────────────────────────┘

You can also query system.backups for details:

kubectl exec <SOURCE_CLICKHOUSE_POD> -- clickhouse-client --query "
SELECT id, status, num_files, formatReadableSize(total_size) as size,
start_time, end_time
FROM system.backups
ORDER BY start_time DESC
LIMIT 5
FORMAT PrettyCompact

And confirm the files exist in S3:

aws s3 ls s3://<BUCKET>/clickhouse-backup/<TIMESTAMP>/ --recursive --summarize

Restore procedure

Use RESTORE ALL with the EXCEPT clause to skip system databases. These contain ClickHouse internal metadata and will cause conflicts if the source and destination are running different ClickHouse versions or configurations.

ACCESS_KEY=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.ACCESS_KEY}' | base64 -d)
SECRET_KEY=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.SECRET_KEY}' | base64 -d)
SESSION_TOKEN=$(kubectl get secret clickhouse-s3-backup -o jsonpath='{.data.SESSION_TOKEN}' | base64 -d)

kubectl exec <DEST_CLICKHOUSE_POD> -n <NAMESPACE> -c <CONTAINER_NAME> -- env \
AWS_ACCESS_KEY_ID="$ACCESS_KEY" \
AWS_SECRET_ACCESS_KEY="$SECRET_KEY" \
AWS_SESSION_TOKEN="$SESSION_TOKEN" \
clickhouse-client --password <PASSWORD> --query "RESTORE ALL EXCEPT DATABASE system, information_schema, INFORMATION_SCHEMA
FROM S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/clickhouse-backup/<TIMESTAMP>')"

Notes on the command:

• `-n <NAMESPACE>`: Include this if the destination is in a different namespace

• -c <CONTAINER_NAME>: Required when the pod has multiple containers. For Altinity pods, use -c clickhouse-pod

• `--password <PASSWORD>`: Include if the destination ClickHouse has a password set.

Why exclude system databases? The system, information_schema, and INFORMATION_SCHEMA databases are internal to ClickHouse. Restoring them onto a different instance — especially one running a different ClickHouse version — can cause schema mismatches, failed migrations, or startup issues. Excluding them ensures only your application data is restored.

Troubleshooting

Handling conflicts on restore

If the destination instance already has tables with the same names, the restore will fail by default. Which approach to use depends on whether the source and destination use the same table engine types.

Scenario A: Same engine types (e.g., both non-replicated, or both replicated)

If the source and destination ClickHouse instances use the same table engines, you can either:

Drop and recreate (cleanest):

-- On the destination ClickHouse
DROP DATABASE IF EXISTS default;
CREATE DATABASE default;

Then run the RESTORE command.

Or allow overwriting:

RESTORE DATABASE default FROM S3('...')
SETTINGS allow_non_empty_tables = 1;

Scenario B: Different engine types (non-replicated backup into a replicated cluster)

This is the common case when migrating from a standalone LangSmith ClickHouse to a replicated cluster. A plain RESTORE will fail with CANNOT_RESTORE_TABLE because the backup contains MergeTree / ReplacingMergeTree tables but the destination has ReplicatedMergeTree / ReplicatedReplacingMergeTree.

The correct procedure is:

1. Let LangSmith migrations run first to create the tables with replicated engines on the new cluster. This happens automatically when you helm upgrade with the external ClickHouse config.

2. Then restore the data with both allow_non_empty_tables and allow_different_table_def:

kubectl exec <DEST_CLICKHOUSE_POD> -n <NAMESPACE> -c <CONTAINER_NAME> -- env \
AWS_ACCESS_KEY_ID="$ACCESS_KEY" \
AWS_SECRET_ACCESS_KEY="$SECRET_KEY" \
AWS_SESSION_TOKEN="$SESSION_TOKEN" \
clickhouse-client --password <PASSWORD> --query "RESTORE DATABASE default
FROM S3('https://<BUCKET>.s3.<REGION>.amazonaws.com/clickhouse-backup/<TIMESTAMP>')
SETTINGS allow_non_empty_tables = 1, allow_different_table_def = 1"

The allow_different_table_def = 1 setting tells ClickHouse to ignore engine type mismatches between the backup and the existing tables, restoring only the data. The data is inserted into the existing replicated tables and automatically replicates to all replicas.

Important: You must use RESTORE DATABASE default, not RESTORE ALL. The ALL variant will attempt to restore system databases which will cause additional conflicts.

Scenario B — Step by step summary

For clarity, here is the full sequence when migrating from a bundled (non-replicated) ClickHouse to a replicated cluster:

1. **Back up** the source ClickHouse to S3 (see Backup procedure above).

2. **Deploy** the Altinity ClickHouse cluster.

3. **Make sure ALL replicas have a clean, empty default database** before running migrations. This is critical — LangSmith migrations use ON CLUSTER DDL, which executes CREATE TABLE on **every replica simultaneously**. If even one replica has a leftover table from a prior restore or failed migration attempt, the entire migration will fail with TABLE_ALREADY_EXISTS.

# On EACH replica (repeat for every replica pod):
kubectl exec <REPLICA_POD> -n clickhouse -c clickhouse-pod -- \
clickhouse-client --password <PASSWORD> --query "DROP DATABASE IF EXISTS default SYNC"
kubectl exec <REPLICA_POD> -n clickhouse -c clickhouse-pod -- \
clickhouse-client --password <PASSWORD> --query "CREATE DATABASE default"

Also clean any stale ZooKeeper/Keeper metadata from prior attempts. Table definitions persist in Keeper even after DROP TABLE:

kubectl exec <KEEPER_POD> -n clickhouse -- \
clickhouse-keeper-client --host localhost --port 2181 \
-q "rmr '/clickhouse/tables/0/default'"

Note: Paths in clickhouse-keeper-client must be single-quoted inside the -q argument. The client's default port is 9181, but this Altinity deployment uses 2181 (ZooKeeper compatible port), so --port 2181 must be specified explicitly.

4. Configure LangSmith to point at the new external ClickHouse (set clickhouse.external.enabled: true in Helm values) and run helm upgrade. The ClickHouse migrations job will create all tables with Replicated* engines using ON CLUSTER DDL.

5. Restore the data with the command from Scenario B above. Data will replicate to all cluster members automatically.

6. Verify data is present and replicated (see Step 4 below).

Step 4: Verify the restore

After the restore completes, confirm data is present and replicated:

# Check data on replica 0
kubectl exec <REPLICA_0_POD> -n <NAMESPACE> -c <CONTAINER_NAME> -- \
clickhouse-client --password <PASSWORD> --query "
SELECT database,
formatReadableSize(sum(bytes_on_disk)) as disk_size,
sum(rows) as total_rows,
count() as parts
FROM system.parts
WHERE active AND database = 'default'
GROUP BY database
"

# Check data on replica 1 (should match replica 0)
kubectl exec <REPLICA_1_POD> -n <NAMESPACE> -c <CONTAINER_NAME> -- \
clickhouse-client --password <PASSWORD> --query "
SELECT database,
formatReadableSize(sum(bytes_on_disk)) as disk_size,
sum(rows) as total_rows
FROM system.parts
WHERE active AND database = 'default'
GROUP BY database
"

# Verify replication health (all tables should show active_replicas = total_replicas)
kubectl exec <REPLICA_0_POD> -n <NAMESPACE> -c <CONTAINER_NAME> -- \
clickhouse-client --password <PASSWORD> --query "
SELECT database, table, total_replicas, active_replicas
FROM system.replicas
WHERE active_replicas < total_replicas
FORMAT PrettyCompact
"

If the last query returns no rows, all tables are fully replicated.

Additional Troubleshooting

Common errors

• S3_ERROR: Access Denied - Missing S3 permissions: If using node role credentials (Option 3), attach the S3 policy to the EKS node role. Alternatively, switch to explicit credentials via environment variables.

• BACKUP_ALREADY_EXISTS - Backup exists at the same S3 path: Use a different S3 path (add a timestamp), or delete existing objects with aws s3 rm s3://<BUCKET>/<PATH>/ --recursive.

• NUMBER_OF_ARGUMENTS_DOESNT_MATCH - Session token passed as 4th arg to S3(): S3() only accepts 1 or 3 arguments. Pass session tokens as environment variables instead

• UNKNOWN_SETTING for s3_session_token - Session token passed via SETTINGS: There is no s3_session_token setting. Use the AWS_SESSION_TOKEN environment variable instead

• Backup hangs or is very slow - Large dataset or slow network to S3: Monitor with SELECT * FROM system.backups WHERE status = 'CREATING_BACKUP'. Consider running during low-traffic periods.

• Restore fails with table conflicts - Tables already exist on destination: Use DROP DATABASE before restoring, or add SETTINGS allow_non_empty_tables = 1.

• CANNOT_RESTORE_TABLE / "different definition" - Non-replicated backup into replicated cluster | Run LangSmith migrations first, then restore with SETTINGS allow_non_empty_tables = 1, allow_different_table_def = 1. See Scenario B above.

• Restore fails with schema errors - ClickHouse version mismatch: Ensure you are excluding system databases. If issues persist, align ClickHouse versions across environments.

TABLE_ALREADY_EXISTS during migrations

Cause: Stale table definitions on one or more replicas, or stale Keeper metadata.

LangSmith migrations use ON CLUSTER DDL, which runs CREATE TABLE on **every replica simultaneously**. If even one replica has a leftover table from a prior restore or failed migration attempt, the entire migration will fail.

Fix: Clean every replica and clear Keeper metadata:

# On EACH replica pod:
kubectl exec <REPLICA_POD> -n clickhouse -c clickhouse-pod -- \
clickhouse-client --password <PASSWORD> --query "DROP DATABASE IF EXISTS default SYNC"
kubectl exec <REPLICA_POD> -n clickhouse -c clickhouse-pod -- \
clickhouse-client --password <PASSWORD> --query "CREATE DATABASE default"

# Clear stale Keeper metadata:
kubectl exec <KEEPER_POD> -n clickhouse -- \
clickhouse-keeper-client --host localhost --port 2181 \
-q "rmr '/clickhouse/tables/0/default'"

Then re-run helm upgrade to trigger migrations again. See Scenario B steps above for the full sequence.

Dirty database version N during migrations

Cause: A prior migration run partially completed.

The schema_migrations table tracks which migrations have run. A "dirty" flag means a migration started but didn't finish cleanly.

Fix:

-- Mark the migration as clean (replace N with the version number from the error):
INSERT INTO schema_migrations (version, dirty, sequence)
VALUES (N, 0, toUInt64(toUnixTimestamp64Nano(now64())));

Then delete the failed migration job and re-run:

kubectl delete job langsmith-backend-ch-migrations
helm upgrade langsmith langsmith/langsmith -f <your-values-file>.yaml

column 'kv' not found in target table during migrations

Cause: Some LangSmith migrations create materialized views using arrayJoin(col) AS kv, which produces an intermediate column name that passes validation on single-node ClickHouse but fails on replicated clusters with ON CLUSTER DDL.

Fix: Manually create the view using ARRAY JOIN + tupleElement() syntax, then mark the migration as clean and re-run.

Example fix for runs_inputs_kv_mv:

CREATE MATERIALIZED VIEW runs_inputs_kv_mv ON CLUSTER 'default'
TO runs_inputs_kv AS
SELECT
...,
tupleElement(kv, 1) AS key,
tupleElement(kv, 2) AS value,
...
FROM runs
ARRAY JOIN inputs_kv AS kv
WHERE ...;

After creating the view manually, mark the migration clean (see the Dirty database version N fix above) and re-run helm upgrade.

Tips

• Use unique S3 paths per backup. Include a timestamp in the path (e.g., clickhouse-backup/2026-04-02_143000) to avoid collisions and make it easy to manage multiple backups.

• BACKUP DATABASE default vs BACKUP ALL: For LangSmith, BACKUP DATABASE default is sufficient and much faster since it skips system databases. BACKUP ALL includes system tables which inflate the backup size significantly

• Test restores in a non-production environment first to validate the process before relying on it for a real migration.

• Keep ClickHouse versions aligned between source and destination when possible. This minimizes compatibility issues during restore.

• Secure your credentials. Avoid pasting raw AWS keys into shared terminal sessions. Use Kubernetes secrets (as shown above) or environment variables. If using STS temporary credentials, be aware they expire (usually 1-12 hours).

• Retry settings: For production backups, add retry settings to handle transient S3 errors:

BACKUP DATABASE default TO S3('...')
SETTINGS backup_restore_s3_retry_attempts = 5,
backup_restore_s3_retry_max_backoff_ms = 2000;

• Migration order for replicated clusters: When migrating from a standalone ClickHouse to a replicated cluster, the correct order is: (1) backup, (2) deploy new cluster, (3) clean all replicas, (4) run migrations via helm upgrade, (5) restore data. Do **not** restore before migrations — the tables need to be created with replicated engines first.

• Materialized view issues: Some LangSmith ClickHouse migrations may fail on replicated clusters due to arrayJoin() syntax differences. See the column 'kv' not found entry in the Troubleshooting section above for the fix.