Resetting a 118,000-Object CockroachDB Database Across Azure and AWS: Drop, Restore and Everything In Between

 

In Oracle, putting a schema back to an earlier point is routine: drop it, import it, done. In a distributed SQL database spread across two clouds, the same request turns into a small project. This post describes how we reset a large CockroachDB database to its pre-data-load backup on a 5-node, multi-region cluster, and why each step was needed.


The setup: one CockroachDB v26.2 cluster with 5 nodes in a 2 + 1 + 2 layout (Azure Canada Central as the primary region, Azure Canada East as a vote-only tie-breaker, AWS ca-central-1 as the secondary region). RF 5, SURVIVE REGION FAILURE. The database holds about 118,000 objects: roughly 70,000 tables and 47,000 views, from a core banking application.

1. Why it isn't as simple as in Oracle

The data is small, about 4 GiB. The difficulty is the number of objects:

  • The catalog is distributed. Every node caches table definitions (descriptor leases). A schema change must wait until every node has released the old version of every affected object, and that wait happens one object at a time. We measured about 23 ms per object.
  • Every catalog write is replicated across regions. Each definition and zone configuration is a replicated write that needs a quorum, so about 10 ms over the inter-cloud VPN.
  • Multi-region multiplies it. Each table gets its own zone configuration. Earlier, a single SET PRIMARY REGION on this database took 3.5 hours, almost all of it in catalog work rather than data.

So DROP DATABASE and RESTORE both touch about 118k catalog entries, and both take time.

2. Two ways to reset the database

A. Restore under a new name, then swapB. Drop, then restore
DowntimeAbout a minute (two renames)Drop plus restore (hours)
If the restore failsThe old database is still thereNo database until it is fixed
Databases at the same timeTwo, for a few hoursNever more than one

Option A is the safer default. Our requirement was to never hold two copies, so we went with B and made it as safe as possible with the preparation below.

3. Preparation: what a database backup does not cover

  1. Check the backup itself. SHOW BACKUPS IN '<uri>' and SHOW BACKUP ... WITH check_files confirm that it exists, what it contains and that every file is readable.
  2. Snapshot the cluster-level configuration. A database backup does not include cluster settings, users, system-level grants or the zone configurations of system ranges. We saved all of them, plus a ready-to-run "re-apply" SQL file:
    SELECT variable, value, default_value FROM [SHOW ALL CLUSTER SETTINGS] WHERE origin = 'override';
    SELECT target, raw_config_sql FROM [SHOW ALL ZONE CONFIGURATIONS] WHERE target NOT LIKE 'TABLE %';
    SHOW USERS; SHOW SYSTEM GRANTS; SHOW GRANTS ON DATABASE mydb;
    One setting matters a lot at this size: sql.schema.approx_max_object_count (default 20,000). Without raising it, a cluster with 118k objects refuses to create new ones, including during a restore.
  3. Find what changed after the backup. Anything created or altered after the backup will disappear with the restore. Descriptor modification times show it:
    SET allow_unsafe_internals = true;   -- this session only
    SELECT name, mod_time FROM crdb_internal.tables
     WHERE database_name = 'mydb' AND mod_time > '<backup time UTC>';
    We saved the SHOW CREATE output for each of these objects with its row count, and agreed with the team which data still mattered.

4. Scale up first: memory is the real risk


An earlier restore of this schema, in one job, pushed nodes with 32 GiB RAM into memory pressure. Two nodes froze and the cluster lost quorum. Catalog-heavy operations need memory, so before the drop and restore we moved every node from 8 vCPU / 64 GiB to 16 vCPU / 128 GiB, one node at a time, so the cluster stayed available throughout:

  1. A Terraform plan targeted at one VM, with the bigger size passed as a variable, so the file defaults stay unchanged. Check: exactly 1 to change.
  2. cockroach node drain --self --drain-wait=3m
  3. Apply the reviewed plan (the VM restarts with the new size; disks, IPs and data are kept).
  4. Wait until every node is live with 0 unavailable and 0 under-replicated ranges before touching the next one.
NodeDrainResizeBack to healthy
Tie-breaker (Azure East)10.3 min*1.4 min1.7 min
AWS node 13.1 min*1.0 min1.7 min
AWS node 23.1 min*1.1 min1.7 min
Azure primary node 23.6 min*1.2 min1.7 min
Azure primary node 13.6 min*1.4 min1.7 min
Whole rolling scale-up: 48.7 minutes, with the cluster available throughout.

* The drain reached its time limit each time, most likely releasing about 118k descriptor leases. After the first node we cut the limit from 10 to 3 minutes. Every node still restarted cleanly and was healthy within 2 minutes.

The order matters too: the tie-breaker first, then the secondary region, and the primary-region nodes that hold the leaseholders last, one at a time.

5. Cut-over: stop the application cleanly


ALTER ROLE app_user WITH NOLOGIN;      -- no new application connections
CANCEL SESSIONS (SELECT session_id FROM [SHOW CLUSTER SESSIONS]
                 WHERE user_name NOT IN ('root', 'node'));
SELECT job_id, job_type, status FROM [SHOW JOBS]
 WHERE status IN ('running', 'pending', 'paused');   -- must be empty

A schema change on 118k objects should never overlap with another schema change. Overlapping runs slow each other down and can conflict.

6. Run the drop in the background, on the node itself


A long DROP DATABASE runs inside a SQL session until it commits. If that session dies (laptop closed, VPN dropped, a remote-command timeout), the whole drop rolls back. So we started it as a systemd unit on one of the nodes, with its own timestamped log:

systemd-run --unit=crdb-drop-mydb \
  -p StandardOutput=append:/var/log/crdb-drop-mydb.log \
  -p StandardError=append:/var/log/crdb-drop-mydb.log \
  /bin/bash -c 'echo "START $(date -u)"; cockroach sql --certs-dir=... \
     -e "DROP DATABASE mydb CASCADE"; echo "END $(date -u) rc=$?"'

The laptop can then be switched off. Two things surprised people the first time:

  • The database still exists for a long time. It only disappears when the drop commits.
  • There is no job at first. The schema-change job is only created at commit. Until then, SHOW CLUSTER STATEMENTS shows the statement running.

After the commit, the data itself is removed later by a background garbage-collection job, after the GC TTL.

7. Restore and verify


RESTORE DATABASE mydb FROM '<backup subdirectory>' IN '<backup collection uri>' WITH DETACHED;
  • DETACHED makes it a background job, so no session needs to stay open.
  • The multi-region configuration (primary and secondary region, survival goal) comes back with the database, because the cluster still has the same regions.
  • Watch node memory while it runs, and PAUSE JOB if a node goes too high.
  • Verify: object counts against the backup, SHOW REGIONS FROM DATABASE, SHOW SURVIVAL GOAL, and 0 unavailable or under-replicated ranges.
  • Then ALTER ROLE app_user WITH LOGIN, an application sanity test, and the same rolling procedure to scale back down.
    

8. Timings

StepDuration
Rolling scale-up, 5 nodes48.7 min
DROP DATABASE (about 118k objects)[to be updated]
RESTORE DATABASE[to be updated]
Verification and application test[to be updated]
Rolling scale-down, 5 nodes[to be updated]

9. Lessons learned


  • Size the operation by object count, not data size. 4 GiB of data across 118k objects behaves very differently from 4 GiB in ten tables.
  • A database backup is not a cluster backup. Save cluster settings, users and system zone configurations separately, and keep a re-apply script.
  • Check for objects created after the backup before you drop anything.
  • Give catalog-heavy operations memory headroom, and scale one node at a time so the cluster never loses quorum.
  • Never run a long schema change from a laptop session. Run it on the server, in the background, with its own log.
  • Time every step. Wrapping each command in a small timing function produced a ready-made report for the change record.

Performed by me on CockroachDB v26.2.0 (CCL), on a 5-node proof-of-concept cluster spread across Azure (Canada Central and Canada East) and AWS (ca-central-1). The timings come from that run. Database, user and object names in the examples are generic, and no production data was used. Behaviour and timings depend on your version, object count, network latency and node sizing, so test in a non-production environment before using these steps.

Tamilselvan Perumal is an Oracle ACE Associate and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.