Backup and Restore

Introduction

Backups protect against mistakes, hardware failure, and bad migrations. pg_dump produces portable logical backups; pg_restore reloads custom-format dumps. This chapter dumps your practice database, restores to a new database, exports CSV with COPY, and introduces WAL and point-in-time recovery at a conceptual level. Compare with MySQL mysqldump.

Prerequisites

Installation and psql
Shell access to run pg_dump and psql
Database hello_demo or shop_dev with sample tables

Logical Backup With pg_dump

Plain SQL file (human-readable, psql restore):

bash

pg_dump "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo" \
  --no-owner \
  --no-acl \
  -f hello_demo_backup.sql

Docker:

bash

docker exec postgres pg_dump -U postgres -d hello_demo \
  --no-owner --no-acl \
  -f /tmp/hello_demo_backup.sql
 
docker cp postgres:/tmp/hello_demo_backup.sql ./hello_demo_backup.sql

Flag	Purpose
`--no-owner`	Omit `OWNER TO`—restore as current user
`--no-acl`	Omit GRANT—simpler dev restores
`--schema-only`	Structure without data
`--data-only`	Data without CREATE TABLE
`-t posts`	Single table

Custom format (compressed, parallel restore):

bash

pg_dump -U postgres -d hello_demo -Fc -f hello_demo.dump

Code explanation:

Plain .sql is easy to diff and edit
-Fc custom format supports selective pg_restore and parallel jobs

All databases (roles + globals)—superuser:

bash

pg_dumpall -U postgres -f cluster_backup.sql

Includes CREATE ROLE—use carefully; passwords are not exported in plain text by default.

Restore Plain SQL

Create empty database:

sql

CREATE DATABASE hello_demo_restore;

Import:

bash

psql "postgresql://postgres:learnpass@127.0.0.1:5432/hello_demo_restore" \
  -f hello_demo_backup.sql

Or inside psql:

sql

\c hello_demo_restore
\i hello_demo_backup.sql

Verify:

sql

\dt
SELECT COUNT(*) FROM posts;

Restore Custom Format

bash

pg_restore -U postgres -d hello_demo_restore \
  --no-owner --no-acl \
  -j 4 \
  hello_demo.dump

Flag	Purpose
`-j 4`	Parallel restore with 4 workers
`-l`	List contents without restoring
`-t posts`	Restore one table

Create target database first—pg_restore does not create the database unless you use -C with createdb privileges.

COPY Export and Import

Server-side CSV export:

bash

docker exec postgres psql -U postgres -d hello_demo -c \
  "COPY (SELECT id, email FROM users ORDER BY id) TO STDOUT WITH (FORMAT csv, HEADER true)" \
  > users_export.csv

Client-side \copy (no superuser file path on server):

sql

\copy users (email, display_name) TO 'users_export.csv' WITH (FORMAT csv, HEADER true)

Import:

sql

\copy users (email, display_name) FROM 'users_seed.csv' WITH (FORMAT csv, HEADER true)

See Insert and COPY.

Before Production Restore

Restore to staging first
Stop app or enable maintenance mode
Confirm disk space—restore expands beyond dump file size
Match major PostgreSQL version (16 dump → 16+ generally OK; test upgrades)

Large Import Tips

Wrap bulk loads in a transaction:

sql

BEGIN;
-- COPY or many INSERTs
COMMIT;

For restore conflicts, pg_restore --clean drops objects first—destructive on target DB.

Split huge SQL files:

bash

split -l 50000 hello_demo_backup.sql hello_demo_part_

WAL and Point-in-Time Recovery (Concept)

PostgreSQL writes the Write-Ahead Log (WAL) before committing data. Base backup (filesystem snapshot or pg_basebackup) plus archived WAL enables point-in-time recovery (PITR)—restore to a moment before a bad DROP.

text

Base backup (Sunday) + WAL archives → restore to Tuesday 14:32

Managed services (RDS, Cloud SQL, Neon) automate this—self-hosted ops read official continuous archiving docs.

Physical copy of $PGDATA requires consistent snapshot or pg_basebackup—not a running arbitrary file copy.

Backup Schedule Habits

Environment	Frequency
Dev laptop	Before risky experiment
Staging	Daily automated `pg_dump`
Production	Automated dumps + off-site storage + test restore monthly

Pair schema changes with Git migrations—not dumps alone.

FAQ

pg_dump lock tables?

pg_dump uses a snapshot (MVCC)—generally no long exclusive locks on reads. Heavy writes continue; very long transactions can block dump start.

Plain SQL vs custom format?

.sql for simple dev and grep. .dump for large DBs, parallel restore, selective table restore.

Restore wrong database?

DROP DATABASE target and recreate—or restore to a new name first.

Backup includes roles?

pg_dump per database does not include cluster roles—use pg_dumpall --roles-only or document roles separately.

mysqldump equivalent flags?

--single-transaction ≈ MVCC snapshot in pg_dump. --routines ≈ included by default in pg_dump.

Test backups?

Monthly restore drill to empty instance—untested backups are wishful thinking.