Backup, Restore & Replication

logical backups: pg_dump / pg_restore

# dump a single database to a compressed custom-format file
pg_dump -Fc -d mydb -f mydb.dump

# restore it into a fresh (already-created) database
pg_restore -d mydb_restored mydb.dump

# a plain SQL dump — human-readable, restorable with psql instead of pg_restore
pg_dump -d mydb -f mydb.sql

pg_dump produces a logical backup — effectively "the SQL statements needed to recreate this data," portable across PostgreSQL versions and even OS platforms. It's consistent (a snapshot as of one instant) without blocking concurrent writes, but restoring a large database this way is slow, since it's re-inserting and re-indexing everything from scratch.

physical backups

A physical backup copies the actual on-disk data files (e.g. via pg_basebackup, or a filesystem/volume snapshot). Much faster to restore for a large database, since it's not replaying SQL, but it's version- and platform-specific, and typically larger since it isn't just the logical data. Tools like pgBackRest and WAL-G manage physical backups plus continuous WAL archiving in production.

point-in-time recovery (PITR)

PostgreSQL continuously writes every change to a Write-Ahead Log (WAL) before applying it to the actual data files — the same mechanism that makes crash recovery possible. Archiving that WAL stream alongside a base physical backup means you can restore to any specific moment ("5 minutes before someone ran that bad DELETE"), not just to the moment of the last full backup. This is the real disaster-recovery answer to "someone dropped a table in production" — a nightly-only backup schedule with no WAL archiving means losing everything since the last backup.

streaming replication

# on the replica's postgresql.conf / standby.signal (PG 12+)
primary_conninfo = 'host=primary-server port=5432 user=replicator password=...'

A streaming replica continuously receives and replays the primary's WAL, staying nearly in sync in real time. It serves two purposes: a hot standby ready to be promoted if the primary fails, and/or a read-only replica you can route reporting/analytics queries to, so they don't compete with production traffic on the primary.

Term	Meaning
Synchronous replication	primary waits for the replica to confirm before considering a commit durable — stronger guarantee, adds latency
Asynchronous replication	primary commits immediately, replica catches up shortly after — a small window of possible data loss on failover
Replication lag	how far behind the replica currently is — worth monitoring directly, since a badly lagging "real-time" replica can be a silent problem

the part that actually matters: test your restores

A backup nobody has ever restored from is a hypothesis, not a safety net — corrupted dump files, missing extensions on the restore target, and permission mismatches are all things that only surface when you actually try. Practicing a full restore periodically, on a schedule, is what turns "we have backups" into something you can actually rely on during an incident.

where to go from here

Transactions & Isolation Levels — the WAL and MVCC concepts underlying all of this.
Users, Roles & Permissions — the replication role a streaming replica connects as.