@Rocky Question: Best practice for synchronizing non-data metadata between two StarRocks clusters We are evaluating a production setup with two StarRocks 4.1.3 shared-nothing (coupled) clusters: a source/primary cluster and a target/standby cluster. We plan to use the official

starrocks-cluster-sync

tool for the supported objects such as internal table data, supported schema synchronization, views, and materialized view definitions. For objects not handled by the migration/sync tool, we are considering building separate automation to keep the target cluster operationally aligned with the source. Not handled by tool - ( possible workarounds ) 1. Creating External Catalogs 2. Database Backup Repositories 3. Roles 4. Users 5. UDFs 6. Routine Load Jobs 7. Resource Groups 8. Plugins 9. Session Variables 10. Config maps (FE and BE incase any FE or BE config is changed) 11. Backup Snapshots we need this in case we need to restore the backup taken in Source Questions: 1. Has anyone implemented a similar production setup for continuously keeping two StarRocks clusters synchronized beyond what

starrocks-cluster-sync

supports? 2. Are there recommended system tables, SQL commands, APIs, or metadata interfaces for extracting and reconciling: users/roles/grants, UDF definitions, Routine Load job definitions, Resource Groups, and plugin state? 3. For Routine Load, what is the recommended DR/failover approach to avoid duplicate Kafka consumption or offset inconsistencies? 4. Are there existing StarRocks-supported or community-recommended approaches that are better than building a custom Airflow-based reconciliation layer? 5. Is there anything important we should avoid synchronizing automatically between production clusters? Our goal is to maintain a continuously synchronized standby cluster while keeping failover safe and avoiding blind replication of environment-specific configuration.

For a production setup with StarRocks 4.1.3 (shared-nothing), using

starrocks-cluster-sync

for data and schema synchronization is the correct starting point. However, since this tool focuses on data and core objects (tables, views, MVs), maintaining operational alignment for the rest of the metadata requires a custom orchestration layer (like Airflow or a Python-based utility). Below are the recommended best practices and interfaces for synchronizing the objects you listed. 1. Extraction and Reconciliation (System Interfaces) There is no single "dump" command for all metadata. You should use a combination of

SHOW CREATE

statements and system views to extract definitions for reconciliation. Object Category | Recommended Command / Interface | Extract Strategy Users / Roles / Grants |

sys.grants_to_users

,

sys.grants_to_roles

,

sys.role_edges

| Query these

sys

views to programmatically rebuild

CREATE USER

,

CREATE ROLE

, and

GRANT

statements. External Catalogs |

SHOW CREATE CATALOG <name>

| This is the most reliable way to extract the full configuration for Hudi, Hive, Iceberg, etc. Resource Groups |

SHOW RESOURCE GROUPS ALL

| Returns a table with properties and classifiers. You must parse the

classifiers

column to reconstruct the

CREATE

statement. UDFs |

SHOW FULL FUNCTIONS IN <db>

| Look at the

Properties

column (JSON). It contains the

symbol

and

object_file

(URL) needed for

CREATE FUNCTION

. Routine Load |

SHOW ROUTINE LOAD

| The

JobProperties

and

DataSourceProperties

columns (JSON) contain the configuration. No

SHOW CREATE

exists; your script must map these JSON keys back to the

CREATE ROUTINE LOAD

syntax. Backup Repos |

SHOW REPOSITORIES

| Extract the location and broker info to run

CREATE REPOSITORY

on the target. Plugins |

SHOW PLUGINS

| Displays state. Note: Plugins often require local binary files on the FE/BE; ensure these are deployed to the standby cluster nodes first. Session Variables |

SHOW GLOBAL VARIABLES

| Capture variables that affect query behavior (e.g.,

enable_pipeline_engine

). 2. Routine Load DR & Failover Approach To avoid duplicate data or offset gaps during failover: * Dual Definition, Single Execution: Create the Routine Load jobs on the target cluster in a

PAUSED

state or do not create them until failover. * Offset Management: Since

starrocks-cluster-sync

is already syncing the table data, the standby table is a mirror. If you fail over and start a Routine Load job on the target, you must ensure it starts from the exact Kafka offset where the primary left off. * Recommended Strategy: 1. Periodically capture the

Progress

(offsets) from

SHOW ROUTINE LOAD

on the primary. 2. During failover, use

ALTER ROUTINE LOAD

on the target to set the

kafka_offsets

to the last captured values before resuming. 3. Note: If

starrocks-cluster-sync

is running, do not run the same Routine Load on the target, as this will cause data duplication. 3. Recommended Custom Reconciliation Layer While Airflow is common, a specialized Python script using the

starrocks-python-sdk

or

mysql-connector-python

is often lighter for metadata-only sync. * Declarative Setup: Treat your metadata as code. Store the extracted definitions (Users, Roles, Catalogs) in a version-controlled repository or a state file. * Idempotency: Your scripts should use

CREATE ... IF NOT EXISTS

or check existence via system views before attempting creation. * StarRocks Migration Tool (SMT): While primarily for migrating from other databases, SMT can generate schema scripts, but it is less useful for continuous metadata reconciliation compared to custom SQL-based scripts. 4. Important Precautions What NOT to synchronize automatically:

I have a few deeper questions to make sure we do not build automation around unsupported assumptions. 1. RBAC synchronization For

sys.grants_to_users

,

sys.grants_to_roles

, and `sys.role_edges`: • Is there an official/recommended way to reconstruct the complete RBAC state, including user authentication type, role inheritance, default roles, and direct user privileges? • Are there any grants or privilege types that cannot be safely reconstructed from these views? • For continuous reconciliation, would you recommend source → target automatic

CREATE/GRANT

operations but handling

DROP/REVOKE

separately to avoid accidental destructive changes? • Is there a recommended way to detect RBAC changes incrementally, or is periodic full-state comparison the expected approach? 2. User authentication We understand that privileges can be reconstructed separately from users, but what is the recommended approach for synchronizing users that use native password authentication? • Can user authentication definitions be safely extracted and recreated on another cluster? • Or is it better to manage user creation/passwords outside the synchronization process using an external identity/secrets system? • If LDAP/JWT/OAuth authentication is used, is the recommended approach simply to configure the same security integration independently on both clusters and synchronize only StarRocks authorization/RBAC? 3. Routine Load offsets and failover This is the area where we want to be particularly careful. You suggested capturing

Progress

from

SHOW ROUTINE LOAD

and starting the target from the last Kafka offsets. • Is the

Progress

value guaranteed to represent offsets that are fully committed and durable in the source StarRocks table? • If we periodically capture offsets and the source fails between two captures, could this cause either duplicate ingestion or skipped records? • Is there an officially recommended way to establish a consistent checkpoint between: Kafka offsets, Routine Load committed data on the source, and data replicated to the target through

starrocks-cluster-sync

? • During failover, is there a documented way to safely initialize a new Routine Load job with specific Kafka offsets? • Would you recommend keeping a target Routine Load job permanently PAUSED, or creating/configuring it only during failover? Our main concern is guaranteeing that activating ingestion on the target does not create an offset gap or duplicate data after the data synchronization tool has already replicated the source table. 4. Routine Load definition changes Since

SHOW ROUTINE LOAD

exposes JSON properties rather than a

SHOW CREATE

statement: • Is there any official API or internal metadata table that exposes the original

CREATE ROUTINE LOAD

definition more directly? • Are all

JobProperties

and

DataSourceProperties

values sufficient to reconstruct an equivalent job? • Are there properties that cannot be modified with

ALTER ROUTINE LOAD

and would require dropping/recreating the job? 5. UDF synchronization For `SHOW FULL FUNCTIONS`: • Is the output sufficient to reconstruct all supported UDF types, including SQL UDFs and external Java/Python UDFs? • For external UDFs, should the target always access the same artifact URI, or is there a recommended way to deploy artifacts independently and then recreate the UDF definition? • Are there any function properties that should not be copied directly between clusters? 6. External Catalog synchronization For `SHOW CREATE CATALOG`: • Are there catalog properties that should be excluded or replaced when recreating a catalog on another cluster, particularly credentials, endpoints, or environment-specific properties? • Is there any official recommendation for managing catalog definitions through Git/IaC rather than periodically extracting them from the source? 7. Plugins and Static Extensions For plugins: • Is

SHOW PLUGINS

sufficient to determine the complete desired plugin state, including version and artifact information needed to recreate the plugin? • Are there differences in how dynamic plugins versus the newer Static Extensions in StarRocks 4.1 should be managed across two clusters? • Would StarRocks recommend managing these through cluster deployment/configuration management rather than metadata synchronization? 8. Backup repositories and snapshots Regarding using the same repository definition and cloud storage location: • Are snapshots created by one StarRocks cluster officially supported for restore from another independent cluster running the same compatible version? • Are there any cluster metadata, encryption, storage-volume, or version compatibility restrictions we should validate before depending on cross-cluster snapshot restore for DR? • Would you recommend periodically testing restore from the standby cluster as part of DR validation? 9. Continuous metadata synchronization architecture Our current design is:

starrocks-cluster-sync

→ tables/data/schema/views/MV definitions Separate reconciliation service → RBAC → UDF definitions → Resource Groups → Routine Load definitions → selected metadata Git/IaC / deployment automation → External Catalogs → Plugins/Static Extensions → FE/BE configuration Failover controller → ingestion ownership → Routine Load activation → application traffic switching Does this separation align with how the StarRocks team would recommend designing a production standby/DR architecture? Finally, is there any existing internal tool, reference architecture, community project, or production deployment pattern for keeping two independent StarRocks clusters operationally synchronized beyond

starrocks-cluster-sync

that we should review before implementing our own reconciler? Are there any other organizatinns who are currently using this setup in production to reference @Rocky??

To ensure your automation is built on supported behavior for StarRocks 4.1.3, here is a technical deep dive into your specific concerns. 1. RBAC and User Authentication * Reconstruction: The

sys

views (

grants_to_users

,

grants_to_roles

,

role_edges

) provide the structure of authorization. However, they do not contain authentication details (e.g., password hashes, LDAP vs. Native). * Recommended approach: Query

information_schema.user_privileges

or use

SHOW USERS

to extract the authentication type. * Passwords: For native authentication, you can extract the encrypted password hash and use it in the

CREATE USER

statement:

CREATE USER ... IDENTIFIED BY PASSWORD '*HASH_STRING'

. * Incremental vs. Full: Periodic full-state reconciliation is safer than incremental tracking. Use a "source-of-truth" model where you generate the desired state from the Primary and apply it to the Standby. Handling

DROP/REVOKE

separately is a valid safety measure, but for a true DR standby, strict mirroring (including removals) is usually preferred to ensure security consistency. * Best Practice: For production, managing users via External Security Integrations (LDAP, OAuth) is the recommended path. In this case, you only synchronize the

CREATE USER

(without password) and the associated RBAC roles/grants. 2. Routine Load & Failover Consistency This is the most sensitive area because

starrocks-cluster-sync

and Routine Load cannot ingest into the same table simultaneously. * The Inconsistency Risk: If

starrocks-cluster-sync

is syncing

table_A

from Primary to Standby, it is already copying the data that was ingested by Routine Load on the Primary. Starting a Routine Load job on the Standby while the sync is active will cause duplicate data. * Recommended Strategy: 1. Definitions Only: Reconcile the Routine Load definition to the Standby but keep the job in a

STOPPED

state. 2. Progress Durability: The

Progress

(offsets) shown in

SHOW ROUTINE LOAD

represents data that has been successfully committed to StarRocks and is durable. 3. Failover Logic: * Stop the

starrocks-cluster-sync

for that table. * Create/Start the Routine Load job on the Standby using the

kafka_offsets

property, initialized to the values captured from the Primary's last successful

Progress

. * Reconstruction: There is no official "Show Create Routine Load" API. Reconstructing from

JobProperties

and

DataSourceProperties

in the

SHOW ROUTINE LOAD

JSON output is the standard method used by community automation scripts. 3. UDF Synchronization * Sufficiency:

SHOW FULL FUNCTIONS

provides the

symbol

and

object_file

(URL). This is sufficient to reconstruct

CREATE FUNCTION

. * Artifact Management: The

object_file

URI must be accessible by both clusters. * Best Practice: Use a centralized artifact repository (S3, Artifactory) that both clusters can reach. This ensures that when you recreate the UDF on the Standby, it pulls the exact same binary. 4. External Catalogs * Environment Specifics: When using

SHOW CREATE CATALOG

, watch out for

endpoint

or

metastore.uris

that might be internal to the Primary's network zone. * Git/IaC: For Catalogs, Git/IaC is the officially recommended approach. Catalogs rarely change and often contain credentials (which may be masked in

SHOW CREATE

output). Managing them via Terraform or Ansible allows you to use environment-specific variables for the Standby. 5. Plugins vs. Static Extensions (v4.1) * Static Extensions (Preferred): From v4.1 onwards, StarRocks introduced Static Extensions. These are JAR files placed in the FE/BE

lib

or

ext

directories. * Management: These should be managed via your deployment automation (Ansible/Kubernetes), not via SQL synchronization.