What's in this guide
PostgreSQL ships with genuinely good backup tools. pg_dump, pg_dumpall, pg_basebackup and WAL archiving cover almost every situation you will meet. What PostgreSQL does not ship with is a backup system: nothing schedules those tools, nothing tells you when a run fails, nothing checks that the file you produced can actually be restored, and nothing moves it somewhere the database host cannot take down with it.
That gap is where this guide lives. It is for developers running self-hosted Postgres in production, teams running it in Docker or Kubernetes, and anyone who has a backup script they have never tested. It covers which tool to reach for, which format to choose, how to get dumps off the host, and how to restore when it matters.
It does not cover PostgreSQL itself. For reference documentation, use the official PostgreSQL backup documentation. What you will find here is the opinionated companion: what each tool does, where it stops, and what to do about the difference.
How to back up PostgreSQL
Start with pg_dump and the custom format. Add a schedule, then get the output off the database host. Those three steps cover most production setups, and each one has a guide below.
PostgreSQL pg_dump and pg_restore guide
The complete reference for both commands: every common flag, what it does, and the errors you will hit. Start here if you are backing up a single database.
Choosing a backup format: custom vs plain
Why -Fc is the right default, when a plain SQL file is easier to work with, and what selective restore actually buys you.
Automate PostgreSQL backups on a schedule
A working backup script that dumps, compresses, uploads and rotates. The starting point most teams build, with the gaps it leaves called out.
Send PostgreSQL backups off-site
How to get dumps into object storage you control. Written for S3, and the same pattern works for R2, Backblaze B2, Wasabi, Azure and Google Cloud Storage.
Storing somewhere else? We have step-by-step guides for Backblaze B2, Wasabi, Azure Blob Storage, Google Cloud Storage, DigitalOcean Spaces and Dropbox.
How to restore
The half of the job nobody rehearses. A backup you have never restored is an assumption, and the moment you find out it was wrong should not be the moment you needed it.
Where PostgreSQL runs
The commands are the same everywhere. Reaching the database is not.
Managed PostgreSQL
On a managed platform your provider handles the base backups, and the questions change: how long is retention, can you download a copy, and what happens if you lose access to the account. Each provider answers differently, so we cover them separately.
- AWS RDS for PostgreSQL
- Neon
- Supabase, which runs Postgres under the hood and adds Storage and Edge Functions on top
- DigitalOcean Managed Databases
Compare your options
Most teams start with a script and cron. It works until it quietly does not. These pieces cover what else is available and when the switch is worth making.
PostgreSQL backup tools compared
pgBackRest, Barman, WAL-G and managed services, with what each one is actually good at and the situations where it is the wrong choice.
Beyond pg_dump and cron
What a DIY backup script does not do: alert on failure, verify restores, enforce retention, keep an audit trail. And how to decide whether that matters for you yet.
PostgreSQL backup FAQ
Short answers to the questions that should be answerable without a click. These double as FAQ schema markup for SEO.
How do I back up a PostgreSQL database?
For a single database, run pg_dump, which writes out the SQL and data needed to rebuild it. For an entire cluster including roles and tablespaces, run pg_dumpall. For a binary copy of the whole data directory, which is what you need for replication or point-in-time recovery, run pg_basebackup. Most teams start with pg_dump in custom format and add the rest when they outgrow it.
What are the different types of backups in PostgreSQL?
Two, and the distinction drives every other decision. A logical backup, produced by pg_dump or pg_dumpall, captures your schema and data as SQL, which makes it portable across Postgres versions and providers. A physical backup, produced by pg_basebackup or a file system copy, is a byte-level copy of the data directory: faster on large databases and required for point-in-time recovery, but tied to the same major version and platform.
Can a PostgreSQL database be backed up without stopping it?
Yes. pg_dump runs against a live database and uses MVCC to take a consistent snapshot as of the moment it starts, so you do not need downtime and you do not get a half-written dump. The caveat is that a long-running dump holds a transaction open for its duration, which delays vacuum cleanup on a busy database. pg_basebackup also runs online against a live cluster.
How can I automatically back up my PostgreSQL database?
The common starting point is a shell script that runs pg_dump and uploads the result, triggered by cron. That covers taking the backup and nothing else: cron will not tell you when the job fails, will not verify the dump restores, and will not enforce retention. Teams usually reach a point where they either build those pieces themselves or move to a managed service that already has them.
Is a pg_dump file a backup?
It is a backup artifact, not a backup system. A dump becomes a backup when it is taken on a schedule, stored somewhere other than the database host, monitored so a failure raises an alert, aged out under a retention policy, and restored at least once so you know it works. Most incidents we see involve a dump that existed and a restore that had never been attempted.
Which pg_dump format should I use?
Custom format, the -Fc flag, is the right default. It is compressed, and pg_restore can selectively restore individual tables or schemas from it rather than forcing an all-or-nothing reload. Plain format, -Fp, produces a readable SQL file that you replay with psql, which is useful for small databases and for inspecting what a dump contains. Directory format, -Fd, is the one to reach for when you want parallel dump and restore on a large database.
How do I back up PostgreSQL roles and users?
pg_dump does not include them. Roles, tablespaces and other cluster-wide objects live outside any single database, so a pg_dump-only strategy silently loses them and the gap only surfaces during a restore. Use pg_dumpall for the whole cluster, or pair pg_dumpall with the --globals-only flag alongside your per-database dumps.
What is point-in-time recovery in PostgreSQL?
Point-in-time recovery, or PITR, combines a base backup with a continuous archive of the Write-Ahead Log so you can restore to any moment inside your retention window rather than only to the last scheduled backup. It is the only native route to a Recovery Point Objective measured in seconds. Unlike on managed platforms, there is no toggle: you configure archiving yourself, or use a tool such as pgBackRest, Barman or WAL-G that manages it for you.
Can I restore a pg_dump to a different PostgreSQL version?
Usually yes, and this portability is the main reason to keep logical backups. Restoring into a newer major version is the supported direction and the common upgrade path. Restoring into an older major version often fails, because the dump can contain syntax the older server does not recognise. Use the pg_dump binary from the newer of the two versions, and test the path before you depend on it.
How often should I back up PostgreSQL?
Work backwards from how much data you can afford to lose. A nightly dump means accepting up to 24 hours of loss in the worst case. If that is too much, either increase the frequency or move to WAL archiving, which narrows the window to seconds. The schedule is a business decision expressed in technical settings, not a technical default.
Where should PostgreSQL backups be stored?
Somewhere the database host cannot take down with it. A dump written to local disk protects you against a dropped table and against nothing else: not disk failure, not a deleted instance, not a compromised host. Object storage in a separate account or region is the usual answer, and it is also what auditors look for under SOC 2, ISO 27001 and GDPR.
How do I know my PostgreSQL backup actually works?
Restore it. Load the dump into a scratch database, then check that the row counts, schema and a few known records match what you expect. A backup that has never been restored is an assumption, and the moment you discover a broken one should not be the moment you need it. Automating this check is the single highest-value thing most teams are not doing.
Glossary
One-line definitions for the terms used across this guide. Written for humans and for AI search.
- Logical backup
- A backup that captures the database as SQL statements and data, produced by pg_dump or pg_dumpall. Portable across Postgres versions and providers, which makes it the format you want for off-site archives and for moving between hosts.
- Physical backup
- A byte-level copy of the PostgreSQL data directory, produced by pg_basebackup or a file system snapshot. Faster than a logical dump on large databases and required as the base for point-in-time recovery, but tied to the same major version and platform.
- pg_dump
- The command-line tool that produces a logical backup of a single database. Runs online against a live server and takes a consistent snapshot. Does not include roles, tablespaces or other cluster-wide objects.
- pg_dumpall
- The command-line tool that dumps an entire PostgreSQL cluster, including the global objects pg_dump leaves out: roles, tablespaces and grants. The --globals-only flag dumps just those, so you can pair it with per-database pg_dump runs.
- pg_basebackup
- The command-line tool that takes a physical base backup of a running cluster. Used to seed streaming replication standbys and as the starting point for point-in-time recovery. Cannot back up a single database on its own.
- WAL (Write-Ahead Log)
- The append-only log PostgreSQL writes every change into before applying it to the data files, used for crash recovery and replication. Archiving WAL continuously alongside a base backup is what makes point-in-time recovery possible.
- PITR (Point-in-Time Recovery)
- Recovery to an arbitrary moment rather than to the last scheduled backup, achieved by restoring a base backup and replaying archived WAL up to a chosen point. On self-hosted PostgreSQL this is configuration you set up yourself, not a feature you switch on.
- Custom format (-Fc)
- The compressed archive format pg_dump produces with the -Fc flag. Not human-readable, restored with pg_restore rather than psql, and the only common format that supports selectively restoring individual tables or schemas.
- RPO / RTO
- Recovery Point Objective is how much data you are willing to lose, measured as time since the last backup. Recovery Time Objective is how long you are willing to be down while restoring. Every backup schedule is an implicit bet on these two numbers.
- S3-compatible storage
- Any object storage service that speaks the Amazon S3 API, including AWS S3, Cloudflare R2, Backblaze B2 and Wasabi. The usual destination for off-site Postgres dumps, because the same tooling works across all of them.
We use pg_dump too. What we add is everything around it: schedules, alerts when a run fails, off-site delivery to storage you own, and retention you set once. See how it works →
Noticed something out of date or missing? This guide is maintained by the SimpleBackups team. Email us at hello@simplebackups.com.