PostgreSQL Backup and Restore Best Practices

Backing up PostgreSQL is easy to underestimate because the first version of a backup strategy often looks deceptively simple.

You run a command. A file appears. The job succeeds. The dashboard turns green.

That is not the same thing as being recoverable.

A real PostgreSQL backup and restore strategy has to answer harder questions:

• What data loss can we tolerate?
• How long can recovery take?
• Do we need point-in-time recovery?
• Are we protecting cluster-wide objects like roles and tablespaces?
• Can we restore selectively or only the whole cluster?
• Have we actually tested the restore process?
• Are backups stored safely off the server?
• Can we recover after operator error, corruption, or total host loss?

That is why backup strategy is not only about making copies. It is about making recovery predictable.

1. Start With Recovery Goals, Not Tools

Before choosing commands, define the recovery goals.

Two concepts matter most:

RPO: Recovery Point Objective

How much data can you afford to lose?

Examples:

• 24 hours
• 1 hour
• 5 minutes
• near-zero

RTO: Recovery Time Objective

How long can the system be down before recovery is completed?

Examples:

• 10 minutes
• 1 hour
• 4 hours
• next business day

These goals shape the backup design.

Practical examples

If you can tolerate:

• up to 24 hours of data loss
• and a slower rebuild

then a regular logical backup may be enough for some systems.

If you need:

• low data loss
• and recovery to a specific point before an incident

then WAL archiving and point-in-time recovery become much more important.

Do not start with:

• “Should we use pg_dump or pg_basebackup?”

Start with:

• “What recovery outcome do we need?”

2. Understand the Three Main PostgreSQL Backup Approaches

PostgreSQL backup strategy usually falls into three broad categories:

1. Logical backups
2. Physical backups
3. Continuous archiving / PITR

Each solves a different problem.

3. Use pg_dump for Logical Backups

pg_dump is PostgreSQL’s standard logical backup tool for one database at a time.

It is excellent when you want:

• portability
• object-level restore flexibility
• schema-only or data-only exports
• movement into newer PostgreSQL versions
• migration between architectures
• developer-friendly restore workflows

Why logical backups are useful

Logical backups are great for:

• application database exports
• selective restore of objects
• migration work
• environment refreshes
• long-term portable backups of database contents

Strong use cases

• backing up one important application database
• seeding staging from production safely
• archiving data in a version-portable format
• restoring selected tables or schemas
• upgrading environments where portability matters

Good command examples

Basic plain-text dump:

pg_dump mydb > mydb.sql

Custom-format dump:

pg_dump -Fc mydb > mydb.dump

Directory-format dump with parallelism:

pg_dump -Fd -j 4 -f mydb.dir mydb

Why custom or directory format is often better

Plain SQL dumps are easy to inspect, but custom and directory formats are often better operationally because they support:

• selective restore
• compression
• and parallel restore workflows

For many real systems, pg_dump -Fc is the best logical-backup default.

4. Use pg_dumpall When Cluster-Wide Globals Matter

A common mistake is assuming pg_dump is enough for a full PostgreSQL environment.

It is not.

pg_dump backs up one database at a time and does not include cluster-wide objects such as:

• roles
• tablespaces
• and other global metadata

That is where pg_dumpall matters.

Good use cases for pg_dumpall

• backing up a whole cluster logically
• preserving roles and tablespaces
• capturing cluster globals alongside database backups
• rebuilding a full logical environment

Good command examples

Whole cluster logical dump:

pg_dumpall > cluster.sql

Globals only:

pg_dumpall --globals-only > globals.sql

A very practical pattern is:

• use pg_dump per database
• and also dump globals with pg_dumpall --globals-only

That way you preserve:

• roles
• tablespace definitions
• and per-database backup flexibility

5. Use pg_restore Correctly

pg_restore is for non-text pg_dump archives such as:

• custom format
• directory format
• tar format

It matters because these formats are usually better for serious operational restores.

Example

Restore a custom archive:

pg_restore -d mydb mydb.dump

Useful restore flags

You will often want options like:

• -j for parallel restore
• -c to clean objects before restore
• -C to create the database
• -t to restore a specific table
• -n to restore a specific schema

Example:

pg_restore -j 4 -d mydb mydb.dump

This is one reason custom and directory-format dumps are attractive: they are easier to work with operationally than a huge single SQL script.

6. Use pg_basebackup for Physical Cluster Backups

When you need a physical copy of the cluster for disaster recovery, standby setup, or PITR workflows, pg_basebackup is the main built-in tool.

What it is good for

Use pg_basebackup when you need:

• a physical base backup of the whole cluster
• standby or replica setup workflows
• point-in-time recovery workflows
• recovery of the full instance, not just one database
• lower-level operational recovery paths

Important limitation

A physical backup is cluster-wide. It is not for selective restore of one table or one database object.

If you want selective logical recovery, pg_dump is the better fit.

Example

pg_basebackup -h dbhost -D /backups/basebackup -Fp -Xs -P

Why it matters

pg_basebackup captures the cluster as PostgreSQL stores it, which is why it works for:

• PITR
• standby creation
• and full-cluster disaster recovery

That also means it is more server-version-specific than logical backup approaches.

7. Use WAL Archiving for Point-in-Time Recovery

If your recovery goals require restoring to a specific point in time, logical backups alone are usually not enough.

That is where continuous archiving comes in.

PostgreSQL can combine:

• a base backup
• plus a continuous sequence of archived WAL files

to recover to a specific time or recovery target.

Why this matters

This is one of the most important PostgreSQL backup capabilities because it helps in scenarios like:

• accidental deletes
• bad migrations
• destructive application bugs
• partial data corruption
• “restore to five minutes before the mistake”

What PITR needs

A workable PITR strategy needs:

• a base backup
• continuous WAL archiving
• and a restore plan that knows how to replay WAL to the desired point

Important rule

You must set up and test WAL archiving before trusting PITR.

A PITR plan without verified WAL archiving is not really a PITR plan.

8. Remember That Replicas Are Not Backups

This is one of the most common PostgreSQL misconceptions.

A read replica or warm standby is useful for:

• availability
• failover
• read scaling
• maintenance workflows

But it is not a complete backup strategy.

Why not?

Because many destructive events replicate too:

• accidental deletes
• bad writes
• bad migrations
• logical corruption
• some operator mistakes

A replica helps with availability. A backup helps with recovery from bad state.

You often want both, but they do not replace each other.

9. Choose Logical vs Physical Based on What You Need to Restore

A very practical rule is:

Choose logical backups when you need:

• portability
• selective restore
• easier version migration
• schema or object-level exports
• human-auditable dump workflows

Choose physical backups when you need:

• full-cluster disaster recovery
• PITR
• standby creation
• exact physical recovery of the instance

Choose both when the system is important

For many serious systems, the best answer is not either/or.

It is:

• logical backups for portability and selective restore
• physical base backups plus WAL archiving for disaster recovery and PITR

That gives you more than one recovery path.

10. Test Restores, Not Just Backup Jobs

This is the most important best practice in the whole article.

A backup is only valuable if it restores successfully.

Not theoretically. Actually.

Good restore testing questions

• Can you restore the backup at all?
• How long does it take?
• Are the roles and privileges correct afterward?
• Does the application actually run?
• Does the data look complete?
• Can you restore one table if needed?
• Can you perform PITR to a specific target?
• Does the team know the steps?

Good practice

Test restores should be part of the backup strategy, not something you hope to figure out during an incident.

For physical base backups, pg_verifybackup is useful because it can validate a pg_basebackup backup against its backup_manifest. That is valuable, but it still does not replace real restore testing.

Practical rule

Verification is good. Restore drills are mandatory.

11. Store Backups Off the Database Host

A local backup on the same server is better than nothing. It is not enough for serious recovery planning.

If the host fails, gets corrupted, is encrypted by ransomware, or is lost entirely, local-only backups may fail with it.

Better pattern

Store backups in at least one independent location such as:

• object storage
• another host
• another volume with separate controls
• offsite or cross-region storage
• immutable or harder-to-modify storage for critical environments

Good principle

The database server should not be the only place where its backups live.

12. Protect Backup Data Like Production Data

Backups often contain everything:

• customer data
• credentials or secrets embedded in tables
• tokens
• operational metadata
• historical records
• WAL segments containing effectively complete change history

That means backup security matters.

Good backup security practices

• encrypt backups at rest
• protect them in transit
• restrict who can access backup storage
• rotate credentials used by backup jobs
• log and review backup access
• avoid leaving dump files in casual temp locations
• protect WAL archives with the same seriousness as primary data

A backup that leaks is still a major security incident.

13. Keep Retention and Restore Cost in Balance

Retention is not only a compliance or storage question. It is also an operational question.

Longer retention gives you:

• deeper recovery windows
• more historical rollback options
• more safety against late-discovered issues

Longer retention also means:

• more storage
• more WAL history to manage
• longer bookkeeping
• potentially longer recovery windows if base backups are too infrequent

Good design question

How long do you need to:

• restore to the latest point
• restore to an older point
• investigate a late-discovered problem
• meet legal or business retention needs

Then build retention around that, not around a random default.

14. Know Your Restore Paths Before You Need Them

A good PostgreSQL backup strategy usually includes more than one restore scenario.

Common restore paths

1. Full logical restore

Restore an entire database from pg_dump.

2. Selective object restore

Restore one schema or table from a logical archive.

3. Full cluster restore

Recover a physical base backup.

4. Point-in-time recovery

Restore the base backup and replay WAL to a target point.

5. Globals restore

Restore roles and tablespaces via pg_dumpall --globals-only.

Why this matters

Different incidents need different responses.

If the team only knows how to do:

• one giant all-or-nothing restore

then many recoverable incidents become more disruptive than they need to be.

15. Be Careful About Version and Environment Compatibility

Logical and physical backups behave differently here.

Logical backups

Logical dumps from pg_dump are generally the better choice when you want to load into newer PostgreSQL versions or different machine architectures.

Physical backups

Physical backups and continuous-archiving workflows are much more version-specific because they reflect the actual storage layout of the cluster.

Practical implication

If your goal is:

• portability
• environment moves
• or major-version migration

prefer logical backup workflows.

If your goal is:

• exact recovery of the running cluster
• PITR
• standby creation

physical workflows are the right fit.

16. Use Parallelism for Large Logical Backups and Restores

For larger databases, logical backup and restore time can become a real operational issue.

PostgreSQL supports:

• parallel dump with pg_dump -j in directory format
• parallel restore with pg_restore -j for custom or directory archives

Example

pg_dump -j 4 -F d -f out.dir mydb

pg_restore -j 4 -d mydb out.dir

If your logical backup windows are long or restore time is a concern, this is one of the simplest high-value optimizations.

17. Run ANALYZE After Logical Restores

After restoring a logical dump, PostgreSQL’s own backup docs recommend running ANALYZE so the optimizer has useful statistics.

This matters because after a restore:

• the data is present
• but planner statistics may not yet reflect the restored state usefully

Good follow-up step

After a large logical restore:

analyze;

This is a small step that can prevent surprisingly bad early query plans on a restored environment.

18. Write and Rehearse a Real Recovery Runbook

Documentation matters more than people think here.

A good backup strategy should have a written recovery runbook covering:

• where the backups live
• what the retention rules are
• who can access them
• how to restore a logical dump
• how to restore a base backup
• how to perform PITR
• how to restore globals
• how to verify success
• who owns the decision-making during an incident

Why this matters

In a real recovery event, speed depends on clarity.

A backup that exists but requires rediscovering the procedure under pressure is much less valuable than one backed by a practiced runbook.

19. Common PostgreSQL Backup Mistakes

Mistake 1: Treating replicas as backups

They are not the same thing.

Mistake 2: Backing up only user tables, not roles or globals

You can restore the data and still have a broken environment.

Mistake 3: Never testing restores

This is the most common and most dangerous failure.

Mistake 4: Keeping backups only on the same host

This fails many real disaster scenarios.

Mistake 5: Using only logical backups when PITR is required

Logical dumps alone do not give you point-in-time recovery.

Mistake 6: Using only physical backups when selective restore is frequently needed

That makes ordinary recovery tasks harder than necessary.

Mistake 7: Forgetting backup security

A leaked backup can be as serious as a production breach.

Mistake 8: No documented recovery process

Even good backups fail operationally when nobody knows the exact restore steps.

20. A Practical PostgreSQL Backup Strategy for Most Serious Systems

A solid default pattern for many production systems looks like this:

Logical layer

• regular pg_dump of important databases in custom format
• periodic pg_dumpall --globals-only
• logical restore drills for selective recovery

Physical layer

• regular pg_basebackup
• WAL archiving enabled and tested
• PITR procedure documented and tested
• backup verification with pg_verifybackup where appropriate

Operational layer

• off-host storage
• encryption and access controls
• retention rules
• restore runbooks
• scheduled restore drills

That gives you:

• portability
• selective restore
• full-cluster recovery
• and recovery to a chosen point in time

FAQ

What is the best PostgreSQL backup method?

There is no single best method for every case. pg_dump is best for portable logical backups of databases or selected objects, while pg_basebackup plus WAL archiving is better when you need full-cluster recovery and point-in-time recovery.

Is a read replica a backup?

No. A replica can help with availability, but it can also replicate destructive changes. You still need real backups and tested restore procedures.

Should I use pg_dump or pg_basebackup?

Use pg_dump for logical backups you want to restore selectively or move across versions more easily. Use pg_basebackup when you need a physical cluster backup for disaster recovery, standbys, or PITR.

How often should I test PostgreSQL restores?

Regularly. The exact cadence depends on the system, but the key rule is that restore testing should be part of the backup process, not an afterthought only used during an incident.

Conclusion

PostgreSQL backup and restore best practices are really about building confidence in recovery, not just building files.

That means:

• choosing the right backup types for the recovery goals
• combining logical and physical approaches when needed
• using WAL archiving when PITR matters
• preserving globals like roles and tablespaces
• securing backup data properly
• storing backups off the database host
• verifying backups
• and testing restores often enough that recovery is a routine skill, not a crisis improvisation

The best backup strategy is the one your team can actually recover from under pressure.

That is the standard that matters.