# Point-in-time recovery

> Restore a PostgreSQL, MySQL or MariaDB container to any second of its window: enable log shipping, restore to a time, then swap, keep or discard the copy.

Source: https://falak.sh/docs/databases/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.

## How it works

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](/docs/databases/backups/#encryption) 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.

## Enable it

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 timeline

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.

## Restore to a time

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

### Precision

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

MariaDB's `read_only` holds back application accounts but not root, and a PostgreSQL session can turn off `default_transaction_read_only` for itself. Inspect the copy as `falak_inspect`, which can only read.

## Alerts

| 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](/docs/observability/alerts/#default-rule-pack).

## Limits

- SQL engines only; Redis and Valkey have [backups](/docs/databases/backups/).
- Times are the server's clock.
- [Preview environments](/docs/guides/preview-environments/) never get PITR.

## Next steps
