Point-in-time recovery: what it actually means for your data

Point-in-time recovery is one of those features that sounds better than it often is. Many managed database providers advertise PITR but deliver something more like "daily snapshots plus some WAL." That is useful, but it is not the same as true PITR, the ability to restore your database to any second within the retention window, not just the nearest snapshot boundary.

We built PITR into every StackBlaze Postgres instance from day one. This post explains exactly how it works, what the guarantees are, and where the limits are.

What PITR actually is

PostgreSQL's write-ahead log is a sequential record of every change made to the database. Every INSERT, UPDATE, DELETE, and DDL statement produces WAL records before the change is applied to the data files. Because the WAL is sequential and complete, you can reproduce the exact state of a database at any point in time by starting from a base backup and replaying WAL up to a target LSN (log sequence number) or timestamp.

The practical implication: if someone runs DROP TABLE users at 14:32:07, you can restore to 14:32:06 and lose only the 1 second of subsequent writes, not the entire day. This is materially different from restoring a daily snapshot, which would lose up to 24 hours of data.

WAL archiving

PostgreSQL ships WAL in 16 MB segments. When a segment is complete, the archive_command fires and uploads it to durable storage. Our implementation uses a daemon we call walg-sidecar that runs alongside each Postgres pod and streams WAL segments to object storage as they are completed. We use WAL-G under the hood, it compresses and encrypts each segment before upload and has retry logic for transient object storage failures.

-- These GUCs are set by StackBlaze automatically; shown here for transparency
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'walg-sidecar archive %p';
ALTER SYSTEM SET archive_timeout = '60';   -- flush segment after 60s even if not full

-- Confirm archiving is active
SELECT pg_walfile_name(pg_current_wal_lsn()),
       last_archived_wal,
       last_archived_time,
       last_failed_wal
FROM pg_stat_archiver;

The archive_timeout of 60 seconds is important. Without it, a low-traffic database might not fill a WAL segment for hours, which would make your effective RPO equal to the time since the last full segment, not the 60-second boundary we advertise. With archive_timeout, even a quiet database ships WAL at least once per minute.

How we ship WAL to object storage

Each Postgres pod has a walg-sidecar container that shares a volume mount with the Postgres container. When Postgres calls archive_command, walg-sidecar intercepts the call, compresses the segment with LZ4, encrypts it with AES-256-CTR using a per-database key from our KMS, and uploads it to S3-compatible object storage in the same region as the database.

We also take a full base backup once every 24 hours using WAL-G's backup-push command. A full restore requires the most recent base backup plus all WAL segments since that backup. On a database with moderate write volume, the WAL since the last base backup is typically 2-10 GB, small enough to stream quickly at restore time.

Recovering to a timestamp

When you trigger a PITR restore from the StackBlaze dashboard, you pick a target timestamp and a target environment. We provision a new Postgres instance, restore the most recent base backup taken before your target, and replay WAL up to the exact LSN that corresponds to your timestamp. The restore runs in a separate namespace so your original database continues serving traffic.

#!/usr/bin/env bash
# StackBlaze PITR restore, internal procedure (simplified)
set -euo pipefail

TARGET_TIME="${1:-}"   # ISO 8601, e.g. 2026-03-28T14:32:06Z
DB_ID="${2:-}"
RESTORE_ENV="${3:-}"

if [[ -z "$TARGET_TIME" || -z "$DB_ID" || -z "$RESTORE_ENV" ]]; then
  echo "Usage: $0 <target-time> <db-id> <restore-env>"
  exit 1
fi

# Find the most recent base backup before target
BACKUP_NAME=$(walg-sidecar backup-list --json | \
  jq -r --arg t "$TARGET_TIME" \
  '[.[] | select(.start_time < $t)] | sort_by(.start_time) | last | .backup_name')

echo "Restoring from base backup: $BACKUP_NAME"

# Restore base backup to new PVC
walg-sidecar backup-fetch "$PGDATA" "$BACKUP_NAME"

# Write recovery config
cat > "$PGDATA/postgresql.auto.conf" <<'HEREDOC'
restore_command = 'walg-sidecar wal-fetch %f %p'
recovery_target_time = 'TARGET_PLACEHOLDER'
recovery_target_action = 'promote'
HEREDOC

touch "$PGDATA/recovery.signal"
echo "Recovery configured, starting Postgres"

Retention windows by plan

The RPO (recovery point objective) is 60 seconds on all plans because archive_timeout is 60 seconds on all plans. The retention window determines how far back in time you can recover, not how much data you lose in a failure scenario.

Limitations

• PITR cannot recover past a major version upgrade boundary. If you upgraded from Postgres 15 to 16, WAL from before the upgrade is not replayable on the new major version.
• Logical replication slots are not preserved across a PITR restore. If you use logical replication for CDC, you will need to resync your downstream consumers after a restore.
• The restore process creates a new database instance. There is no in-place rewind, your original instance keeps running until you manually cut traffic over.
• Very large databases (>5 TB) may have restore times measured in hours rather than minutes, because we have to replay a significant amount of WAL. We are working on incremental restore support for this case.

PITR is one of the most important reliability features in any production database setup. We think every database, not just enterprise tiers, should have it by default. That's why we ship it on every plan, including Hobby.