Point-in-Time Recovery
Restore a PostgreSQL database to any specific second within the retention window.
Point-in-time recovery (PITR) lets you restore your database to any specific second within the 30-day backup retention window. This is useful for recovering from accidental data deletion or corruption.
How it works
SMLL continuously archives PostgreSQL Write-Ahead Log (WAL) files. Combined with daily base backups, this allows recovery to any point between the oldest backup and the current time.
- The system identifies the most recent base backup before your target time
- The base backup is restored
- WAL files are replayed up to your target timestamp
- The database comes online at the exact state of the target time
Using PITR
- Navigate to your database's Backups tab
- Click Point-in-Time Recovery
- Select the target date and time (UTC)
- Confirm the restore
The database will be unavailable during recovery. Duration depends on the amount of WAL to replay.
Considerations
- PITR restores the entire database cluster, not individual tables
- The target time must be within the 30-day retention window
- WAL archiving must be active (enabled by default on all databases)
- Recovery to a very recent time (last few seconds) may not include the most recent transactions
When to use PITR vs. backup restore
| Scenario | Use |
|---|---|
Recover from accidental DELETE or DROP TABLE at a known time | PITR |
| Restore to a known-good daily snapshot | Backup restore |
| Investigate data at a specific historical point | PITR |
| Fastest possible recovery | Backup restore |