< Rocky> how to setup backup strategy for starrocks using s3 StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> how to setup backup strategy for st...

Lý Thành

10/01/2026, 9:41 AM

@Rocky how to setup backup strategy for starrocks, using s3 backup, special about inscreasemental backup, as i can not backup full cluster every day.

Rocky

10/01/2026, 9:41 AM

To set up a backup strategy for StarRocks using AWS S3, you can use the built-in

BACKUP

and

RESTORE

functionality. Since StarRocks currently only supports Full backups (it does not have a native "differential" or "incremental" flag that automatically tracks changes), the standard way to implement an incremental strategy is through Partition-level backups. 1. Create an S3 Repository First, you must create a repository to link StarRocks to your S3 bucket. StarRocks uses the Hadoop

s3a://

protocol for S3.

Copy code

sql
CREATE REPOSITORY my_s3_repo
WITH BROKER 
ON LOCATION "<s3a://your-bucket-name/starrocks-backups/>"
PROPERTIES(
    "aws.s3.access_key" = "your_access_key",
    "aws.s3.secret_key" = "your_secret_key",
    "aws.s3.region" = "us-east-1"
);

_Note: Ensure your StarRocks cluster has network access to S3. You can also use

aws.s3.use_instance_profile

= "true" if your nodes have IAM roles._ 2. The "Incremental" Strategy: Partition Backup Because you cannot backup the full cluster every day, you should structure your tables with a Partitioning Plan (usually by date). You then only back up the latest partitions that have changed. Step A: Full Backup (Once) Perform a full backup of your critical tables once to establish a baseline.

Copy code

sql
BACKUP SNAPSHOT your_db.initial_full_backup
TO my_s3_repo
ON (table1, table2);

Step B: Daily Incremental (Partition-level) Every day, back up only the newest partition(s). For example, if today is October 1st and your table is partitioned by day:

Copy code

sql
BACKUP SNAPSHOT your_db.daily_snapshot_20261001
TO my_s3_repo
ON (
    your_large_table PARTITION (p20261001)
);

3. Key Considerations * Snapshot Naming: Each

snapshot_name

must be unique within the repository. It is common practice to include the date in the name. * Concurrency: Only one

BACKUP

RESTORE

job can run per database at a time. * Shared-data Clusters: Note that Shared-data (Compute-Storage Decoupled) StarRocks clusters do not support the

BACKUP

and

RESTORE

commands, as they rely on the object storage's own versioning/backup capabilities. * Version 3.4+ Enhancements: If you are on StarRocks v3.4.0 or later, you can also back up metadata for external catalogs, UDFs, and views using the same

BACKUP

syntax. Summary of Strategy Strategy | Implementation Full Backup | Run

BACKUP

on the whole database/table once a week. Incremental | Run

BACKUP

on specific partitions (e.g., today's date) every night. Retention | Manually delete old snapshots in S3; StarRocks does not currently automate the deletion of remote snapshot files via SQL. References • sql-reference/sql-statements/backup_restore/BACKUP.md • administration/management/BE_configuration.md

Lý Thành

10/01/2026, 9:45 AM

@Rocky i do full cluster backup weekly, so when this week backup success, then delete previous full backup

Rocky

10/01/2026, 9:45 AM

In StarRocks, the standard

BACKUP

command creates snapshots in your S3 repository, but StarRocks does not currently provide a SQL command to delete specific snapshots from that repository. To implement a "delete previous after success" strategy, you must use external automation (scripts) or S3-native features. 1. Manual Cleanup via S3 CLI (Recommended for Scripts) When your weekly backup succeeds, you can trigger a script to delete the previous snapshot's folder in S3. 1. Check Status: Ensure the new backup is finished by checking

SHOW BACKUP

. 2. Delete Files: Use the AWS CLI to remove the old snapshot's data. StarRocks stores snapshots in S3 under a path defined by the snapshot name.

Copy code

bash
    # Example: If your old snapshot was named 'full_backup_last_week'
    aws s3 rm <s3://your-bucket-name/starrocks-backups/full_backup_last_week> --recursive

2. AWS S3 Lifecycle Policies (Automated) If you always name your backups with a consistent prefix (e.g.,

weekly_full_

), you can set an S3 Lifecycle Rule on the bucket to automatically delete objects older than 8 or 14 days. This removes the need for "delete after success" logic entirely. 3. Community Tool:
starrocks-backup-and-restore
There is a widely used community CLI tool designed specifically to solve this gap. It handles: * Incremental backups (only backing up changed partitions). * Lifecycle Management: It can automatically prune old backups based on a "keep-last-N" policy. * Success Verification: It ensures the new backup is valid before deleting old ones. _ _Reference:* deep-bi/starrocks-backup-and-restore 4. Important Distinction: Shared-Data Mode If you are using StarRocks Shared-data mode (Compute-Storage Decoupled, v3.0+), the

BACKUP

command is not supported. Instead, you use Cluster Snapshots (introduced in v3.5). * Cluster Snapshots are managed differently and often provide more built-in retention control. * For standard clusters (Shared-nothing), the manual/scripted S3 cleanup remains the only method. Summary Workflow 1. Run

BACKUP SNAPSHOT your_db.new_backup TO s3_repo ...;

2. Poll

SHOW BACKUP

until

State

FINISHED

. 3. Execute

aws s3 rm <s3://bucket/path/old_backup> --recursive

. Note: Only

DROP REPOSITORY

exists in SQL, but it only deletes the connection to S3, not the actual backup data. To save on S3 costs, you must delete the files in S3. References • sql-reference/sql-statements/backup_restore/DROP_REPOSITORY.md • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/SHOW_SNAPSHOT.md • administration/management/BE_configuration.md

Lý Thành

10/01/2026, 10:24 AM

@Rocky how to backup full starrocks cluster, all databases in one command

Rocky

10/01/2026, 10:24 AM

Whether you can back up the entire cluster with one command depends on your StarRocks architecture (Shared-nothing vs. Shared-data). 1. For Shared-nothing Clusters (Standard) There is no single SQL command to back up all databases at once in a Shared-nothing cluster. The

BACKUP

command is scoped to a single database. To back up the full cluster, you must execute a backup for each database individually:

Copy code

sql
-- You must run this for every database in your cluster
BACKUP DATABASE db1 SNAPSHOT db1_snapshot TO my_s3_repo;
BACKUP DATABASE db2 SNAPSHOT db2_snapshot TO my_s3_repo;

Recommended Approach: Since you cannot run multiple backup jobs within the same database simultaneously, but you can run them for different databases, most users use a shell script or a Python script to: 1. Run

SHOW DATABASES;

2. Iterate through the list and trigger

BACKUP DATABASE <db> ...

for each. 3. Monitor status using

SHOW BACKUP FROM <db>;

. 2. For Shared-data Clusters (v3.5+) If you are using the Shared-data (Compute-Storage Decoupled) architecture, you can use the Cluster Snapshot feature. This effectively backs up the *entire cluster state*—including all databases, tables, catalogs, users, and privileges—with one configuration setting. To enable automated full-cluster snapshots:

Copy code

sql
-- This one command enables continuous full cluster backups
ADMIN SET AUTOMATED CLUSTER SNAPSHOT ON 
STORAGE VOLUME my_s3_volume;

* What it does: It automatically captures the metadata (FE) and data (CN/Object Storage) of the entire cluster every few minutes (default 10 mins). * How to view:

SELECT * FROM information_schema.cluster_snapshots;

Comparison Summary Feature | Shared-nothing | Shared-data (v3.5+) Command |

BACKUP DATABASE <name>

ADMIN SET AUTOMATED CLUSTER SNAPSHOT ON

Scope | Single Database | Entire Cluster (All DBs + Metadata) Automation | Requires external script | Built-in / Continuous Restore | Database/Table level | Full Cluster Restore Note for Shared-nothing users: If you need a tool to simplify the "all databases" workflow, the starrocks-backup-and-restore community tool is frequently used to automate full-cluster backups and retention. References • administration/cluster_snapshot.md • sql-reference/sql-statements/backup_restore/BACKUP.md • administration/management/Backup_and_restore.md • sql-reference/sql-statements/backup_restore/SHOW_BACKUP.md References • sql-reference/sql-statements/backup_restore/BACKUP.md • administration/management/Backup_and_restore.md

Lý Thành

10/02/2026, 6:42 AM

@Rocky SELECT * FROM FILES ( "aws.s3.endpoint" = "localhost", "path" = "s3://datalake/funding_rate/dt=2026-10-02/funding_rate_1790912706074.parquet", "aws.s3.access_key" = "lab", "aws.s3.secret_key" = "labsecret123", "format" = "parquet", "aws.s3.enable_ssl" = "false", "aws.s3.use_aws_sdk_default_behavior" = "false", "aws.s3.use_instance_profile" = "false", "aws.s3.enable_path_style_access" = "true" ) LIMIT 3; SQL Error [1064] [42000]: Access storage error. Error message: Connection refused

Rocky

10/02/2026, 6:42 AM

The

Connection refused

error occurs because the StarRocks Backend (BE) or Compute Node (CN) processes are trying to connect to the S3 service on their own local network interface, but cannot find it there. In StarRocks, the

FILES()

function is executed by the BEs/CNs. When you specify

localhost

, each BE node looks for the S3 service (likely MinIO or SeaweedFS, given port 8333) running on its own machine. How to fix it: 1. Replace
localhost
with a Reachable IP or Hostname Do not use

localhost

unless the S3 service is literally running on the same physical/virtual machine as every StarRocks BE node. Instead, use the internal LAN IP of the server hosting your S3 service. * Change:

"aws.s3.endpoint" = "<http://192.168.1.10:8333>"

(replace with your actual server IP). 2. Fix for Docker Users If you are running StarRocks in Docker: * Linux: Use the IP of the host machine or the Docker network IP of the S3 container. * Mac/Windows: Use

"aws.s3.endpoint" = "<http://host.docker.internal:8333>"

. * Docker Compose: Use the service name of your S3 container (e.g., `http://minio:8333`). 3. Check S3 Service "Listening" Address Ensure your S3-compatible service (MinIO/SeaweedFS) is configured to listen on all interfaces (

0.0.0.0

), not just

127.0.0.1

. If it only listens on

127.0.0.1

, it will reject any connection coming from the StarRocks nodes, even if they are on the same machine. Corrected Query Example

Copy code

sql
SELECT * FROM FILES
(
    -- Use the actual IP address of the server
    "aws.s3.endpoint" = "<http://10.0.0.5:8333>", 
    "path" = "<s3://datalake/funding_rate/dt=2026-10-02/funding_rate_1790912706074.parquet>",
    "aws.s3.access_key" = "lab",
    "aws.s3.secret_key" = "labsecret123",
    "format" = "parquet",
    "aws.s3.enable_ssl" = "false",
    "aws.s3.use_aws_sdk_default_behavior" = "false",
    "aws.s3.use_instance_profile" = "false",
    "aws.s3.enable_path_style_access" = "true"
)
LIMIT 3;

Verification Step From one of your StarRocks BE nodes, run this command to see if the port is reachable:

Copy code

bash
curl http://<your-s3-ip>:8333

If you get a response (even an XML error from S3), the connection is working. If you get

Connection refused

there, the issue is your network or the S3 service configuration. References • sql-reference/sql-functions/table-functions/files.md • integrations/authenticate_to_aws_resources.md References • sql-reference/sql-functions/table-functions/files.md

Open in Slack

Previous Next