Skip to content

Point-in-time recovery

Point-in-time recovery (PITR) restores a SQL database container to any second of its recovery window — for example to 30 seconds before a DROP TABLE. It covers PostgreSQL, MySQL and MariaDB. A restore never touches the running database: it builds a new, read-only copy you inspect first, then swap in, keep or discard.

Each container keeps a base backup (a physical copy: pg_basebackup, or XtraBackup / mariadb-backup) plus a continuous log: every PostgreSQL WAL segment (pushed at least every 60 s) and every MySQL / MariaDB binlog (rotated every 60 s). Both are compressed and encrypted like backups and stored with your storage provider. The recovery point objective is about a minute.

The engine writes log files into a spool on the container’s own volume; the agent ships them in batches with presigned upload URLs, so servers still hold no storage credentials. If the control plane or the storage is unreachable, the spool grows on the volume and nothing unshipped is ever deleted.

PITR is on by default for SQL containers created in a production environment when the organization has a storage provider, off elsewhere.

  1. Open Databases, then the database container’s page. Find the Point-in-time recovery card.

  2. Turn on Ship the log continuously and set:

    Setting Default
    Storage provider The organization’s oldest
    Keys Held by Falak (a key per segment, sealed) or Held by you (an age public key; restores ask for the private key)
    Keep (days) 7 — the recovery window, 1 to 35 days
    Base backup every (days) 7
  3. Save. A first base backup starts right away.

Turning PITR off, or shortening the window, leaves less to restore from, so it takes the restore permission (owners and admins). With PITR off, the history is kept until its window has passed.

The card shows the window’s recoverable ranges in green and gaps in red, the age of the last segment shipped (“12 s ago”), what is waiting in the spool, and when the next base backup is due.

A gap appears when a segment never arrived, or when MySQL / MariaDB purged binlogs before they were spooled. Recovery never crosses a gap; Falak records it, alerts, and takes a new base backup at once.

Retention runs hourly: everything the oldest base inside the window needs is kept, and older bases and segments are deleted from storage.

  1. On the Point-in-time recovery card → Restore to a point in time.
  2. Pick the time (UTC) inside a green range, or click Restore to the latest point for everything shipped so far. For keys held by you, paste your age private key; it is used for this restore only, never stored.
  3. Falak creates a new container on a new volume (the source’s image digest, accounts and passwords), fetches the newest base before that time and every log segment after it, verifies each one, and replays to the time.
  4. The copy starts read-only. The card shows its address (published on 127.0.0.1 only) and the row count of every table. Inspect it as falak_inspect, an account that can only SELECT (admins can reveal its password on the card).
  5. Decide.
Decision What happens
Swap The copy becomes writable and takes over the container’s name, DNS name, host port, databases, users, schedules and PITR settings; the services that use it reconnect. The old container is stopped and kept with its volume (PITR off) until you delete it. The copy’s PITR history starts with a base of its own.
Keep as a new database The copy becomes writable, a database of its own in the same environment, on the canvas.
Discard The copy’s container and volume are deleted.

Restoring needs databases.restore (owners and admins).

  • PostgreSQL keeps fractional seconds. If nothing was committed after the chosen time in the shipped WAL, everything there happened before it: Falak recovers to the end of the WAL and says so.
  • MySQL / MariaDB round the time down to the second (binlog timestamps have whole seconds); replay stops before the first event at or after it.
Alert type When Severity
pitr.lag The oldest unshipped segment is older than 5 minutes warning
pitr.gap A gap in the timeline critical
pitr.spool_full The spool is above 20 % of the volume critical
pitr.base_failed A base backup failed critical
pitr.stopped Shipping stopped critical
pitr.recovered Back to normal info

They are part of the default rule pack.