@Rocky What is the format of the backup files for primary key tables? The documentation here - https://docs.starrocks.io/docs/administration/management/Backup_and_restore/ doesn't mention anything about file format.

The backup files for Primary Key tables in StarRocks do not use a single "standard" file format like a single Parquet or CSV file. Instead, a backup consists of a collection of files that represent a physical snapshot of the table's storage structure at a specific point in time. For a Primary Key table, the backup stored in your repository (S3, HDFS, etc.) includes the following components: 1. Data Files (

.dat

) These are the segment files that contain the actual table data. * Format: They are stored in StarRocks' proprietary columnar storage format (which is architecturally similar to Parquet but optimized for the StarRocks engine). * Immutability: Once written, these files are immutable. The backup process essentially copies these segment files from the BE (Backend) nodes to your remote repository. 2. Delete Vectors (DelVectors) Primary Key tables use a "Delete+Insert" strategy. * Format: These files track which rows in the

.dat

segment files have been deleted or updated. * Role in Backup: The backup includes the specific versions of the DelVectors associated with the snapshot. This ensures that when you restore, StarRocks knows which records are the most current. 3. Primary Key Index * Format: The primary key index is a persistent structure (often a custom hash index) that maps primary keys to their physical locations (

rowset_id

,

segment_id

,

rowid

). * Restoration: Since StarRocks v2.5, Primary Key tables include this index state in the backup. This allows the index to be reconstructed or loaded immediately upon restoration, which is why Primary Key tables cannot be restored to StarRocks versions earlier than v2.5. 4. Metadata Files The repository will also contain metadata files (often in a sub-folder like

__meta/

) that describe: * The snapshot version and timestamp. * The mapping of tablets to physical segment files. * Schema information (column names, types, etc.). Summary Table Component | Extension/Format | Description Actual Data |

.dat

| Columnar segment files (proprietary). Delete Markers | DelVector | Binary vectors marking deleted rows. Index | PK Index | Mapping of keys to row locations. Metadata | Meta (JSON/Binary) | Tablet-to-file mapping and schema info. Important Note: Because these files are in a proprietary internal format, they are not designed to be read directly by external tools (like Spark or Hive) from the backup repository. To access the data externally, you should use the Export or INSERT INTO FILES commands to convert the data into Parquet or CSV. References • administration/cluster_snapshot.md • sql-reference/sql-statements/backup_restore/BACKUP.md • faq/Exporting_faq.md

@Rocky My plan is to use a CRON job (like kestra) to automate backups. It would run something like

BACKUP DATABASE requests SNAPSHOT requests_backup
TO requests
ON (TABLE requests PARTITION (p2026050520));

And the partition being backed up is dynamically computed from the time the cron job is triggered

Is there anything I need to change? Like I need to make the snapshot name dynamic too?

Yes, there are a few important changes and considerations you should make for your automated backup plan: 1. The Snapshot Name MUST be Dynamic In StarRocks, a

SNAPSHOT

name (the label) must be globally unique within the scope of the repository and database. * What happens if it's not? If you use

requests_backup

every time, the first run will succeed, but the second run will fail with an error stating that the snapshot already exists in the repository. * Recommendation: Append a timestamp or the partition name to your snapshot name. _ _Example:*

requests_backup_2026050520

2. Handle the Asynchronous Nature of Backups The

BACKUP

command returns immediately as soon as the task is submitted. It does not wait for the data to be uploaded to S3/HDFS. * Kestra Tip: If your Kestra flow continues to subsequent steps (like validation or notification), you should add a polling loop that checks

SHOW BACKUP FROM requests;

and waits until the

State

becomes

FINISHED

(or `CANCELLED`/`ERROR`). 3. Management of Old Snapshots StarRocks does not have a "retention policy" for backups. * Repo Growth: Every time your cron job runs, it creates a new set of files in your remote storage. * Manual Deletion: StarRocks does not provide a SQL command to delete snapshots from the remote repository (e.g., S3). You must manage the lifecycle of these files manually or via cloud provider policies (like S3 Lifecycle Rules) to delete old folders. 4. Partition Logic Your logic for backing up specific partitions is the recommended way to handle large tables. * Ensure the Partition Exists: Make sure your dynamic computation for the partition name

p2026050520

matches exactly how your partitions are named in StarRocks (they are case-sensitive). * Primary Key Tables: Since you are backing up a Primary Key table, ensure your StarRocks version is v2.5 or later (which is likely, given your syntax), as earlier versions had limitations on PK table restores. Revised SQL Example for your Automation Assuming Kestra provides a timestamp variable like `{{ execution.startDate }}`:

sql
-- 1. Submit the backup with a unique name
BACKUP DATABASE requests 
SNAPSHOT requests_backup_{{ now().format('yyyyMMddHHmm') }}
TO requests
ON (TABLE requests PARTITION (p{{ now().format('yyyyMMddHH') }}00));

-- 2. Monitoring (Check this via your script/Kestra loop)
SHOW BACKUP FROM requests;

Summary of what to check: 1. Unique Labels: Use

requests_backup_<timestamp>

. 2. State Polling: Monitor

SHOW BACKUP

to ensure success. 3. Storage Cleanup: Set up an S3/HDFS cleanup job for snapshots older than X days, as StarRocks won't delete them for you. 4. One at a time: Remember that only one backup/restore job can run per database at a time. If your cron triggers too frequently and a previous job is still running, the new one will fail. References • administration/management/Backup_and_restore.md • faq/Exporting_faq.md • sql-reference/sql-statements/backup_restore/BACKUP.md