Automate Point-in-Time Recovery for Postgres on AWS RDS

What you'll build

A production RDS for PostgreSQL instance with 35-day point-in-time recovery locked in, plus a rehearsed, scripted restore that brings up a copy of the database as it existed at any second you name. You'll prove it by dropping a table and getting it back.

Prerequisites

• An existing RDS for PostgreSQL instance (not Aurora; Aurora uses restore-db-cluster-to-point-in-time instead). Verified against RDS for PostgreSQL 17.11 and 18.6.
• AWS CLI v2, verified on 2.36.x. Check with aws --version.
• psql 17 or newer on a host that can reach the instance's VPC (a bastion, or your laptop if the instance is publicly accessible).
• IAM permissions for rds:ModifyDBInstance, rds:DescribeDBInstances, rds:DescribeDBInstanceAutomatedBackups, rds:RestoreDBInstanceToPointInTime, and rds:DeleteDBInstance.
• macOS or Linux shell. The date -u calls below work on both BSD and GNU date.

Set these once and reuse them in every step:

export SRC=prod-pg            # your instance identifier
export PGUSER=postgres        # master user
export PGDATABASE=app
export PGPASSWORD='...'       # or use ~/.pgpass

Step 1: Lock in retention and the backup window

PITR only works while automated backups are on. RDS keeps a daily snapshot plus transaction logs it uploads to S3 every five minutes, and it can replay those logs to any second inside the retention period. Set retention to the 35-day maximum and pin the backup window to a quiet hour in UTC:

aws rds modify-db-instance \
  --db-instance-identifier "$SRC" \
  --backup-retention-period 35 \
  --preferred-backup-window 03:00-03:30 \
  --copy-tags-to-snapshot \
  --deletion-protection \
  --apply-immediately

The window must be at least 30 minutes, in hh24:mi-hh24:mi UTC, and must not overlap the weekly maintenance window. --copy-tags-to-snapshot keeps cost-allocation tags on the backups. --deletion-protection stops someone from deleting the instance (and with it, every automated backup) by accident.

One warning: if retention was 0, changing it to a nonzero value takes the instance offline briefly while RDS takes the first backup. Moving between two nonzero values applies with no outage.

Step 2: Find the restore window

Two timestamps bound what you can restore to. The earliest comes from the oldest surviving automated backup; the latest trails real time by about five minutes, because that's how often logs ship.

aws rds describe-db-instance-automated-backups \
  --db-instance-identifier "$SRC" \
  --query 'DBInstanceAutomatedBackups[0].[Status,BackupRetentionPeriod,RestoreWindow.EarliestTime,RestoreWindow.LatestTime]' \
  --output text

active  35  2026-07-26T03:07:11+00:00  2026-08-30T13:52:00+00:00

Status must be active. If it's creating, the first snapshot hasn't finished; wait for it before trying a restore.

Step 3: Stage a "bad deploy" with a timestamped marker

Create a table, record the exact UTC second after it lands, then destroy it. That timestamp is your restore target.

psql -h "$(aws rds describe-db-instances --db-instance-identifier "$SRC" \
  --query 'DBInstances[0].Endpoint.Address' --output text)" \
  -c "CREATE TABLE pitr_marker (id int, note text);" \
  -c "INSERT INTO pitr_marker VALUES (1, 'restore me');"

sleep 5
export RESTORE_TIME="$(date -u +%Y-%m-%dT%H:%M:%SZ)"
echo "$RESTORE_TIME"

sleep 5
psql -h "$(aws rds describe-db-instances --db-instance-identifier "$SRC" \
  --query 'DBInstances[0].Endpoint.Address' --output text)" \
  -c "DROP TABLE pitr_marker;"

Step 4: Restore to the second before it happened

A PITR restore never touches the source. It creates a new instance, and by default that new instance lands in the default subnet group, default security group, and default parameter group. Pull the real ones from the source so the restored copy is reachable and configured the same way:

export SUBNET_GROUP=$(aws rds describe-db-instances --db-instance-identifier "$SRC" \
  --query 'DBInstances[0].DBSubnetGroup.DBSubnetGroupName' --output text)
export SG_IDS=$(aws rds describe-db-instances --db-instance-identifier "$SRC" \
  --query 'DBInstances[0].VpcSecurityGroups[].VpcSecurityGroupId' --output text)
export PARAM_GROUP=$(aws rds describe-db-instances --db-instance-identifier "$SRC" \
  --query 'DBInstances[0].DBParameterGroups[0].DBParameterGroupName' --output text)

--restore-time must be earlier than LatestRestorableTime, so wait until the log shipper has caught up past your marker:

until [ "$(aws rds describe-db-instances --db-instance-identifier "$SRC" \
  --query 'DBInstances[0].LatestRestorableTime' --output text | cut -c1-19)" \
  \> "$(echo "$RESTORE_TIME" | cut -c1-19)" ]; do
  echo "waiting for LatestRestorableTime to pass $RESTORE_TIME"; sleep 30
done

aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier "$SRC" \
  --target-db-instance-identifier "$SRC-pitr-drill" \
  --restore-time "$RESTORE_TIME" \
  --db-subnet-group-name "$SUBNET_GROUP" \
  --vpc-security-group-ids $SG_IDS \
  --db-parameter-group-name "$PARAM_GROUP" \
  --no-multi-az \
  --query 'DBInstance.[DBInstanceIdentifier,DBInstanceStatus]' --output text

aws rds wait db-instance-available --db-instance-identifier "$SRC-pitr-drill"

Swap --restore-time for --use-latest-restorable-time when you want the newest possible copy (the two flags are mutually exclusive). The waiter polls every 30 seconds and gives up after 60 tries; small instances finish well inside that, large ones may not (see Troubleshooting).

Step 5: Turn it into a runbook script

During a real incident nobody should be composing flags from memory. Save this as pitr-restore.sh, commit it next to your infra code, and run the drill quarterly.

#!/usr/bin/env bash
# Usage: ./pitr-restore.sh <source-id> <target-id> <UTC time, e.g. 2026-08-30T13:41:07Z | latest>
set -euo pipefail
SRC=$1; TARGET=$2; WHEN=$3

desc() { aws rds describe-db-instances --db-instance-identifier "$SRC" --query "DBInstances[0].$1" --output text; }

if [ "$WHEN" = latest ]; then TIME_ARGS=(--use-latest-restorable-time)
else TIME_ARGS=(--restore-time "$WHEN"); fi

echo "Restorable through: $(desc LatestRestorableTime)"
aws rds restore-db-instance-to-point-in-time \
  --source-db-instance-identifier "$SRC" \
  --target-db-instance-identifier "$TARGET" \
  "${TIME_ARGS[@]}" \
  --db-subnet-group-name "$(desc DBSubnetGroup.DBSubnetGroupName)" \
  --vpc-security-group-ids $(desc 'VpcSecurityGroups[].VpcSecurityGroupId') \
  --db-parameter-group-name "$(desc 'DBParameterGroups[0].DBParameterGroupName')" \
  --output off

aws rds wait db-instance-available --db-instance-identifier "$TARGET"
aws rds describe-db-instances --db-instance-identifier "$TARGET" \
  --query 'DBInstances[0].[DBInstanceStatus,Endpoint.Address]' --output text

chmod +x pitr-restore.sh
./pitr-restore.sh prod-pg prod-pg-pitr-2 latest

Verify it works

Connect to the restored instance and look for the table you dropped:

export DRILL_HOST=$(aws rds describe-db-instances --db-instance-identifier "$SRC-pitr-drill" \
  --query 'DBInstances[0].Endpoint.Address' --output text)
psql -h "$DRILL_HOST" -c "SELECT * FROM pitr_marker;"

 id |    note
----+------------
  1 | restore me
(1 row)

The same query against the source still fails with ERROR: relation "pitr_marker" does not exist. That's the whole point: the source is untouched, and the copy is frozen at the second you chose. The restored volume keeps lazy-loading blocks from S3 for a while after available; StorageOperationStatus in describe-db-instances shows Initializing until that finishes, and performance is reduced until then.

Once you've confirmed the data, delete the drill instance so it stops billing:

aws rds delete-db-instance --db-instance-identifier "$SRC-pitr-drill" \
  --skip-final-snapshot --delete-automated-backups --output off

Troubleshooting

An error occurred (PointInTimeRestoreNotEnabled) when calling the RestoreDBInstanceToPointInTime operation The source has BackupRetentionPeriod of 0. Run Step 1, wait for the automated backup Status to turn active, then retry. There's no way to restore to a time before backups were on.

An error occurred (DBInstanceAlreadyExists) when calling the RestoreDBInstanceToPointInTime operation: DB instance already exists The target identifier is taken, usually by last quarter's drill. Pick a new name or delete the old one. If you're replacing production with the restored copy, rename the old instance first with aws rds modify-db-instance --db-instance-identifier prod-pg --new-db-instance-identifier prod-pg-old --apply-immediately, then restore into prod-pg. Renaming reboots the instance and swaps its DNS endpoint.

Waiter DBInstanceAvailable failed: Max attempts exceeded The restore took longer than the waiter's 30-minute ceiling. The instance is still being created; nothing failed on the RDS side. Rerun aws rds wait db-instance-available or poll DBInstanceStatus yourself. Multi-terabyte restores can take hours.

psql: error: connection to server at "prod-pg-pitr-drill....rds.amazonaws.com" (10.0.2.14), port 5432 failed: Connection timed out You restored without --vpc-security-group-ids, so the copy got the VPC's default security group, which usually allows nothing inbound. Fix it in place: aws rds modify-db-instance --db-instance-identifier prod-pg-pitr-drill --vpc-security-group-ids sg-0123456789abcdef0.

Next steps

• Cross-region PITR: aws rds start-db-instance-automated-backups-replication --region us-west-2 --source-db-instance-arn arn:aws:rds:us-east-1:123456789012:db:prod-pg --backup-retention-period 14 copies automated backups to a second region. You can then restore there with --source-db-instance-automated-backups-arn even if the primary region is down.
• Retained backups: when you delete an instance, choose to retain automated backups. They stay restorable for the rest of the retention period and show up with Status: retained.
• Wire the runbook into an on-call playbook and put RESTORE_TIME selection in it: the right target is the last second before the bad write, which you get from application logs or pg_stat_statements, not from a guess.
• Read the RDS PITR docs for engine-specific limits and the restore-db-instance-to-point-in-time reference for storage, IOPS, and encryption options you may need to carry over.