CockroachDB backup and recovery

Choosing the restore point

A restore point selects a state of the backed-up history. It does not select which transactions were good. Here the purchase commits first, then the mistaken 2.20 price, then payment 504. Choosing an earlier point keeps every included event, not just those the shop wants.

Restoring before the mistake recovers 20.00 and loses the payment record. Restoring after payment 504 recovers the payment and the earlier 2.20 mistake. Neither restore rewinds the provider. The later charge therefore needs reconciliation against the earlier database.

Both candidate points are after the purchase. Its agreed lines still total 29.00 and its stock consumption is already present. A repair that creates order 103 again would repeat work we retained. If the only problem were this one catalogue value, restoring an isolated copy to inspect the old price and correcting the live database might be sufficient. Here we follow the wider restore to understand its obligations.

Identify the unwanted commit from retained database and application evidence. The time someone noticed the price is not its commit time, and an application’s updated_at need not mark that boundary. Choose a supported timestamp before the unwanted change, record the source and object scope, then verify the resulting facts. The engine accepting a timestamp does not establish that it is the right business cut.

Full backups and incremental chains

A full backup supplies a complete starting copy for its selected scope. An incremental backup supplies changes since the preceding backup in its chain. A backup collection can contain several full backups, each with its following increments. The collection URI identifies the container of those paths; it is not itself one recoverable point.

These two commands do different work. The first starts a new full backup. Run the second later to append an increment to the latest full-backup path. Here external://shop_recovery stands for an already configured external storage connection, with suitable SQL and storage access.

BACKUP DATABASE shop
INTO 'external://shop_recovery'
WITH revision_history = true;

-- Run later, against the same collection and object scope.
BACKUP DATABASE shop
INTO LATEST IN 'external://shop_recovery'
WITH revision_history = true;

Both commands explicitly request revision history; the next section explains what that adds. First consider their file dependencies. Call a full backup A, its first increment B and its second increment C. C contains later changes, not another complete copy of A and B.

C cannot replace B. A usable route to C needs the starting full backup and the required increments through C, all readable with their necessary keys. A new full backup supplies another starting route, but it does not repair the missing files in the earlier one. Keep material by the recovery points it supports, rather than deleting an old full merely because newer increments exist.

Finding the backup to restore

List the collection’s full-backup paths, then inspect the selected path and its increments:

SHOW BACKUPS IN 'external://shop_recovery';

-- Replace <full-path> with a path returned above.
SHOW BACKUP FROM '<full-path>'
IN 'external://shop_recovery';

Read database_name, object_name and object_type for scope; backup_type, start_time and end_time for the full copy and later intervals. Check that the intended database and related objects are there and that the chain reaches the target. Keep the source identity and the selected full path with that evidence.

LATEST is convenient when creating increments. During an incident, make its choice explicit: it selects the most recent full-backup path, with that path’s increments available to the restore. A newer full can begin after the state you need. Inspect the paths before relying on LATEST, and pin the chosen path in the recovery record and restore command.

Revision history and interior restore points

Without captured revision history, the chain supports its saved backup points. Having full backup A and increment B does not make every moment between their timestamps restorable. Captured revisions can support an interior point within the history actually saved. The required file chain and the times that chain can reconstruct are separate questions.

A full backup with revision history can include retained revisions from before its backup timestamp. An increment with revision history captures the changes since its preceding backup, within the available revision window. An increment can add revision history even if an earlier backup omitted it, but that does not fill older gaps.

Neither kind can recreate versions already garbage-collected before capture. So inspect the captured bounds and test the target you want. The source’s garbage-collection period alone does not tell you what the backup covers. Scheduled backups protect the revisions they still need to capture, as the revision-history documentation explains.

Request the option explicitly when interior recovery is required. In the v26.2.8 drill, an unqualified BACKUP chain rejected an interior restore because revision history had not been captured. An explicit revision_history = false chain rejected it too. A separate full-and-increment chain using revision_history = true restored the intended interior cut. The current BACKUP option reference describes a true default; that differs from this observed run. Do not base recoverability on an assumed default.

Use AS OF SYSTEM TIME on RESTORE for the chosen point. This command’s path and timestamp are placeholders to replace with the inspected backup path and established cut:

RESTORE DATABASE shop
FROM '<full-path>' IN 'external://shop_recovery'
AS OF SYSTEM TIME '<chosen-timestamp>'
WITH new_db_name = 'shop_recovered';

The restore creates the selected state. It does not install all the backup’s old versions as queryable history in shop_recovered. To examine another earlier point, restore another candidate from the retained backup material. Keep the original evidence while deciding which candidate to use.

Choosing the restore scope

Choose the unit whose facts must agree. Restoring only payments would leave its references and the order agreement to be accounted for separately. This shop candidate uses a database restore so that products, orders, order lines, payments and operation mappings share the same cut.

A table restore is not an automatic dependency collector. Include the needed related objects deliberately. Options such as skip_missing_foreign_keys remove the missing constraint; they do not recover the referenced facts. A successful restore after skipping it has a different integrity contract. The restore dependency rules cover the supported combinations.

A database restore also does not recreate cluster users. By default, the restore operator owns the restored objects. Establish the intended runtime identities and grants, and check application access using those identities. The GRANTS option can restore backed-up privileges for corresponding destination usernames; it is not a substitute for verifying who those users are or what the service should now access.

Check the destination’s supported source version, capacity, required regions and storage access before starting. CockroachDB cannot restore a newer-version backup into an older engine; the documented forward path is the same or the next major version. Encrypted backups need the correct passphrase or usable KMS key. An operator able to list a bucket today has not established that a recovery cluster can read and decrypt every required object during an outage. The RESTORE reference specifies version, region, privilege and destination restrictions.

Proving the restored state

Metadata describes a path; validation tests progressively more of it. Keep the candidate isolated from ordinary callers and external workers while checking it. Restored dispatch records can describe effects that already happened.

verify_backup_table_data must accompany schema_only. Give each validation restore its own destination name. A successful checksum check is useful evidence, but still leaves the business assertions to make. The backup-validation guide describes these native checks.

Wait for job completion. Ordinary RESTORE blocks; with DETACHED, the returned job identifier only identifies the asynchronous job. Inspect that job with SHOW JOBS and retain its terminal status and any errors. Neither a job ID nor a file check means the shop has recovered.

Check both sides of the cut

For the earlier candidate, expect P7 at 20.00, pending order 103 and its agreed 29.00 total, with payment 504 and its operation mapping absent. Stock must remain 10/0/4. Keep agreement and payment sums separate so a join cannot multiply the line values:

SELECT
  (SELECT price FROM shop_recovered.products
   WHERE product_id = 'P7') AS p7_price,
  o.status,
  (SELECT sum(quantity * unit_price)
   FROM shop_recovered.order_items
   WHERE order_id = o.order_id) AS agreed_total,
  (SELECT coalesce(sum(amount), 0)
   FROM shop_recovered.payments
   WHERE order_id = o.order_id) AS recorded_paid
FROM shop_recovered.orders AS o
WHERE o.order_id = 103;
-- Before reconciliation: 20.00 | pending | 29.00 | 0.00

SELECT product_id, stock
FROM shop_recovered.products
ORDER BY product_id;
-- P3 10; P7 0; P9 4

Inspect constraints and representative other records as well. Read the recovered order through the application under its intended runtime identity, with dispatch still disabled. Check derived stores and still-applicable correction or deletion decisions before older data returns to ordinary use.

The small native drill restored only a projection of this shop: three products, order 103, two lines and the payment-operation tables. Its interior restore produced the expected earlier facts; a latest restore produced 2.20 and payment 504. This establishes those SQL outcomes on one disposable node with local backup storage. It does not measure production recovery time, test cloud credentials, demonstrate node survival or prove application runtime access. Rehearse the whole recovery path with representative size and access arrangements before relying on an outage budget.

Reconcile the later payment

The candidate can be prepared while the source is still serving traffic. Final reconciliation needs a finite source history. Stop new source writes and external dispatch, account for transactions and provider calls already in flight, and prevent stale callers or workers from adding authoritative work there. Preserve the final source evidence. Moving a SQL application to CockroachDB owns the complete application handover; these are the conditions the recovery depends on.

If the source was destroyed, work from the evidence that survived and retain any gaps explicitly. A missing local payment row does not establish that the charge failed. In this branch, the hypothetical provider offers a retained, queryable result for order-103-part-payment-1. Its immutable meaning is order 103, 10.00 by card, with local result payment 504. Query and compare that result before repairing the database.

A matching accepted provider result can justify recording the missing local payment. An unavailable result leaves the outcome unresolved. A conflicting order, amount or method needs investigation rather than an automatic insert. Those cases lead to different recovery actions even when the local payment row is absent in all three.

Record the result once

With ordinary writers excluded, first read the recovered operation mapping and any payment 504. If both already represent the original accepted operation, verify the order, amount, method and payment identity, and return that result. A changed amount under the same operation identity is a mismatch, not another partial payment.

If both are absent and the evidence matches, record the payment and its immutable operation mapping in one transaction. This is the insertion branch after those checks, using the example’s additional payment_operations table:

BEGIN;
SELECT order_id, status
FROM shop_recovered.orders
WHERE order_id = 103 FOR UPDATE;

-- After verifying the retained agreement and accepted evidence:
INSERT INTO shop_recovered.payments
  (payment_id, order_id, amount, method)
VALUES (504, 103, 10.00, 'card');

INSERT INTO shop_recovered.payment_operations
  (operation_id, order_id, amount, method, payment_id)
VALUES ('order-103-part-payment-1', 103, 10.00, 'card', 504);
COMMIT;

The mapping’s operation identifier is a primary key, and its payment identifier is unique and references the payment. Keep the mapping immutable. A constraint can reject a second identity or payment; the recovery procedure must still compare the stored payload and result. Plain ON CONFLICT DO NOTHING would hide whether the existing record means the same thing.

Before insertion, verify that order 103’s agreed lines still total 29.00, its state permits the payment, and the known 10.00 fits the remaining balance. Stop on partial or inconsistent local evidence. If the transaction aborts, retry the whole database decision using the same operation identity; if its commit result is uncertain, look up that identity before choosing the next action. CockroachDB transactions and retries develops those native retry paths.

The native drill inserted the payment and mapping, then read back the same mapping on a repeat. A blind duplicate insert was rejected with 23505. Payload comparison was performed by the verifier; no constraint was claimed to decide whether a changed amount was a valid retry. No provider request ran: the accepted outcome was supplied evidence.

Keep the retained purchase

The repaired database records 10.00 against 29.00, leaving 19.00 due and order 103 pending. P7’s agreed unit price remains 20.00. Its recovered catalogue price does not reprice the order. Stock remains P3 10, P7 0, P9 4 because the purchase already consumed it before the restore point.

Do not send another charge or call the purchase routine to recover this payment. An unavailable provider outcome remains unresolved. A mismatched order or amount requires investigation. If other later work depended on the 2.20 price, assess its original agreement and dependencies separately; restoring a database cannot decide its business treatment.

Return the recovered service to use

For this branch, the completed result is concrete. P7 is at 20.00 and order 103 is pending for 29.00. Payment 504 and its original operation mapping are recorded once, 19.00 is due, and stock is still 10/0/4. The old source can add no more authoritative work, and the finite set of later operations has dispositions, including named unresolved cases.

Verify runtime access and application reads, route callers to one recovered authority, and resume external dispatch only after its earlier effects are accounted for. Operating and securing a CockroachDB cluster covers cluster health and access in more detail.

Establish and test backup coverage for the recovered history before relying on it. Keep the original recovery material and evidence according to the agreed retention decision. Record the achieved recovery point, elapsed stages, restricted service and remaining exceptions. A restored database becomes a recovered service when its facts, authority and external obligations agree.