title | sidebar_label | description |
---|---|---|
ALTER TABLE ATTACH PARTITION | ATTACH PARTITION | ATTACH PARTITION SQL keyword reference documentation. |
Attaches one or more partitions to a table. This feature is designed to support attaching partitions from a backup or copying and attaching partitions across a network between QuestDB instances.
Before running an ATTACH PARTITION
query, partitions must first be copied in the format <partition_name>.detached
to the storage directory of the table to which they will be attached. Given a partition named 2020
, it must be copied to the destination table as 2020.detached
. Details of the naming format for detached partitions along with example commands for copying and attaching partitions can be found in the examples section below.
:::caution
This operation is only supported on tables which do not contain SYMBOL
types
:::
Syntax
Examples
Assuming a backup directory ~/backup/
exists for a table sensor_data
which was partitioned by YEAR
, the following example demonstrates how to attach the 2020 partition to a table named sensor_data
:
cp -r ~/backup/2021-01-01/sensor_data/2020 /path/to/questdb/db/sensor_data/2020.detached
The directory structure of the sensor_data
table should look like the following:
db
└── sensor_data
├── 2020.detached
│ ├── device_version.d
│ ├── driver.d
│ └── ...
└── 2021
├── device_version.d
├── driver.d
└── ...
The following SQL query will attach the 2020
partition to the sensor_data
table:
ALTER TABLE sensor_data ATTACH PARTITION LIST '2020';
The structure of the sensor_data
table will then reflect that the 2020
partition has been successfully attached:
db
└── sensor_data
├── 2020
│ └── ...
└── 2021
└── ...
:::info
Details of creating backups can be found on the BACKUP syntax reference page.
:::
Partitions may be referred to by YEAR
, MONTH
or DAY
:
--DAY
ALTER TABLE sensor_data ATTACH PARTITION LIST '2019-05-18';
--MONTH
ALTER TABLE sensor_data ATTACH PARTITION LIST '2019-05';
--YEAR
ALTER TABLE sensor_data ATTACH PARTITION LIST '2019';
Multiple partitions may be copied and attached to a table:
cp -r ~/backup/2021-01-01/sensor_data/2020 /path/to/questdb/db/sensor_data/2020.detached
cp -r ~/backup/2021-01-01/sensor_data/2019 /path/to/questdb/db/sensor_data/2019.detached
The partitions are attached by providing a comma-separated list of partitions:
ALTER TABLE sensor_data ATTACH PARTITION LIST '2019','2020';