Skip to main content

Point-in-Time Recovery

Examples on this page use this demo table:

sql
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 <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. It reads the same spellings of a time as CREATE DATABASE ... AT TIMESTAMP (the list), RFC 3339 with microseconds such as 2026-08-02T09:27:00.123456Z among them, and a time with no zone is UTC. A text that is no time, or a time before 1970, is refused before anything is restored, with a message that names --target-time (and, for a text that is no time, the accepted formats).
  • --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:

text
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:

sql
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:

    bash
    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:

    sql
    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:

    bash
    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:

    bash
    scramdb --config /etc/scramdb/restore-config.toml
    bash
    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.

Prepared transactions​

A transaction prepared with PREPARE TRANSACTION comes back as it stood at the target. One prepared at or before the target and not ended by then is listed in pg_prepared_xacts, whether it was prepared before the base backup or after it, and COMMIT PREPARED or ROLLBACK PREPARED ends it. One that COMMIT PREPARED or ROLLBACK PREPARED ended before the target leaves nothing to end, and one prepared after the target is absent and its name is free. A target inside a COMMIT PREPARED leaves the transaction committed once or still prepared, never both. The server logs the files of a prepared transaction only while it archives its log or keeps a backup location, which a restore needs anyway.

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).
  • The archive also has a size bound. [storage.wal.archive] max_size (default a quarter of the data volume) caps the bytes the archive holds on this node's disk. Past it, the oldest segments go first whatever their age, except those a base backup or a branch being staged still needs. When only those are left the archive stays past its bound, one warning is logged and scramdb_wal_archive_over_bound_total counts it: take a newer base backup, or raise the bound, to let them go. An archive in an object store is pruned by age alone.
  • A branch being staged holds the archive for as long as it works. The pin that keeps its segments is renewed while it runs, and released if it stops renewing within [storage.disk] pin_lease (default "30m"), counted in scramdb_wal_pins_lapsed_total. A staging whose pin lapsed fails with the reason instead of finishing on segments that may be gone.

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​

toml
[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
# max_size = "200GB" # the oldest segments no backup or branch needs go first once the archive passes this
# compress = true # store each archived segment compressed; a mixed archive restores whole

[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.

Archived segments are stored compressed by default and read back the same, so a restore or a branch needs nothing extra; scramdb_wal_archive_raw_bytes_total and scramdb_wal_archive_stored_bytes_total give the saving. The archive's size and each database's size are on the node's disk: query scram_disk or read scramdb_disk_bytes{class="archive"} (see Observability).

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.