Backup and restore

Last reviewed May 28, 2026 Content v20260528

Track mode

sql_sandbox

Means

SQL sandbox

Reading

~1 min

Level

intermediate

This lesson

This lesson teaches Backup and restore: the SQL patterns, schema habits, and query reasoning you need before advancing in PostgreSQL.

Teams query Backup and restore on every PostgreSQL codebase—skipping it leaves gaps in debugging and data reviews.

You will apply Backup and restore in contexts like: Managed cloud databases, on-call runbooks, and disaster recovery drills.

Copy Postgres SQL into psql, local PostgreSQL, or DB Fiddle (PostgreSQL dialect)—use \d and EXPLAIN ANALYZE where lessons show them. The in-browser lab ships later; psql is the practice path now.

Toward the end—consolidate before PostGIS teaser, interview prep, and production checklist.

Backups prove disaster recovery. Logical backups (pg_dump) are portable; physical backups (base backups + WAL archiving) suit large databases and point-in-time recovery.

pg_dump basics

# Shell — custom format supports parallel restore
pg_dump -Fc -d practice -f practice.dump

pg_restore -d practice_restored practice.dump

Practice: Use local or staging environments only for backup/restore and pooling experiments.

SQL plain dump

pg_dump -d practice --schema-only -f schema.sql
pg_dump -d practice --data-only -t customers -f customers.sql

Restore drill checklist

Restore to isolated instance monthly
Verify row counts and critical queries
Document RTO/RPO with stakeholders
Combine with managed automated snapshots in production

Important interview questions and answers

Q: pg_dump -Fc?
A: Custom compressed archive—parallel pg_restore.
Q: PITR?
A: Point-in-time recovery needs WAL archiving plus base backup—not just a single plain SQL file.

Self-check

Which tool creates a logical backup?
Why test restores, not just backups?

Tip: A backup file you never restored is hope, not insurance.

Interview prep

pg_dump?: Logical backup tool producing portable dump files.
Restore drill?: Proves backups work—file existence is not enough.

Discussion

Past discussion is visible to everyone. Only logged-in users can post comments and replies.

Starter discussion topics

pg_dump format?
PITR idea?

No discussion yet. Be the first to ask a question.