Skip to main content

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>, --target-time <TIMESTAMP> or --target-name <NAME> 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 three 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.
  • --target-name <NAME>: use this when you named the moment beforehand with a restore point (below). Replay stops exactly after the point: everything committed before it is restored, nothing after it.

Exactly one of the three is required. Passing more than one, or none, is a CLI error:

restore requires exactly one of --target-lsn, --target-time and --target-name

Naming a restore point​

Before a risky change, name the moment, as a superuser:

SELECT pg_create_restore_point('before-migration');

Expect: one row with the point's WAL position in pg_lsn form, for example 0/16B3748. SELECT scram.create_restore_point('before-migration') makes the same point and answers the position as a number. The point is a record in the WAL, written durably before the statement answers and archived with its WAL segment, so a restore can find it once that segment is archived (or from the local WAL directory given as --wal-source). A name is at most 63 bytes. You may make a name again: a restore to it stops at the first point of that name made after the backup it restores from, as in PostgreSQL. A name the replayed WAL does not hold fails the restore with the WAL after the base backup holds no restore point named "<NAME>".

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.

Schema changes after the backup​

Replay brings back the schema as it stood at your target, not as it stood when the backup was taken. Changes made after the backup are replayed in the order they were made, alongside the rows:

  • A table created after the backup comes back with its primary key, unique and check constraints, NOT NULL columns, defaults, identity columns and comment.
  • An index created after the backup is listed in pg_indexes, and a primary key or unique constraint made after it refuses duplicates; an index dropped after the backup is gone.
  • A column added after the backup is there, and the rows written before it read its default.
  • A view created after the backup answers.
  • Every sequence, an identity column's included, resumes past every value the restored rows hold. As in PostgreSQL after a crash, the next value may skip ahead of the last one used.

A target before a change restores the schema without it: restoring to before a CREATE TABLE leaves the table out, and restoring to before an ALTER TABLE ... ADD COLUMN leaves the column out.

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.
  • The archive deletes old segments by default. [storage.wal.archive] retention (default "168h", seven days) removes an archived segment once it is older than that AND lies before the base backup's WAL position at [storage.wal.backup] destination; with no backup there, age alone decides. The newest segment always stays. To keep more history, set a longer duration, for example retention = "720h" for 30 days (durations take hours, not days).

Timelines​

Every restore starts a new timeline, as in PostgreSQL. The restored server writes its WAL on a timeline one past the highest the WAL source or the backup has used, and keeps a small history file beside its WAL naming the timelines it descends from and the WAL position where each one began. Archived WAL file names carry their timeline, and the archiver ships the history file with the segments, so the old server and the restored one can archive to the same destination without overwriting each other.

  • A restore replays the backup's own timeline. A backup taken after an earlier restore carries its timeline's history in its manifest, so a later restore from it needs nothing else.
  • The WAL source is only read. A restore never writes into its archive, so a read-only archive can be restored from.
  • Two restores from one archive pick the same timeline number. If both archive back into that destination, the second one finds a different history already stored under its number and archives nothing, logging why and raising scramdb_wal_archive_errors_total. Give each restored server an archive destination of its own.
  • A branch at a past time follows the history. On a restored server, CREATE DATABASE ... AT TIMESTAMP (see Branching) may start from a base backup taken before the restore: the branch replays the old timeline up to the point where the new one began, then the new one. A base backup from a history the server does not descend from is refused with the reason.

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"
# retention = "168h"    # archived segments older than this, and before the backup's WAL position, are removed

[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.
  • Cluster restore: the same for a whole cluster, every node and shard group to one consistent point.
  • 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.