12. Backups and changefeeds

A cluster that survives node loss, zone loss and region loss still does not survive DELETE FROM orders without a WHERE clause, a bad migration, or a ransomware event. Replication is not backup, and this chapter is about the two ways data legitimately leaves the cluster: as a backup, and as a stream.

Backups

BACKUP INTO 's3://bucket/crdb?AWS_ACCESS_KEY_ID=…&AWS_SECRET_ACCESS_KEY=…'
  AS OF SYSTEM TIME '-10s';

BACKUP INTO LATEST IN 's3://bucket/crdb?…'     -- incremental
  AS OF SYSTEM TIME '-10s';

   FULL         every range, exported as SST files
   INCREMENTAL  only the revisions since the last backup,
                read from MVCC history

   A "backup collection" is a full plus its chain of
   incrementals, in one destination, addressed by time.

Three properties worth knowing:

Backups are distributed and transactionally consistent. Every node exports its own ranges directly to object storage — there is no single machine funnelling terabytes — and the result is a snapshot of one instant, not a smear across the run.

AS OF SYSTEM TIME '-10s' is not decoration. It makes the backup a follower-readable, contention-free snapshot (chapter 8) rather than one that fights live traffic.

Incrementals are read out of MVCC history, which ties them directly to chapter 6’s GC TTL.

Danger

If the GC TTL is shorter than the gap between incremental backups, the history the incremental needs has already been collected, and the backup fails — or silently has to be a full backup instead. gc.ttlseconds must exceed your incremental interval, with margin. This is the single most common backup misconfiguration in the product.

-- what exists, and when
SHOW BACKUPS IN 's3://bucket/crdb?…';
SHOW BACKUP LATEST IN 's3://bucket/crdb?…';

Scheduling is built in, which is better than a cron job that nobody notices has stopped:

CREATE SCHEDULE store_backup
  FOR BACKUP INTO 's3://bucket/crdb?…'
  RECURRING '@hourly'
  FULL BACKUP '@daily'
  WITH SCHEDULE OPTIONS first_run = 'now';

SHOW SCHEDULES;

Restores

RESTORE FROM LATEST IN 's3://bucket/crdb?…';

-- selectively, and into a different name
RESTORE TABLE store.orders FROM LATEST IN 's3://…'
  WITH into_db = 'recovery';

-- a specific point in the collection's history
RESTORE FROM '/2026/09/21-030000.00' IN 's3://…';

   RESTORE is:
     • offline for the objects being restored — the
       table does not exist until the job finishes
     • a JOB: SHOW JOBS, pausable, resumable
     • as fast as your object storage and network
       allow, in parallel across nodes

Caution

A backup you have never restored is a hypothesis. Restore into a scratch database on a schedule, check row counts and a few known values, and time it — because the number you actually need during an incident is how long a restore takes, and nobody knows that until they have done it.

For the accident that happened twenty minutes ago and is still inside the GC window, the faster recovery is not a restore at all (chapter 6):

INSERT INTO orders
SELECT * FROM orders AS OF SYSTEM TIME '-20m'
WHERE id NOT IN (SELECT id FROM orders);

Getting data in

IMPORT INTO users (id, email, region)
  CSV DATA ('s3://bucket/users.csv?…');

-- from another PostgreSQL-shaped system
IMPORT INTO orders PGCOPY DATA ('s3://…');

   IMPORT INTO   appends to an existing table; the table
                 is OFFLINE for the duration
   COPY          smaller, online, ordinary transactions
   INSERT batches 1k–10k rows per statement is the
                 practical sweet spot for app-driven loads

After any bulk load, two things follow immediately: ANALYZE the table so the optimiser stops believing it is empty (chapter 9), and check the range distribution, because a load that arrived in key order has just built the hotspot of chapter 3.

Changefeeds

Change data capture is part of the database rather than a separate system reading its log. Compare the Debezium guide, which exists because PostgreSQL and MySQL make you build this from the outside.

CREATE CHANGEFEED FOR TABLE orders, payments
  INTO 'kafka://broker:9092?topic_prefix=crdb_'
  WITH updated, resolved = '10s', diff, format = json;

Sinks include Kafka, cloud object storage, Google Cloud Pub/Sub, and webhooks. There is also a sinkless form that streams straight to your SQL session, which is the easiest way to see what a feed emits:

EXPERIMENTAL CHANGEFEED FOR TABLE orders WITH updated;

What it guarantees

   AT LEAST ONCE       a row change may be emitted more
                       than once. Consumers must be
                       idempotent — key on primary key
                       plus the `updated` timestamp.

   PER-KEY ORDERING    changes to one row always arrive
                       in order. Across rows, they do not.

   RESOLVED TIMESTAMPS "everything at or below time T has
                       been emitted" — the closed
                       timestamp of chapter 6, surfaced.
                       This is how a consumer knows it
                       has a consistent cut.

That last one is the feature that makes changefeeds genuinely useful rather than merely available. A resolved message lets a downstream warehouse commit a batch knowing nothing earlier can still arrive.

The shape of a message

{
  "after":   {"id": "…", "status": "paid", "total": 4200},
  "before":  {"id": "…", "status": "pending", "total": 4200},
  "updated": "1712558400.123456789,0",
  "key":     ["…"]
}

before requires WITH diff, and diff requires that the GC TTL still holds the previous version — the same dependency as incremental backups, for the same reason.

Operating them

SHOW CHANGEFEED JOBS;
PAUSE JOB …;  RESUME JOB …;

   A paused or failing changefeed resumes from its last
   checkpoint — as long as that checkpoint is still
   inside the GC WINDOW. Past that, the feed cannot
   resume and must be recreated with a full initial
   scan. A weekend-long outage plus a 4-hour TTL is
   exactly how that happens.

The most useful alert on a cluster running changefeeds is changefeed lag — how far resolved trails real time — with a threshold well inside the GC TTL.

Cluster-to-cluster replication

Backups protect against mistakes. They do not give you a second cluster ready to serve in minutes, because a restore takes as long as a restore takes.

Physical cluster replication streams one cluster’s data continuously into a standby cluster, which is not readable while it is replicating but can be failed over to at a chosen timestamp.

   PRIMARY ────continuous stream────► STANDBY
                                      (not serving)

   cutover: pick a timestamp, promote. recovery point
   is seconds, not "since the last backup".

   WHICH TOOL FOR WHICH DISASTER

   a bad DELETE 20 min ago     AS OF SYSTEM TIME  (ch 6)
   a bad migration yesterday   RESTORE from backup
   the whole cluster is gone   standby cluster cutover
   a region is gone            nothing — SURVIVE REGION
                               FAILURE already handled it

That last line is worth repeating: multi-region survival (chapter 11) and disaster recovery solve different problems. A cluster that survives region failure still needs backups, because it will happily replicate your mistaken DROP TABLE to every region in milliseconds.

Sizing and retention

   FULL weekly + INCREMENTAL hourly
     restore time: full + up to a week of increments
     storage: modest
     gc.ttlseconds must exceed 1 h, comfortably

   FULL daily + INCREMENTAL every 15 min
     restore time: shorter chains, faster recovery
     storage: larger
     gc.ttlseconds must exceed 15 min, comfortably

   RETENTION: keep enough fulls to survive a mistake
   you did not notice for a while. "Corruption found
   on Monday, introduced the previous Tuesday" is the
   scenario that decides your retention, not cost.

-- what the schedule actually did, not what you think it did
SELECT id, label, schedule_status, next_run, last_run
FROM [SHOW SCHEDULES] WHERE label LIKE 'store%';

The pipeline this makes possible

   CockroachDB ──changefeed──► Kafka ──► ClickHouse
      OLTP, the source            log      analytics
      of truth                             (ch: kafka,
                                            clickhouse)

That is the standard arrangement, and it is why chapter 1 said analytics belong elsewhere: the transactional store keeps its job, and the columnar store gets a continuous, ordered, checkpointed feed without anybody polling a table for updated_at.

What to take from this chapter

• Replication is not backup. The cluster survives hardware, not mistakes.
• BACKUP INTO is distributed, transactionally consistent, and should be taken AS OF SYSTEM TIME '-10s' so it never contends with live traffic.
• Incremental backups read MVCC history, so gc.ttlseconds must exceed your incremental interval — the most common backup misconfiguration there is.
• Backups can be scheduled inside the database; SHOW BACKUPS and SHOW SCHEDULES tell you what actually exists.
• RESTORE is a job, offline for the objects it restores. Practise it, because the number you need in an incident is how long it takes.
• Inside the GC window, AS OF SYSTEM TIME undo beats a restore for recent mistakes.
• After any IMPORT: run ANALYZE, and check you have not built a hotspot.
• Changefeeds are built in: at-least-once, per-key ordered, with resolved timestamps giving consumers a consistent cut. Consumers must be idempotent.
• A changefeed resumes from its checkpoint only while that checkpoint is inside the GC window. Alert on changefeed lag.

Next: tuning and operations — what to watch, what to set, and how to find the one query or one range that is hurting you.