PostgreSQL Backup: pg_dump, pg_restore and PITR (2026)

Most PostgreSQL backup stories end the same way. A migration goes sideways, someone runs a DELETE without a WHERE clause, or a disk fails, and the team reaches for the backup they were sure existed. Sometimes it does. Often it is stale, half-configured, or has never once been restored to confirm it works. A backup you have not tested is not a backup. It is a guess.

This tutorial covers the two backup methods that carry almost every real PostgreSQL recovery: logical backups with pg_dump and pg_restore, and physical backups that enable point-in-time recovery (PITR). You will see the exact commands, when each method fits, how automated backups work on a managed platform, and, most importantly, how to actually test a restore before you need it.

The short answer: back up a PostgreSQL database with pg_dump for logical, portable snapshots and with continuous WAL archiving for point-in-time recovery. Run pg_dump -Fc dbname > backup.dump to capture a database, restore it with pg_restore -d dbname backup.dump, and rely on PITR to rewind to any moment before an incident. A managed database automates both and lets you restore to a timestamp without touching the tooling.

The Two Kinds of PostgreSQL Backup

PostgreSQL backups fall into two families, and understanding the split is the whole game.

Logical backups capture the contents of your database as a set of SQL statements or a compressed archive. pg_dump reads the data and produces a file that can recreate the schema and rows on any compatible PostgreSQL server. Logical backups are portable across versions and architectures, easy to inspect, and perfect for copying a single database, migrating between hosts, or seeding a staging environment. They are a point-in-time snapshot of the instant the dump began.

Physical backups copy the actual data files on disk, plus the write-ahead log (WAL) that records every change. Combined with continuous WAL archiving, a physical base backup lets you replay the log forward to any moment you choose. This is what makes point-in-time recovery possible: instead of restoring to "last night's dump," you restore to "3:59 PM, one minute before the bad migration ran." Physical backups operate on the whole cluster, not a single database, and they are the foundation of serious disaster recovery.

Neither method replaces the other. Logical dumps are your portable, human-legible safety net. PITR is your fine-grained undo button for the entire instance. A mature setup uses both.

Backing Up with pg_dump

pg_dump is the workhorse. It connects to a running database as a normal client, so it does not require shell access to the server, and it produces a consistent snapshot even while the database serves live traffic.

The single most useful form uses the custom format, which is compressed and can be restored selectively:

# Custom-format dump (compressed, restorable with pg_restore)
pg_dump -Fc -h db.example.com -U appuser -d appdb > appdb.dump

# Or write straight to a named file with -f
pg_dump -Fc -h db.example.com -U appuser -d appdb -f appdb.dump

The -Fc flag selects the custom archive format. Prefer it over a plain SQL file for anything nontrivial, because it compresses automatically and lets pg_restore reorder, filter, and parallelize the restore later.

A few forms worth knowing:

# Plain SQL dump (human-readable, restore with psql)
pg_dump -h db.example.com -U appuser -d appdb -f appdb.sql

# Schema only, no data (useful for diffing structure)
pg_dump -s -h db.example.com -U appuser -d appdb -f schema.sql

# Data only, no schema
pg_dump -a -h db.example.com -U appuser -d appdb -f data.sql

# A single table
pg_dump -Fc -t public.orders -U appuser -d appdb -f orders.dump

# Directory format, dumped in parallel with 4 jobs (fast for large DBs)
pg_dump -Fd -j 4 -h db.example.com -U appuser -d appdb -f appdb_dir

To capture every database in a cluster at once, including roles and tablespaces, use pg_dumpall:

# Full-cluster logical backup, including global roles
pg_dumpall -h db.example.com -U postgres -f cluster.sql

Set the password without an interactive prompt by exporting PGPASSWORD or, better, using a ~/.pgpass file so the secret never lands in your shell history:

# ~/.pgpass, chmod 600
# hostname:port:database:username:password
db.example.com:5432:appdb:appuser:your-password-here

Restoring with pg_restore

A dump you cannot restore is worthless, so the restore path deserves as much attention as the backup. The command depends on the format you dumped.

For a custom or directory-format archive, use pg_restore:

# Restore a custom-format dump into an existing, empty database
createdb -h db.example.com -U appuser appdb_restored
pg_restore -h db.example.com -U appuser -d appdb_restored appdb.dump

# Drop and recreate objects before restoring (clean slate)
pg_restore --clean --if-exists -h db.example.com -U appuser -d appdb appdb.dump

# Parallel restore with 4 workers (much faster for large archives)
pg_restore -j 4 -h db.example.com -U appuser -d appdb_restored appdb.dump

# Restore only one table out of a full-database archive
pg_restore -t orders -h db.example.com -U appuser -d appdb_restored appdb.dump

# List the contents of an archive without restoring anything
pg_restore -l appdb.dump

For a plain SQL dump, there is no pg_restore step. You feed the file to psql directly:

psql -h db.example.com -U appuser -d appdb_restored -f appdb.sql

One habit that saves grief: restore into a fresh database (appdb_restored), not on top of your live one. It lets you verify row counts and spot-check data before you promote anything, and it means a botched restore never touches production.

Automated Backups and Point-in-Time Recovery

Running pg_dump by hand is fine for a one-off migration. It is a terrible backup strategy, because the backup you forgot to run last night is the one you need this morning. Real protection means backups happen on a schedule, without a human in the loop, and it means having PITR so your recovery granularity is measured in minutes, not days.

How PITR works. PostgreSQL continuously writes a stream of changes to the write-ahead log. If you take a physical base backup and then archive every WAL segment as it is filled, you can restore the base backup and replay the WAL forward, stopping at any timestamp you name. Lose an hour of data to a bad script at 4:00 PM and you can recover the state as of 3:59 PM. This is the difference between "we lost everything since the last dump" and "we lost one minute."

Setting PITR up yourself means configuring WAL archiving, a restore_command, and a recovery target, then storing and rotating the WAL somewhere durable:

# postgresql.conf — enable WAL archiving for PITR
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'

# During recovery, target a specific moment (PostgreSQL 12+)
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-07-18 15:59:00+00'

This works, and it is worth understanding, but it is also a system you now own: the archive storage, the retention policy, the base-backup schedule, the monitoring that tells you when archiving silently stopped. Getting any one piece wrong quietly is how teams discover, mid-incident, that their WAL archive filled up three weeks ago.

Managed backups. On a managed PostgreSQL database, this is handled for you. A managed PostgreSQL instance on Out Plane runs automated backups and continuous WAL archiving with no configuration, so point-in-time recovery is available out of the box. You pick a recovery timestamp and restore to it; the archiving, retention, and durability are the platform's job. Managed instances also include read replicas and connection pooling, which lean on the same replication machinery. For the wider production picture of tuning and hardening a database, the PostgreSQL production guide covers connection management, security, and performance in depth.

Managed vs. Self-Hosted Backups Compared

The method is the same; the operational burden is not. This table lays out where the work lives.

Self-hosting teaches you exactly how PostgreSQL recovery works, and there are good reasons to run it yourself. But for anything you do not want to babysit, managed backups remove an entire class of 3 AM surprises. If you are still choosing where to run PostgreSQL at all, free PostgreSQL hosting in Europe compares the real options, and Out Plane offers a permanent free Hobby tier with $20 in trial credit that needs no card.

Test Your Restore Before You Need It

The single most valuable thing in this entire guide is this section, and it is the one most teams skip. A backup that has never been restored has an unknown success rate. Treat the restore as the deliverable, not the dump.

Build a repeatable drill. It takes a few minutes and it turns "I think we have backups" into "I restored one on Tuesday."

#!/usr/bin/env bash
set -euo pipefail

# 1. Take a fresh dump of production
pg_dump -Fc -h "$PROD_HOST" -U "$PGUSER" -d appdb -f /tmp/appdb.dump

# 2. Restore it into a throwaway database
createdb -h "$TEST_HOST" -U "$PGUSER" appdb_verify
pg_restore -j 4 -h "$TEST_HOST" -U "$PGUSER" -d appdb_verify /tmp/appdb.dump

# 3. Verify the restore is sane — row counts, a known record, a checksum
psql -h "$TEST_HOST" -U "$PGUSER" -d appdb_verify -c \
  "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;"

# 4. Tear down the throwaway database
dropdb -h "$TEST_HOST" -U "$PGUSER" appdb_verify

Run this on a schedule, and compare the row counts against what you expect. A restore that succeeds but produces an empty table has told you something crucial before an incident, not during one. If your platform can restore to a fresh instance from a timestamp, exercise that path too, so you know the PITR flow works and roughly how long it takes. Recovery time is a number your business cares about; measure it when nothing is on fire.

Frequently Asked Questions

What is the difference between pg_dump and pg_restore?

pg_dump creates a backup; pg_restore reads one back into a database. pg_dump connects to a running PostgreSQL server and writes out the schema and data as a file, and pg_restore takes a custom- or directory-format dump file and recreates those objects in a target database. For plain SQL dumps you skip pg_restore and load the file with psql instead. The two are a matched pair for the custom archive format.

How do I back up a single PostgreSQL table?

Use pg_dump with the -t flag naming the table, for example pg_dump -Fc -t public.orders -U appuser -d appdb -f orders.dump. This produces an archive containing only that table's schema and data, which you can restore into any compatible database. You can pass -t multiple times to capture several specific tables in one dump.

What is point-in-time recovery in PostgreSQL?

Point-in-time recovery lets you restore a PostgreSQL cluster to any exact moment in the past, not just to the time of your last full backup. It works by combining a physical base backup with the continuously archived write-ahead log, then replaying the log forward to a target timestamp you specify. PITR is what lets you recover the state from one minute before a bad migration rather than from last night.

How often should I back up my PostgreSQL database?

Back up as often as the data you can afford to lose. A daily pg_dump limits your worst case to roughly 24 hours of lost data, which is fine for a low-change side project but unacceptable for a busy application. For anything transactional, pair periodic base backups with continuous WAL archiving so point-in-time recovery caps your data loss at minutes. Managed platforms run frequent automated backups plus WAL archiving by default.

Can I restore a pg_dump backup to a newer PostgreSQL version?

Yes. Logical dumps produced by pg_dump are portable across PostgreSQL versions and are the standard tool for major-version upgrades. Restore a dump taken from an older server into a newer one and PostgreSQL recreates the objects natively. As a rule, use the pg_dump binary from the newer version when dumping from the old server, so the output matches what the new server expects.

Does a managed PostgreSQL database handle backups automatically?

Yes. A managed PostgreSQL database runs automated backups and continuous WAL archiving without any configuration on your part, so point-in-time recovery is available out of the box. A managed instance on Out Plane keeps backups in the same EU region as the database, includes read replicas and connection pooling, and lets you restore to a timestamp without writing any tooling. You still get direct access to pg_dump for portable logical exports whenever you need one.

How do I test that my PostgreSQL backup actually works?

Restore it into a throwaway database and verify the contents. Take a fresh dump, run pg_restore into a new empty database, then check row counts and spot-check known records against what you expect. Tear the test database down afterward. Automate this drill on a schedule so a broken backup is discovered on a routine Tuesday, not during a real outage.

Where should I store PostgreSQL backups?

Store backups somewhere durable, separate from the database server, and in a location you control for compliance reasons. A backup on the same disk as the database offers no protection against disk failure. If EU data residency matters, keep both the database and its backups in an EU region. On Out Plane the database provisions in a region such as Nuremberg or Helsinki, and its automated backups stay in-region alongside it.

Backups Are a Restore You Have Practiced

PostgreSQL gives you two reliable ways to protect data: portable logical dumps with pg_dump and pg_restore, and full point-in-time recovery built on WAL archiving. Learn the commands, because you will reach for them during migrations and one-off exports for the rest of your career. But the real work of a backup strategy is not the dump. It is the discipline of automating it, storing it somewhere durable and in the right region, and rehearsing the restore until it is boring.

The full command reference lives in the official PostgreSQL backup documentation, which is worth reading once end to end. When you would rather not own the archiving, retention, and monitoring yourself, a managed database handles automated backups and PITR for you. Spin one up at console.outplane.com on a permanent free Hobby tier with $20 in trial credit and no card, or see pricing for what it costs as you grow. Either way, the rule holds: the only backup that counts is the one you have restored.