Skip to main content

Restoring a Backup

Restoring ClickHouse backups is essential for disaster recovery, staging environment duplication, or rolling back to a known state. Elestio supports backup restoration both through its web dashboard and manually through Docker Compose and command-line methods. This guide explains how to restore ClickHouse backups from SQL-based snapshots or file-based archives, covering both full and partial restore scenarios, and includes solutions for common restoration issues.

Restoring from a Backup via Terminal

This method applies when you have a backup created using ClickHouse’s native BACKUP command or a direct copy of the data directory. To restore the backup, you must stop the running ClickHouse container, replace the data files, and restart the container to load the restored dataset.

Stop the ClickHouse Container

Shut down the ClickHouse container cleanly to avoid issues with open file handles or inconsistent state:

docker-compose down

Replace the Backup Files

If your backup was created using the native ClickHouse BACKUP command and saved to /backups/backup_2025_06_09, copy it into the appropriate path within the container or bind mount.

Example:

cp -r ./clickhouse_backup_2025_06_09 /opt/app/backups/backup_2025_06_09

Make sure this path corresponds to the volumes specified in your docker-compose.yml. For example:

volumes:
  - ./backups:/backups
  - ./data:/var/lib/clickhouse

If you’re restoring from a tarball archive, extract it into the correct volume mount:

tar -xzf clickhouse_backup_2025_06_09.tar.gz -C /opt/app/data/

Restart ClickHouse

Start the ClickHouse container again:

docker-compose up -d

ClickHouse will load the data either from the standard data directory or, if using the backup snapshot method, you can explicitly restore the database using SQL (next section).

Restoring via Docker Compose Terminal

If you’re using backups made with the SQL BACKUP command, ClickHouse also provides a built-in method to restore via the RESTORE command.

Copy the Backup Directory into the Container

docker cp ./clickhouse_backup_2025_06_09 $(docker-compose ps -q clickhouse):/backups/backup_2025_06_09

Restore with ClickHouse SQL

Enter the container terminal:

docker-compose exec clickhouse bash

Then run the restore command:

clickhouse-client --query="RESTORE DATABASE default FROM Disk('/backups/backup_2025_06_09')"

This will restore the default database and its contents from the previously created backup directory.

Partial Restores in ClickHouse

ClickHouse supports more granular restore operations using SQL syntax. You can restore individual tables or databases if the backup was created using the native BACKUP command.

Restore a Single Table

clickhouse-client --query="RESTORE TABLE default.events FROM Disk('/backups/backup_2025_06_09')"

This restores just the events table from the default database without affecting other tables.

Restore Specific Schemas or Data

You can also export and import CSV or TSV snapshots for partial data management:

clickhouse-client --query="SELECT * FROM default.events FORMAT CSV" > events.csv
clickhouse-client --query="INSERT INTO default.events FORMAT CSV" < events.csv

Common Errors & How to Fix Them

Restoring ClickHouse data can occasionally fail due to permission issues, path mismatches, unsupported formats, or version conflicts. Here are some frequent issues and their solutions.

1. ClickHouse Fails to Start After Restore

Error:

DB::Exception: Corrupted data part ...

Cause: The backup directory is incomplete or corrupted, or the file was not extracted properly.

Resolution:

  • Re-verify that the backup files were copied completely.

  • Use tar -tzf to inspect archive contents before extracting.

  • Make sure you’re restoring on the same ClickHouse version that created the backup.

2. RESTORE Command Fails with Permission Denied

Error:

DB::Exception: Cannot read from backup: Permission denied

Cause: The container cannot access the /backups/ directory due to permissions.

Resolution:

  • Ensure the backup directory is readable by the ClickHouse process.

  • Use chmod -R 755 /opt/app/backups/ to adjust permissions if needed.

3. Data Not Restored

Cause: The RESTORE command did not include the correct database/table name or no data existed in the backup path.

Resolution:

  • Use clickhouse-client --query="SHOW DATABASES" to confirm no restore happened.

  • Run ls /backups/backup_2025_06_09/ inside the container to verify backup contents.

4. Permission Denied When Copying Files

Error:

cp: cannot create regular file ‘/opt/app/data/’: Permission denied

Resolution:

Ensure your terminal session or script has write access to the target directory. Use sudo if needed:

sudo cp -r ./clickhouse_backup_2025_06_09 /opt/app/data/

No comments to display

Back to top