Workflow [PR], commit [0eb298b]

Duplicate? #64222

I thought that backups implementation is very similar to disk implementation internally in ClickHouse. And it can work similar to web disks. Except that even if you adapt backup engine to disk interface, you cannot just use this disk in MergeTree table storage policy, because underlying data in backup is stored in format similar to DatabaseOrdinary and you cannot attach such a table to an DatabaseAtomic that expects that table data path will be store/first_uuid_characters/uuid.

So then I decided that it makes sense to implement a new Backup database engine. That way we actually solve a lot of problems:

1. You can have backup with any amount of databases and tables. You just need to specify a concrete database name in the Backup database engine.
2. Table backup looks exactly the same as a database backup. We store the database in backup from which we created the table. So that way the interface is very generic and will work with databases and tables.
3. You do not need to specify table types, because all metadata for all tables is already stored in backup.
4. During database load it is possible to properly bootstrap tables, when we read table metadata, so we can change engines in ReplicatedMergeTree tables, change storage policy, change views/materialized views queries.

Although, we still have the problem that the database relative path will be completely different from the backup database relative path. So we need to also have a path translation layer somewhere inside disk implementation.

So current design has following components:

1. BackupDisk - adapts backup interface to disk interface and performs path translation.
2. DatabaseBackup - uses backup to load tables metadata, changes tables metadata to properly bootstrap tables, creates storage policy with backup disk and sets this storage policy to bootstrap tables.

When the MergeTree table inside the database Backup tries to read some file, it uses the relative path of the Backup database that gets translated inside the DiskBackup to a proper path that can be used to query files/directories in backup.

DatabaseBackup provides a very familiar interface to people who work with backups.

You have backup query:

BACKUP DATABASE/TABLE name TO BACKUP_ENGINE

Backup engine can be anything like S3, Azure, File, Disk. With Disk backup engine it looks like this:

BACKUP DATABASE/TABLE name TO Disk(‘disk_name’, ‘backup_name’)

To create database Backup you can just use such query:

CREATE DATABASE backup ENGINE = Backup(‘database_name_inside_backup’, BACKUP_ENGINE)

For example with Disk backup engine query will be like this:

CREATE DATABASE backup ENGINE = Backup(‘database_name’, Disk(‘disk_name’, ‘backup_name’))

The goal was for the client to specify exactly the same engine that he used during backup creation. For all backup engines database Backup will work.

Duplicate? #64222

I discussed with @alexey-milovidov and this pull request looks abandoned. Last commit May 22, 2024 .

Plus only Backup disk implementation is not useful at all, because you need to have implementation of full Backup attach mechanism.

In this pull request I implemented it on database layer, so it can work with any table/database from backup. I wrote more motivation above.

Database Backup works with both incremental and non-incremental backups.

Let’s make an example with a Disk engine because it looks more like production configuration. Let’s first setup backups disk in storage.xml:

<storage_configuration>
	<disks>
		<backups>
			<type>local</type>
			<path>/home/ubuntu/ClickHouseWorkDir/backups/</path>
		</backups>
	</disks>
</storage_configuration>
<backups>
	<allowed_disk>backups</allowed_disk>
	<allowed_path>/home/ubuntu/ClickHouseWorkDir/backups/</allowed_path>
</backups>

Example of usage. Let’s create test database, tables, insert some data and then create a backup:

CREATE DATABASE test_database;

CREATE TABLE test_database.test_table_1 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_1 VALUES (0, 'test_database.test_table_1');

CREATE TABLE test_database.test_table_2 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_2 VALUES (0, 'test_database.test_table_2');

CREATE TABLE test_database.test_table_3 (id UInt64, value String) ENGINE=MergeTree ORDER BY id;
INSERT INTO test_database.test_table_3 VALUES (0, 'test_database.test_table_3');

BACKUP DATABASE test_database TO Disk('backups', 'test_database_backup');

So now we have test_database_backup backup and let’s create database Backup:

CREATE DATABASE test_database_backup ENGINE = Backup('test_database', Disk('backups', 'test_database_backup'));

Now we can query any table from database:

SELECT id, value FROM test_database.test_table_1;

┌─id─┬─value──────────────────────┐
│  0 │ test_database.test_table_1 │
└────┴────────────────────────────┘

SELECT id, value FROM test_database.test_table_2;

┌─id─┬─value──────────────────────┐
│  0 │ test_database.test_table_2 │
└────┴────────────────────────────┘

SELECT id, value FROM test_database.test_table_3;

┌─id─┬─value──────────────────────┐
│  0 │ test_database.test_table_3 │
└────┴────────────────────────────┘

It is also possible to work with this database as with any ordinary database. For example query tables in it:
SELECT database, name FROM system.tables WHERE database = 'test_database_backup':

┌─database─────────────┬─name─────────┐
│ test_database_backup │ test_table_1 │
│ test_database_backup │ test_table_2 │
│ test_database_backup │ test_table_3 │
└──────────────────────┴──────────────┘

Please add some documentation for this feature in docs/en/engines/database-engines/.

I added documentation.

Integration tests

test_storage_dict/test.py::test_storage_dict
test_merge_tree_s3/test.py::test_s3_disk_reads_on_unstable_connection[node]
-- flaky in master

Stress test (debug)

#76230

Could you please merge this branch with master one more time - there was a change in style check (#76082) and now style check fails in the private synchronization PR of this PR, because checks were started there on a branch including the mentioned change in the style check, but this PR's checks were run without it, but it will fail in this PR as well once you merge with master branch.
The concrete failure reason is this

Integration tests (asan, old analyzer, 2/6)

test_quorum_inserts

Integration tests (asan, old analyzer, 3/6)

test_storage_rabbitmq

CH Inc sync

It was broken in general, no relevant failures