PostgreSQL Backup & Restore: A Complete Step-by-Step Guide

Why PostgreSQL Backup Strategy Matters

No backup strategy means one bad day can mean permanent data loss. PostgreSQL offers several backup methods, each suited to different recovery requirements. Understanding the trade-offs between them — and knowing how to restore — is a fundamental DBA skill.

Backup Methods Overview

Method	Type	Hot Backup?	Point-in-Time Recovery?
pg_dump	Logical	Yes	No
pg_dumpall	Logical	Yes	No
pg_basebackup	Physical	Yes	Yes (with WAL)
pgBackRest	Physical	Yes	Yes
File system snapshot	Physical	Depends	Yes (with WAL)

Method 1: pg_dump (Logical Backup)

pg_dump exports a single database to a SQL script or custom archive format. It's the simplest option for logical backups.

Creating a Backup

Export in the custom format (recommended for flexibility):

pg_dump -U postgres -Fc -f /backups/mydb_$(date +%F).dump mydb

Restoring with pg_restore

createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored /backups/mydb_2025-03-05.dump

Key options:

-Fc — custom format (compressed, supports parallel restore)
-j 4 — use 4 parallel workers during restore
--no-owner — skip ownership commands during restore

Method 2: pg_basebackup (Physical Backup)

pg_basebackup creates a binary copy of the PostgreSQL data directory. Combined with WAL archiving, it enables point-in-time recovery (PITR).

pg_basebackup -U replicator -D /backups/base -Ft -z -P -Xs

Flags explained:

-Ft — tar format
-z — gzip compression
-Xs — stream WAL during backup
-P — show progress

Method 3: Point-in-Time Recovery (PITR)

PITR allows you to restore a database to any specific moment in time — invaluable when you need to recover from accidental data deletion or corruption.

Step 1: Enable WAL Archiving

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'

Step 2: Restore the Base Backup

Copy your base backup to the PostgreSQL data directory.

Step 3: Configure Recovery

Create a recovery.signal file and set the target in postgresql.conf:

restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2025-03-04 14:30:00'

Step 4: Start PostgreSQL

PostgreSQL will replay WAL segments up to the specified time and pause, allowing you to verify data before promoting the instance.

Backup Best Practices

Test your restores regularly — a backup you've never restored from is just a file. Schedule monthly restore drills.
Follow the 3-2-1 rule — 3 copies, 2 different media types, 1 offsite location.
Monitor backup completion — alert on missed or failed backup jobs immediately.
Encrypt backup files — especially when storing offsite or in cloud object storage.
Document your RTO and RPO — know your Recovery Time Objective and Recovery Point Objective before an incident, not during.

A solid backup strategy is the foundation of every DBA's operational plan. Invest the time to set it up correctly, test it regularly, and document the process so any team member can execute a restore under pressure.