Postgres Backup Best Practices for App Teams

A good Postgres backup setup has three layers: continuous WAL archiving for point-in-time recovery, regular full backups, and an independent logical copy (a pg_dump) stored somewhere your main provider cannot touch. On top of that, you test restores on a schedule. Most data loss incidents are not caused by a lack of backups, but by backups that were never restored, did not cover the thing that broke, or lived next to the data they were protecting.

This guide is a practical checklist for app teams, whether you run Postgres yourself or use a managed host.

#Know what you are protecting against

Different failures need different backups:

If your plan does not address every row of this table, you have a gap.

#Postgres backup types: logical versus physical

Logical backups (pg_dump, pg_dumpall) export SQL definitions and data. They are portable across Postgres versions and platforms, you can restore a single table, and they are human-inspectable. They are slow for large databases, take a snapshot of one moment only, and restore time includes rebuilding indexes.

Physical backups (pg_basebackup, pgBackRest, WAL-G, Barman) copy the data files. Combined with WAL archiving they give point-in-time recovery. They are fast to restore at scale, but tied to the same major version and architecture.

You want both: physical plus WAL for fast recovery, logical for portability and independence.

#pg_dump flags that matter

pg_dump \
  --format=custom \
  --no-owner --no-privileges \
  --file=app-$(date -u +%Y%m%dT%H%M%SZ).dump \
  "postgresql://user:pass@host:5432/db?sslmode=require"

• --format=custom (or -Fc) is compressed and lets pg_restore pick objects and run in parallel. Use --format=directory with --jobs to dump large databases in parallel.
• --no-owner --no-privileges makes the dump restorable on a server with different roles. Leave them off if you restore to an identical setup and want grants preserved.
• --schema / --table narrow the dump. --exclude-table-data keeps the definition of huge, rebuildable tables (logs, caches) but skips their rows.
• Run pg_dump with a client version at least as new as the server.

pg_dump takes a consistent snapshot using a single transaction, so it does not block normal reads and writes. It does hold locks that conflict with schema changes, so do not schedule it during migrations.

Restore:

createdb restore_test
pg_restore --no-owner --no-privileges --jobs 4 -d restore_test app-20261102T020000Z.dump

pg_dump does not include cluster-wide objects such as roles. If you self-host, add pg_dumpall --globals-only to capture them.

#The 3-2-1 rule, adapted for databases

The classic rule: three copies, on two kinds of storage, one off-site. For a hosted database:

1. The live database (copy one).
2. The provider's backups and WAL archive (copy two). Fast to restore, but under the same account and provider.
3. Your own logical dump in storage you control, in a different account or provider (copy three).

The third copy protects against the scenarios the provider cannot: account lockout, a mistake in the provider's console, a billing problem, or a decision to leave the platform.

#Automating your own copy

A small job on a server or CI runner you control is enough:

#!/bin/sh
set -eu
STAMP=$(date -u +%Y%m%dT%H%M%SZ)
pg_dump --format=custom --no-owner --no-privileges \
  --file="/tmp/app-$STAMP.dump" "$DATABASE_URL"
# encrypt before it leaves the machine
gpg --batch --yes --recipient [email protected] --encrypt "/tmp/app-$STAMP.dump"
# upload to storage in a separate account, with object lock or versioning
aws s3 cp "/tmp/app-$STAMP.dump.gpg" "s3://example-db-backups/app/$STAMP.dump.gpg"
rm -f "/tmp/app-$STAMP.dump" "/tmp/app-$STAMP.dump.gpg"

Use credentials for this job that can write new objects but cannot delete old ones. That way stolen credentials cannot wipe your history.

#Retention

Keep enough history to notice problems. Many bugs are found days or weeks later.

A common schedule:

• Point-in-time recovery: at least 7 days.
• Daily backups: 7 to 14 days.
• Weekly backups: 4 to 8 weeks.
• Monthly backups: as long as your legal and business needs require, and no longer if they contain personal data (GDPR storage limitation; see GDPR-Friendly Database Hosting).

Write the schedule down. "We keep some backups" is not a policy.

#Encryption and access

• Encrypt backups at rest. Most managed platforms do; for your own copies, encrypt before upload.
• Treat backup storage as sensitive as production. A backup is a full copy of your users' data.
• Limit who can restore. Restoring over production is a destructive action.
• Log access to backups.

#Test restores, every month

A backup you have not restored is a hope. Make a restore drill a recurring calendar task:

1. Pick a backup at random, not the newest.
2. Restore it into a scratch database.
3. Run checks: row counts on key tables, the latest created_at, a few application queries.
4. Time it. That number is your real recovery time.
5. Delete the scratch database.

select 'users' as t, count(*), max(created_at) from public.users
union all
select 'orders', count(*), max(created_at) from public.orders;

Automate it if you can: a CI job that restores last night's dump and runs the checks will catch a broken backup pipeline within a day.

#Monitor the backups

Alert when:

• A scheduled backup did not run or failed.
• The backup size changed sharply (a 90% drop often means a broken dump, not a cleanup).
• WAL archiving is falling behind or failing.
• The newest restorable point is older than expected.

#How this maps to StitchDB

If you use StitchDB, most of the layers are built in, and the Backups page is the control center:

• Continuous change log. Every change is streamed off the project's server to a different machine as it happens, so a lost server loses at most the last few seconds.
• Point-in-time recovery on every plan. 24 hours on Free, 7 days on Pro, 14 on Scale, 30 on Enterprise. Restore in place, or on Pro and up into a new branch without touching production. See Postgres Point-in-Time Recovery.
• Daily restore points on paid plans, kept daily for 7 days, then weekly up to the plan's retention. Backups are encrypted with AES-256. Back up now takes an extra one (at most one per hour).
• Restore one table and a per-table recycle bin for the human-error cases.
• A restore is undoable. A backup of the current state is taken first and listed as Before a restore.

For the third, independent copy:

• Export current data on the Backups page gives a link (valid 15 minutes) to a fresh pg_dump custom-format file of the database. Nothing is stored on StitchDB's side.
• stitchdb db dump from the CLI dumps your schemas, and --data-only the data. See CLI migrations and dumps.
• Export project under Database › Export & import builds an archive with the database dump, auth users, stored files, Edge Function source and settings. See Export.
• Or run pg_dump yourself over a session-mode connection (port 5432; see Connect to Postgres), with a script like the one above.

pg_dump --format=custom --no-owner --no-privileges \
  -f app.dump \
  "postgresql://p_<ref>_owner:<password>@<db-host>:5432/p_<ref>?sslmode=require"

Free projects have no daily backups (only point-in-time recovery for 24 hours), so on Free your own exports matter even more.

Note that point-in-time recovery rewinds the database, not stored files. If your app stores uploads, back up files separately too.

#A one-page checklist

• WAL archiving or managed PITR, with at least 7 days.
• Daily full backups with written retention.
• An independent, encrypted logical copy outside the provider account.
• Write-only credentials for backup uploads.
• Monthly restore drill, timed, with checks.
• Alerts on failed or suspicious backups.
• A runbook: who restores, how, and how to choose the method.
• File storage backed up as well as the database.

#FAQ

#How often should I back up Postgres?

Continuously, via WAL or managed PITR, plus a daily full backup. Add your own logical dump at least daily or weekly depending on how much loss you can accept from the off-platform copy.

#Is pg_dump safe to run on a live database?

Yes. It uses a consistent snapshot and does not block normal reads and writes. Avoid running it during schema migrations.

#Are replicas a backup?

No. Replicas copy deletes and corruption instantly. They protect against server loss, not mistakes.

#How do I back up a very large database?

Use physical backups with incremental or block-level changes for the main layer, and parallel pg_dump --format=directory --jobs for logical copies.

Every StitchDB project has point-in-time recovery, and paid plans add daily restore points. Compare plans or start free.