PostgreSQL Point-in-Time Recovery for Django

Problem statement

A nightly pg_dump protects against losing the whole database, but not against the most common real incident: a bad deploy, a buggy data migration, or a mistaken DELETE at 14:20 that is noticed at 16:00. Restoring last night's dump loses everything written since midnight. Restoring nothing leaves the damage.

Point-in-time recovery (PITR) combines periodic physical base backups with a continuous archive of PostgreSQL's write-ahead log (WAL). You can restore the database to any moment covered by the archive, for example 14:19:30, one second before the mistake. This guide sets it up with pgBackRest on a self-managed server, restores to a timestamp, and covers the equivalent on managed services.

1. Install pgBackRest, configure a repository (local disk for testing, object storage for production) with encryption and retention.
2. Set archive_mode = on and archive_command = 'pgbackrest --stanza=main archive-push %p', then restart PostgreSQL.
3. Create the stanza, run check, and take a full backup; schedule full and differential backups.
4. To recover, stop PostgreSQL (or restore on a separate server), run pgbackrest restore --type=time --target=..., start PostgreSQL, and verify.
5. On managed services (RDS, Cloud SQL, Azure), PITR is built in: restore to a timestamp into a new instance and repoint Django.
6. Practise restores regularly and record how long they take.

Step-by-step solution

1. Install and configure pgBackRest

sudo apt install pgbackrest

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=myapp-pg-backups
repo1-s3-endpoint=s3.eu-central-1.amazonaws.com
repo1-s3-region=eu-central-1
repo1-path=/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=<long random passphrase>
process-max=2
start-fast=y
log-level-console=info

[main]
pg1-path=/var/lib/postgresql/16/main

S3 credentials go in repo1-s3-key and repo1-s3-key-secret, or come from an instance role (repo1-s3-key-type=auto on AWS). The file contains secrets: make it readable only by the postgres user. Store the cipher passphrase somewhere independent of the server, or the backups are unreadable after a total loss.

repo1-retention-full=2 keeps two full backups and all WAL needed to restore to any point since the older one.

2. Enable WAL archiving

# /etc/postgresql/16/main/postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica
max_wal_senders = 3

sudo systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check

check forces a WAL switch and confirms the segment reached the repository. If it fails, PostgreSQL keeps unarchived WAL locally, which can fill the disk; monitor archiving (step 6).

3. Take and schedule backups

sudo -u postgres pgbackrest --stanza=main --type=full backup
sudo -u postgres pgbackrest --stanza=main info

Schedule them, for example weekly full and daily differential:

# crontab for postgres
30 1 * * 0   pgbackrest --stanza=main --type=full backup
30 1 * * 1-6 pgbackrest --stanza=main --type=diff backup

Your recovery window starts at the oldest retained full backup and extends to the most recently archived WAL segment, usually seconds before now.

4. Restore to a point in time

Suppose a data migration deployed at 14:20 UTC corrupted data. First, find the target: the deploy log or django_migrations.applied shows when the migration ran.

SELECT app, name, applied FROM django_migrations ORDER BY applied DESC LIMIT 5;

Choose a time shortly before it. The safest approach restores onto a separate server (or the same server into a different data directory) so production stays available for comparison:

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
  --type=time "--target=2026-09-25 14:19:30+00" \
  --target-action=promote restore
sudo systemctl start postgresql

• --delta reuses matching files in the data directory, making in-place restores faster.
• --type=time --target=... stops WAL replay at that timestamp. Always include the time zone.
• --target-action=promote opens the database for writes after recovery. Use pause instead to inspect the state read-only before deciding.

Watch the PostgreSQL log for recovery stopping before commit of transaction ... and database system is ready to accept connections.

5. Reconnect Django and verify

After restoring to a new server, point Django's DATABASES at it (or swap DNS or a connection string), then:

python manage.py showmigrations | tail -5
python manage.py check --database default

The migrations list should end before the bad migration. Deploy code that matches that schema (the previous release), or the app will expect columns that do not exist.

Data written after the target time is not in the restored database. If it matters (orders, payments), export it from the damaged database before discarding it and reconcile manually.

Media files are not part of database recovery. Uploads made after the target time still exist in storage but their database rows do not; uploads deleted during the incident need media backups.

6. Monitor archiving and backups

SELECT archived_count, failed_count, last_archived_time, last_failed_time, last_failed_wal
FROM pg_stat_archiver;

Alert when last_failed_time is recent or last_archived_time is older than a few minutes on a busy database, and when the latest backup is older than expected (pgbackrest info --output=json shows backup timestamps).

Verification check:

Restore regularly (monthly is common) to a scratch server at a random recent timestamp, start Django against it with the matching release, and run a smoke test. Record the time from start to a working app: that is your real recovery time.

Explanation

PostgreSQL writes every change to the WAL before applying it to data files. A physical base backup captures the data files at one moment; replaying archived WAL on top of it rolls the database forward, transaction by transaction. Recovery can stop at any recorded point, which is what makes PITR possible. pgBackRest manages the pieces (compression, encryption, parallel transfer, retention, and verification) so backups are complete and restorable.

Edge cases / notes

Managed PostgreSQL

Amazon RDS, Cloud SQL, Azure Database for PostgreSQL, and most managed services provide PITR through automated backups: choose a timestamp in the console or CLI, and the service creates a new instance restored to that time. Check the configured retention period, and practise the switch-over, since Django must be repointed to the new endpoint.

Logical mistakes versus corruption

PITR fixes "the data is wrong since time T". Hardware failure or data file corruption on the primary is covered too, by restoring the latest point. Neither replaces replicas for high availability, which minimise downtime rather than data loss.

When to automate this

Automate the restore test end to end: create a server, restore to a timestamp, run Django checks and a smoke test against it, record the duration, and destroy the server. An untested backup is an assumption about your recovery ability.

Next steps

• Keep logical dumps as a second layer with Backup and Restore PostgreSQL for Django Apps.
• Put PITR into a broader plan with Write a Disaster Recovery Plan for a Django App.
• Review coverage with the Django Backup and Recovery Checklist.
• For migrations that fail rather than corrupt, see Django Migration Failed During Deploy: Recovery Playbook.

FAQ

What is point-in-time recovery in PostgreSQL?

Restoring a base backup and replaying archived WAL up to a chosen timestamp, so the database reflects its state at that moment.

Is pg_dump enough for Django backups?

It protects against total loss but only to the time of the dump. PITR lets you recover to just before a mistake, losing seconds instead of hours.

How do I undo a bad Django data migration with PITR?

Restore to a timestamp just before the migration, deploy the previous release that matches that schema, and reconcile any data written afterwards.

Does PITR restore uploaded media files?

No. It covers the database only. Back up media storage separately.