Checkpoints & resumable copies

A checkpoint is a durable record of how far a source has been read, so a restart continues where the last run stopped instead of re-copying from the beginning. It is what turns a one-shot copy into a repeatable incremental sync you can put on a timer.

Two settings control it, on the source endpoint:

Positions are namespaced as <source>:<cursor_id>, so several routes may share one store without colliding. checkpoint_store may embed credentials and is treated as a secret.

Which sources checkpoint

An SQLx source with no cursor_column is a destructive work queue, not a resumable read — progress is the deletion of claimed rows, so there is nothing to checkpoint. See Endpoints.

Picking a store

checkpoint_store selects the backend by URL scheme; a value with no scheme is a plain table/collection name in the source datastore.

Notes:

• ClickHouse requires an external store. It cannot cheaply upsert cursor rows, so a source-datastore checkpoint is rejected; cursor_id without a checkpoint_store disables resume with a warning. Use --resume when a missing resumable configuration must be fatal, or --no-resume when a full re-copy is intentional and resume diagnostics should be ignored.
• Object-store sources must point checkpoint_store at a different bucket or prefix than they read — a cursor object written under the source prefix would be listed and re-read as data. The source rejects an overlapping location.
• The default name carries a hash for a source name with other characters than letters, digits and _: public.orders gets mqb_cursors_public_orders_<hash>, so it cannot share a table with a source named public_orders. A table that already exists as mqb_cursors_public_orders keeps being used.
• A file store is written atomically (temp file + rename) and concurrent writers in one process are serialized, so several routes may share one file. Use one process per file: two processes that write the same file overwrite each other’s cursors.
• Cloud object-store checkpoints need the object-store feature compiled in.

Using it with copy

Pass --no-resume to make an intentional full copy without checkpoint diagnostics. For SQL and ClickHouse, cursor_column is still required to page through the source; the flag only ignores optional cursor_id / checkpoint_store state. It currently applies to cursor-based SQL and ClickHouse sources, MongoDB change streams, and object-store sources. It does not change native queue or CDC offsets and cannot be combined with --resume; for sources such as Kafka, NATS, and Postgres CDC, the flag is intentionally a no-op.

cursor_id, cursor_column, and checkpoint_store are ordinary endpoint config fields, so on the CLI they are just query parameters on --from:

# Incremental table → table sync. Re-run it as often as you like: each run copies
# only rows whose `id` is greater than the last successfully written row.
mqb copy \
  --from 'postgres://user:pass@localhost/app?table=orders&cursor_column=id&cursor_id=orders_sync' \
  --to   'clickhouse://localhost:8123?table=orders&database=analytics' \
  --drain

# Read-only source: keep the cursor next to the job instead of in the source DB.
mqb copy \
  --from 'mysql://ro_user:pass@reporting/app?table=events&cursor_column=event_id&cursor_id=events_export&checkpoint_store=file%3A%2F%2F%2Fvar%2Flib%2Fmqb%2Fcursors.json' \
  --to   'file:///data/events.jsonl' \
  --drain

# MongoDB bulk read that survives a restart mid-copy.
mqb copy \
  --from 'mongodb://localhost:27017?database=app&collection=orders&consume=capture_all&cursor_id=orders_dump' \
  --to   'file:///data/orders.jsonl'

A checkpoint_store URL inside a URI must be percent-encoded (:// → %3A%2F%2F), since it is a query-parameter value. In a YAML config it is written plainly:

orders_sync:
  input:
    sqlx:
      url: "postgres://user:pass@localhost/app"
      table: orders
      cursor_column: id
      cursor_id: orders_sync
      checkpoint_store: "file:///var/lib/mqb/cursors.json"
  output:
    clickhouse: { url: "http://localhost:8123", table: orders, database: analytics }

Translating a copy command into a config file

Most of a copy command maps field for field: URI query parameters become endpoint config keys, --drain becomes exit_on_empty: true, and --filter becomes a filter middleware on the input. mqb to-cli goes the other way — it prints a command for a loaded config — but there is no converter in this direction, so three differences are worth knowing.

--no-resume is not a field to copy across. It does not remove cursor_id from the config; it makes the consumer ignore it. Translating the fields literally therefore reverses the behaviour — the CLI run re-copies everything, the config route resumes and copies nothing. The correct translation is to omit cursor_id, which is how a config route says the same thing:

mqb copy 'sqlite:///data/app.db?table=orders&cursor_column=id&cursor_id=nightly' \
         'file:///data/orders.jsonl' --drain --no-resume

orders_dump:
  exit_on_empty: true
  input:
    sqlx: { url: "sqlite:///data/app.db", table: orders, cursor_column: id }  # no cursor_id
  output:
    file: { path: /data/orders.jsonl }

--resume cannot be reproduced. It generates a copy-… cursor id from the credential-redacted source, destination, and filter. Re-running the same command resumes correctly, and mqb checkpoint show SOURCE TARGET finds its position, but a config route has no way to name that id, so it starts a fresh checkpoint under whatever cursor_id you choose.

The defaults differ. copy defaults to concurrency: 4 and batch_size: 1024; a route defaults to 1 and 512. Write both out explicitly, or the translated route runs at a quarter of the concurrency for no visible reason.

--drain and checkpoints

--drain exits when the source yields an empty batch — i.e. when the checkpoint has caught up with the table. That is exactly the shape you want for a cron/systemd-timer job: each invocation drains the backlog since last time and exits 0. Without --drain, the same command runs forever, polling for new rows every polling_interval_ms (100 ms by default) — or backing off exponentially up to max_polling_interval_ms while drained, if you set it.

MongoDB capture_all follows a change stream once its initial read is done, so a --drain run ends when that stream goes quiet rather than at a known end of data. capture_new only ever emits changes made after it starts and is continuous by nature. For a one-shot read with a real end, use consume: snapshot — non-destructive, no replica set, and not resumable (it rejects cursor_id).

An object-store source ends a drain run when it reaches the end of the objects it listed, which can be before the prefix is exhausted — the checkpoint makes this safe rather than lossy: each run resumes at the last fully-acked object key, so repeated runs advance until one reports zero messages. Loop the job until it moves nothing if you need a single pass to cover everything.

Inspecting and editing a checkpoint

mqb checkpoint reads and changes a stored position without touching the store by hand:

mqb -c config.yaml checkpoint show --route orders_sync     # a configured route or consumer
mqb checkpoint show 'sqlite:///data/app.db?table=orders&cursor_column=id' \
  file:///data/orders.jsonl                                 # a `copy --resume` job

value:   int:3
source:  sqlx:orders:id
updated: 2026-09-30T08:12:44.120+00:00

• reset deletes the position, so the next run starts from the beginning. It prints the old value first.
• set --value V overwrites it, e.g. to replay from a known row (int:1000) or to undo a reset. Use the format show prints. For a SQL cursor_column source a value in another form than int:<number> or str:<text> is refused.
• Stop the route or copy first. reset and set do not coordinate with a running reader: its next acknowledged batch saves its own position over the edit.
• A copy --resume job is named by the same SOURCE, TARGET and --filter it ran with.
• Postgres CDC is refused: manage its replication slot instead.

Each position is stored with the source that wrote it and a timestamp. On SQL and ClickHouse the source is the table and cursor_column, on MongoDB the database and collection. A run refuses a position written by a different source instead of resuming from a value that means something else, e.g. after cursor_column changed under the same cursor_id. show marks it MISMATCH. Reset it, or pick a new cursor_id. Positions written by mq-bridge 0.4.16 or older still load and are upgraded on the next save; an older mq-bridge cannot read the new format and starts from the beginning.

Delivery semantics

Checkpoints are at-least-once, never at-most-once:

• The position advances only after the sink acknowledges, and only across the contiguous run of acks from the front of the batch. The first nack stops the advance.
• On a partial failure the in-memory read cursor rolls back to the committed boundary, so nacked rows are re-read on the next poll rather than skipped until a restart.
• A crash between “rows written” and “checkpoint flushed” replays that batch. Make the sink idempotent — see Upserts & insert-if-absent and Deduplication; a file or object-store sink does this with name_by: source_position.
• If persisting the cursor fails, the route logs a warning and keeps running; rows may be reprocessed on restart.

Gotchas

• cursor_column must be monotonic and non-decreasing for new rows (WHERE col > $last ORDER BY col ASC). An autoincrement id or an append-only timestamp works; a mutable updated_at does not give you deletes, and a column that can go backwards loses rows.
• Cursor polling captures appends only. Updates and deletes to already-copied rows are not observed. For those, use CDC (postgres_cdc, MongoDB capture_*).
• Equal-value groups must fit in a batch. If more rows share one cursor_column value than batch_size, the reader refuses to advance rather than skipping the remainder. It reports cursor_column '…' has a group of equal values larger than batch_size and retries that poll indefinitely rather than exiting, so a --drain job hangs instead of failing — watch for the repeating log line. Raise batch_size above the largest group, or pick a more unique column.
• The cursor column must be integer or text, with more types on Postgres. Other types the SQL Any driver can’t decode fail permanently; expose the column as BIGINT/TEXT through a view. On Postgres a timestamp, date, numeric or uuid column works too.
• cursor_column and delete_after_read are mutually exclusive — one is non-destructive, the other consumes.
• Changing cursor_id or checkpoint_store starts over. The position is keyed by both; a new key means a full re-copy. Reuse the same pair to continue a sync, and give unrelated jobs distinct cursor_ids. Changing the table or cursor_column under the same key fails the run instead; see Inspecting and editing a checkpoint.
• capture_all’s initial snapshot is not incrementally checkpointed. What gets persisted is the change-stream resume token, written once streaming begins; a run interrupted during the snapshot re-snapshots from the beginning. Size the sink’s idempotency accordingly.
• ClickHouse and SQL Server: ClickHouse is polling-only and needs an external store; cursor_column mode is not supported on Microsoft SQL Server at all.

See also

• Endpoints (concepts) — read modes and CDC
• PostgreSQL parameters · MongoDB · ClickHouse · Postgres CDC
• CLI commands — copy and checkpoint flags, URI grammar