# 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 <span class="s3">BACKUP</span> 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 <span class="s2">BACKUP</span> command and saved to <span class="s2">/backups/backup\_2025\_06\_09</span>, 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 <span class="s1">docker-compose.yml</span>. For example:

```yaml
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 <span class="s2">BACKUP</span> command, ClickHouse also provides a built-in method to restore via the <span class="s2">RESTORE</span> 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 <span class="s1">default</span> 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 <span class="s1">BACKUP</span> command.

#### **Restore a Single Table**

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

This restores just the <span class="s1">events</span> table from the <span class="s1">default</span> 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 &amp; 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 ...
```

<span class="s1">**Cause:**</span> 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 <span class="s1">tar -tzf</span> 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
```

<span class="s1">**Cause:**</span> The container cannot access the <span class="s2">/backups/</span> directory due to permissions.

**Resolution:**

- Ensure the backup directory is readable by the ClickHouse process.
- Use <span class="s1">chmod -R 755 /opt/app/backups/</span> to adjust permissions if needed.

#### **3. Data Not Restored**

<span class="s1">**Cause:**</span> The RESTORE command did not include the correct database/table name or no data existed in the backup path.

**Resolution:**

- <span class="s1">Use </span>`clickhouse-client --query="SHOW DATABASES"`<span class="s1"> to confirm no restore happened.</span>
- Run <span class="s1">ls /backups/backup\_2025\_06\_09/</span> 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/
```