Recovering Deleted Databases, Tables, and Partitions in StarRocks

Background

A colleague reported a missing table early in the morning, prompting an investigation into StarRocks table recovery procedures.

Recovery Capabilities and Limitations

StarRocks cannot recover tables removed with TRUNCATE TABLE. However, databases, tables, or partitions deleted via DROP operations can be restored using the RECOVER command, but only within a specific time window.

The retention window is controlled by the FE dynamic parameter catalog_trash_expire_second, which defaults to 1 day (86400 seconds). After this period, recovery is impossible.

Configuration Details

Temporary Change (Runtime Only)

The parameter can be adjusted at runtime without restarting the FE. The following screenshot shows the temporary modification interface:

Permanent Change (fe.conf)

For persistence across restarts, modify fe.conf and restart the FE. The screenshot below illustrates the permanent configuration:

Critical Limitations

Only the most recently deleted metadata can be recovered (default 1 day, configurable via catalog_trash_expire_second in fe.conf).

If a new object with the same name and type is created after deletion, the previously dropped metadata cannot be recovered.

Tables dropped with DROP TABLE ... FORCE are not recoverable. The following screenshot shows the error when attempting to recover a force-dropped table:

RECOVER Command Syntax

Recover Database

RECOVER DATABASE <db_name>;

Recover Table

RECOVER TABLE [db_name.]table_name;

Recover Partition

RECOVER PARTITION partition_name FROM [db_name.]table_name;

Practical Examples

Recover a database named example_db: RECOVER DATABASE example_db; Recover a table named example_tbl in example_db: RECOVER TABLE example_db.example_tbl; Recover partition p1 from table example_tbl:

RECOVER PARTITION p1 FROM example_tbl;

Real-World Recovery Walkthrough

The author documents an actual incident where a table was dropped and a replacement table created, then the original needed restoration:

# Drop the original table
DROP TABLE starrocks_audit_tbl_bak0806_bak;

# Create a new table with the same name
CREATE TABLE starrocks_audit_tbl_bak0806_bak (...);

# Discover issues with the new table; need to restore the old one
# Rename the new table (DO NOT drop it, or it will overwrite the dropped table's metadata!)
ALTER TABLE starrocks_audit_tbl_bak0806_bak RENAME starrocks_audit_tbl_bak0806_bak_new;

# Recover the original table
RECOVER TABLE starrocks_audit_tbl_bak0806_bak;

# Verify data restoration
SELECT COUNT(*) FROM starrocks_audit_tbl_bak0806_bak;

The following screenshot confirms successful recovery:

Database Recovery Demonstration

Screenshot showing the process of recovering a dropped database:

Partition Recovery Demonstration

(1) Drop a partition:

(2) Recover the partition data:

Production Best Practices

To prevent accidental data loss, the author recommends:

Revoking DROP permissions for developers in production environments.

Requiring review/approval for all DDL operations.

Using RENAME to back up tables before updates, enabling quick rollback.

Only cleaning up renamed backups after the new version is verified stable.