Are you an LLM? Read llms.txt for a summary of the docs, or llms-full.txt for the full context.
Skip to content

Point-in-Time Recovery (PITR)

Restore your database to any second within the retention window.

How PITR Works

PITR combines:

  1. Base backup - Full database snapshot
  2. WAL archives - Transaction log segments

To recover, Postbase:

  1. Restores the base backup
  2. Replays WAL segments up to the target time
  3. Returns the database to the exact state at that moment
Timeline:
────────────────────────────────────────────────────────────────▶
│                                                               │
Base Backup                                              Target Time
(2026-01-25 03:00)                                   (2026-01-25 14:30)
│                                                               │
└─────── WAL Segments (continuous) ─────────────────────────────┘
         000001, 000002, 000003, ... , 000050

Enabling PITR

Via CLI

postbase cloud pitr enable -d production

Output:

Enabling PITR for production...
 
✓ PITR enabled
 
  Base Backup: bkp_D3uqtMIZXOoU_WPo2gbi3_xu
  Retention: 7 days
  WAL Receiver: Starting...
 
The database is now protected with continuous WAL archiving.
Recovery point objective (RPO): < 1 second

With Custom Retention

postbase cloud pitr enable -d production --retention 14

Checking PITR Status

postbase cloud pitr status -d production

Output:

PITR Status for production
 
  Status:      Enabled
  WAL Receiver: running
  WAL Files:   42
 
  Recovery Window:
    From:    2026-01-24 03:00:00
    To:      2026-01-25 14:45:00
    Lag:     15s
    Base:    2026-01-24 03:00:00
 
  View WAL archives: postbase cloud pitr wal -d production

Monitoring WAL Receiver

Receiver Status

postbase cloud pitr receiver -d production

Output:

WAL Receiver Status for production
 
  Status:         ✓ Healthy
  Receiver:       running
  Started:        2026-01-24 03:05:00
  Uptime:         1d 11h 40m
 
  WAL Activity:
    Files:        42
    Data:         672 MB
    Last File:    000000010000000000000042
    Last WAL:     2026-01-25 14:44:45
    Lag:          15s

Restart Receiver

If the receiver has issues:

postbase cloud pitr restart-receiver -d production

Viewing WAL Archives

postbase cloud pitr wal -d production

Output:

WAL Archives for production
 
FILENAME                          SIZE       ARCHIVED AT
000000010000000000000042          16 MB      2026-01-25 14:44:45
000000010000000000000041          16 MB      2026-01-25 14:30:12
000000010000000000000040          16 MB      2026-01-25 14:15:33
...
 
Total: 42 archives, 672 MB

Recovery Window

The recovery window shows the time range available for PITR:

postbase cloud pitr status -d production
Recovery Window:
  From:    2026-01-24 03:00:00  (base backup time)
  To:      2026-01-25 14:45:00  (latest WAL)

You can restore to any point between these times.

Performing PITR Restore

To Specific Time

postbase cloud pitr restore \
  -d production \
  --target-time "2026-01-25T14:30:00Z" \
  --force

To Latest

Restore to the most recent available state:

postbase cloud pitr restore \
  -d production \
  --target-time latest \
  --force

Dry Run

Preview what would be restored:

postbase cloud pitr restore \
  -d production \
  --target-time "2026-01-25T14:30:00Z" \
  --dry-run

Output:

PITR Restore Preview
 
  Database:     production
  Target Time:  2026-01-25T14:30:00Z
 
  Recovery Plan:
    1. Stop current database
    2. Restore base backup: bkp_D3uqtMIZXOoU_WPo2gbi3_xu
    3. Apply WAL segments: 000001 through 000035
    4. Recovery to: 2026-01-25T14:30:00Z
 
  Estimated Time: ~5 minutes
 
  Run without --dry-run to execute.

Checking Restore Progress

List Restores

postbase cloud pitr restores -d production

Output:

PITR Restores for production
 
ID                             TARGET TIME                  STATUS       WAL FILES  DURATION
rstr_abc123...                2026-01-25 14:30:00          completed    35         4m 32s
rstr_def456...                2026-01-24 18:00:00          completed    12         2m 15s

Get Restore Details

postbase cloud pitr restores rstr_abc123 -d production

Disabling PITR

postbase cloud pitr disable -d production --force

Best Practices

Monitor WAL Lag

Keep WAL lag under 5 minutes:

postbase cloud pitr receiver -d production

If lag is high, check database activity and network.

Test Recovery Regularly

Monthly recovery tests:

# Create test database
postbase cloud provision production-pitr-test
 
# Restore to test
postbase cloud pitr restore \
  -d production-pitr-test \
  --source production \
  --target-time "2026-01-25T12:00:00Z"
 
# Verify data
postbase cloud psql -d production-pitr-test \
  -c "SELECT COUNT(*) FROM users"
 
# Cleanup
postbase cloud destroy production-pitr-test

Coordinate with Deployments

Before major changes:

# Note the current time
date -u +"%Y-%m-%dT%H:%M:%SZ"
# 2026-01-25T14:00:00Z
 
# Deploy changes
npm run migrate
 
# If issues, restore to pre-deployment
postbase cloud pitr restore \
  -d production \
  --target-time "2026-01-25T14:00:00Z" \
  --force

Troubleshooting

WAL Receiver Not Running

# Check status
postbase cloud pitr receiver -d production
 
# Restart if needed
postbase cloud pitr restart-receiver -d production

High WAL Lag

Causes:

  • High write volume
  • Network issues
  • Backup service overloaded

Solutions:

  1. Check service status
  2. Review database write patterns
  3. Contact support if persistent

Recovery Window Too Short

If you need longer retention:

# Increase retention
postbase cloud pitr enable -d production --retention 14

Cannot Restore to Specific Time

Error: "Target time outside recovery window"

The requested time is:

  • Before the base backup
  • After the latest WAL segment

Check the recovery window:

postbase cloud pitr status -d production