Point-in-Time Recovery

Examples on this page use this demo table:

CREATE TABLE orders (id BIGINT PRIMARY KEY, status TEXT);

By the end of this page you will have restored a ScramDB instance to an exact moment in the past, chosen either by timestamp or by LSN, and confirmed you landed exactly where you meant to.

Point-in-time recovery (PITR) is not a separate tool from restore, it is scramdb restore called with --target-lsn <LSN> or --target-time <TIMESTAMP> instead of replaying every WAL segment available. Same command, same manifest, same WAL archive, one extra flag that tells replay where to stop.

Choosing a target

You have two ways to say where you want to land:

• --target-time <RFC3339 timestamp>: use this when you know roughly when things went wrong, for example "restore to five minutes before the bad DELETE ran." This is the more common case: pull the timestamp from an incident timeline, an application log, or a monitoring alert. Accepts microsecond precision, e.g. 2026-08-02T09:27:00Z or 2026-08-02T09:27:00.123456Z.
• --target-lsn <LSN>: use this when you have an exact WAL position, for example from a log line ScramDB itself printed (backup and restore both log LSNs), or from monitoring that tracks replication/WAL position directly.

Exactly one of the two is required. Passing both, or neither, is a CLI error:

specify exactly one of --target-lsn or --target-time, not both

restore requires exactly one of --target-lsn or --target-time

Worked example: restore to just before a bad change

This walks through taking a backup, making more changes, then restoring to the moment right before a change you want to undo.

1. Take a backup:

   scramdb backup --config /etc/scramdb/config.toml --out file:///var/lib/scramdb/backups/pitr-demo

   Expect: backup complete: N file(s), base_lsn=<LSN>, base_wal_file_id=<ID>, destination='file:///var/lib/scramdb/backups/pitr-demo'

2. Make more changes, including one you will want to recover from. For example:

   INSERT INTO orders (id, status) VALUES (101, 'pending');
   -- record the time here, e.g. 2026-08-02T09:30:00Z
   DELETE FROM orders WHERE id = 101;  -- the mistake

3. Note the timestamp right before the mistake. In this example, 2026-08-02T09:30:00Z, captured right after the insert and before the delete.

4. Restore to that timestamp, into a fresh directory:

   scramdb restore --config /etc/scramdb/restore-config.toml \
     --backup file:///var/lib/scramdb/backups/pitr-demo \
     --target-time 2026-08-02T09:30:00Z

   Expect: restore complete: replayed to lsn=<LSN>, target config=/etc/scramdb/restore-config.toml

5. Start the restored instance and verify:

   scramdb --config /etc/scramdb/restore-config.toml

   psql "host=127.0.0.1 port=5432 user=scramdb dbname=scramdb" -c "SELECT * FROM orders WHERE id = 101;"

   Expect the row to be present, with status = 'pending', since the insert happened before your target time and the delete happened after it. If you had instead picked a target time after the delete, the row would be absent. Checking both a row that existed at your target moment and confirming a later change is absent is the strongest verification you can do: it proves you landed on the correct side of the boundary, not just somewhere in the neighborhood.

Verifying you landed where you meant

The restore command's own output is your first checkpoint: the lsn=<LSN> it logs is the exact WAL position replay stopped at. Cross-check that against what you expected (for example, a WAL position your monitoring reported near the target time).

Beyond that, query for state you know should differ on either side of your target: a row that existed before but not after, a column value that changed at a known time, a count that should match a known figure as of that moment. A restore that "starts cleanly" but has the wrong data at the boundary is a failed PITR, so verify against the boundary itself, not just server health.

What governs how far back you can go

PITR range is bounded by what WAL is actually retrievable, it is not unlimited in either direction:

• You cannot target a point before your backup's own base LSN. Replay only moves forward from the backup; a target before the backup was taken needs an older backup instead.
• How far forward you can go depends on retained WAL. Two settings control this, under [storage.wal.retention]:
  • min_kept (default 1): always keep at least this many closed WAL files locally, regardless of anything else.
  • archive_aware (default true): once a WAL archiver has started, a local WAL file is not eligible for pruning until the archiver's own watermark has passed it. Do not set this to false if you rely on PITR via the archive, doing so lets local WAL get pruned ahead of what the archive has actually captured.
• Whether archiving is enabled at all determines whether you have durable WAL history beyond what happens to still be sitting in the local wal/ directory.

Configuring retention and archiving

[storage.wal.archive]
enabled = true
# destination unset -> derives to "{data_dir}/wal-archive" (a local subdirectory of the data volume).
# destination = "file:///var/lib/scramdb/wal-archive"
# poll_interval = "5s"

[storage.wal.retention]
min_kept = 1
archive_aware = true

The shipped Docker image sets [storage.wal.archive].enabled = true by default, specifically so PITR and branching work without extra setup. If destination is left unset while archiving is enabled, WAL archives to {data_dir}/wal-archive, inside the same data volume as everything else; point it elsewhere with an explicit destination (same URL forms as backup: local path, file://, s3://, gs://, az://) if you want the archive on separate storage.

There is no environment-variable form of any of these keys, they are TOML-only, set under [storage.wal.*] in your config file.

Next

• Restore: the underlying command and its full failure-mode reference.
• Branching: fork a database as of a past moment on a live server, using this same backup-plus-WAL machinery, without a separate restore step.