Skip to main content

​
Overview

Point-in-Time Recovery (PITR) lets you restore a PostgreSQL database to a specific moment in time instead of restoring only to the timestamp of a snapshot backup. On Aptible, PITR uses PostgreSQL write-ahead log (WAL) archives together with snapshot backups in two stages:
  1. Aptible restores a new database from the latest qualifying backup before your target time.
  2. Aptible then replays archived WAL data up to the time you selected.
PITR is available for PostgreSQL 13 and newer.
PITR creates a new database. It does not overwrite or roll back the source database in place.

​
How PITR Relates to Backup Retention

PITR depends on the same Environment-level backup retention policy used for automatic backups. To use PITR:
  • The database must have backups enabled
  • PITR retention must already be enabled for the Environment
  • The database must already have retained WAL archive history for the time you want to recover to
  • The database must be in a PITR-capable state. See PITR States and Required Actions below.
Enabling PITR retention does not by itself make every earlier point in time recoverable. A PITR restore can only succeed for times covered by the database’s available WAL archive history. PITR retention is not self-service configurable today. If you need longer retention, contact Aptible Support.
In the current Dashboard UI, PITR retention appears in the Environment backup retention policy.
For the retention policy settings themselves, see Database Backups.

​
PITR States and Required Actions

If PITR is not enabled for your database, it will be in one of the states below. In these states, one or more prerequisites for performing PITR are not satisfied.
StateWhat it meansWhat to do
Backups disabledPITR cannot run because database backups are disabled.Re-enable backups for the database first.
Pending restartThe database supports PITR, but it must be restarted before PITR can finish initializing.Restart the database, then PITR will initialize automatically.
RestrictedPITR is unavailable for this database. In the current product, this state is used for special cases such as extremely high write volume.Contact Aptible Support.
Unsupported versionThe database version does not support PITR.Upgrade to PostgreSQL 13 or newer if PITR is required.
New PostgreSQL databases created through the Dashboard are initialized for PITR automatically when the Environment is already configured to retain PITR data.

​
Restoring with PITR

In the Aptible Dashboard:
  1. Navigate to the Environment that contains the source database.
  2. Select the database you want to recover.
  3. Select the Recovery tab.
  4. Enter a name for the new database.
  5. Choose the Target Time (UTC). For guidance on selecting a target time, see Choosing a Recovery Time below.
  6. Select Start Recovery.
The restore form creates a new database from the latest relevant backup and applies archived WAL data during provisioning until the selected time is reached.
Creating a Point-in-Time Restore

​
Choosing a Recovery Time

When choosing a target time:
  • You can recover only to times on or after the database’s oldest available WAL archive time.
  • In practice, choose a timestamp before the transaction or change you want to avoid restoring.
  • WAL archiving happens asynchronously. Under normal conditions, WAL is available for PITR within about 5 minutes of a committed transaction.
  • If you need to target a time within the last 5 minutes, the recovery may not succeed.
If the time you want is earlier than the oldest WAL archive still retained, PITR cannot recover to that moment.

​
What to Expect During Recovery

PITR runs in two stages behind the scenes:
  1. A new database is created from a qualifying snapshot backup.
  2. Archived WAL data is replayed during provisioning until the requested recovery time is reached.
Recovery duration depends on how much WAL data must be replayed. For databases with large WAL volume, this can take a long time and may take hours.

​
Troubleshooting Insufficient WAL

If Aptible cannot replay enough WAL data to reach the requested time, the recovered database can fail to start. One log message you may see is: 
last completed transaction was at log time 2026-04-01 13:03:26.023332+00
This commonly means there were no transactions after the requested recovery time, so there was nothing to exclude during replay and the timestamp needs to be adjusted. If this happens:
  1. Do not retry the recovery yourself through the UI.
  2. Leave the failed recovered database in place.
  3. Contact Aptible Support.
Support can retry the provisioning step with an adjusted timestamp without taking a new snapshot, which is faster and avoids repeating the initial restore stage unnecessarily.