Restores one or more partitions to the table where they have been detached from by using the SQL ALTER TABLE DETACH PARTITION statement.
This feature is part of the manual S3/cold storage solution, allowing restoring data manually.
ATTACH PARTITION, the partition folders to be attached must
be made available to QuestDB using one of the following methods:
- Copying the partition folders manually
- Using a symbolic link
This section describes the details of each method.
Partition folders can be manually moved from where they are stored into the
table folder in
db. To make the partitions available for the attach operation,
the files need to be renamed
For example, in a table partitioned by year, given a partition folder named
2020.detached, rename it as
2020.attachable, and move it to the table
Symbolic links can be used to
attach partition folders that exist potentially in a different volume as cold
storage. The partitions attached in this way will be read-only. To make
detached partition folders in cold storage available for attaching, for each
partition folder, create a symbolic link with the name
<partition_name>.attachable from the table's folder, and set the target
path to the detached partition folder.
SQL statements that hit partitions attached via symbolic links may have slower runtimes if their volumes have a slower disk.
In Windows, symbolic links require admin privileges, and thus this method is not recommended.
Partitions attached via the symbolic link approach are read-only for the following operations:
DROP PARTITION: Once the partition folders are unlinked, the symbolic links are removed, but the content remains. Detaching a partition that was attached via symbolic link does not create a copy
UPDATE: Attempts to update the read-only partitions result in an error.
INSERT: Attemps to insert data into a read-only partition result in a critical-level log message being logged by the server, and the insertion is a no-op. If Prometheus monitoring is configured, an alert will be triggered.
For read-only partitions, the following operations are supported:
Assuming the QuestDB data directory is
/var/lib/questdb/db, for a table
with AWS S3 for cold storage:
Copy files from S3:cd /var/lib/questdb/db/x# Table x is the original table where the partition were detached from.mkdir 2019-02-01.attachable && aws s3 cp s3://questdb-internal/blobs/20190201.tar.gz - | tar xvfz - -C 2019-02-01.attachable --strip-components 1mkdir 2019-02-02.attachable && aws s3 cp s3://questdb-internal/blobs/20190202.tar.gz - | tar xvfz - -C 2019-02-01.attachable --strip-components 1
Execute the SQL
ALTER TABLE ATTACH PARTITIONcommand:ALTER TABLE x ATTACH PARTITION LIST '2019-02-01', '2019-02-02';
After the SQL is executed, the partitions will be available to read.
The following example creates a table
tab with some data, detaches all but the
last partition, and demonstrates how to attach the partitions using symbolic
These SQL statements create table
tab partitioned by year, and insert seven
rows that result in a total of seven partitions:
This SQL statement detaches partitions 2022, 2023, 2024, 2025, 2026, and 2027:
Assuming QuestDB's root directory to be
content of the table folder is:
You can now move those
<partition_name.detached> folders to a different path,
potentially a different volume:
When you want to attach these partitions back, create a symlink for every
partition to be attached from the table folder
The content of the table folder should look like this now:
After the symbolic links have been created, the partitions can be attached with the following SQL statement:
- The SQL reference to the partitions does not include the suffix
WHEREclause is not supported when attaching partitions.
- The latest partition cannot be detached. However, it can be irreversibly deleted using DROP TABLE.
- S3/Cold storage interaction is manual. Partitions can only be attached to the same table they were detached from. The table name must be the same. Moving partitions between tables or database instances is not supported.
- The operation will fail if a partition already exists. We are working on functionality to allow merging data in the same partition for attaching.