PostgreSQL Backup and Restore

PostgreSQL's standard backup tool is pg_dump (per-database) alongside pg_dumpall (entire server) — both run from your regular terminal, not inside psql.

Plain SQL Backup

pg_dump shop > shop_backup.sql

Produces a plain-text file of SQL statements that recreate the database's structure and data — easy to read, version-control, or edit by hand.

Restoring a Plain SQL Backup

psql shop < shop_backup.sql

Custom Format Backup (Recommended for Larger Databases)

pg_dump -Fc shop > shop_backup.dump

• -Fc uses PostgreSQL's own compressed, binary format.
• Smaller file size than plain SQL, and supports selective restores (just one table, or schema-only, without touching the rest).

Restoring a Custom Format Backup

pg_restore -d shop shop_backup.dump

# restore into a brand-new, empty database:
createdb shop_new
pg_restore -d shop_new shop_backup.dump

# restore just one table:
pg_restore -d shop -t employees shop_backup.dump

Backing Up Every Database on the Server

pg_dumpall > all_databases_backup.sql

Also captures roles and other server-wide (cluster-level) objects that a per-database pg_dump leaves out.

Restoring Everything

psql -f all_databases_backup.sql postgres

Backing Up Just the Schema (No Data)

pg_dump --schema-only shop > shop_schema.sql

Handy for spinning up an identical, empty structure — a staging or test environment, for instance.

Common Errors & Troubleshooting

• pg_dump: error: connection to database failed — same causes as any connection error: wrong host/port/credentials, or the database name is misspelled.
• Restoring a plain SQL dump errors partway through because the target database/tables already have data — either restore into a fresh, empty database, or drop the conflicting objects first; plain SQL dumps generally don't handle "already exists" gracefully.
• pg_restore seeming to do nothing — double-check you're not accidentally trying to restore a plain-SQL (.sql) dump with pg_restore, which only understands the custom/directory/tar formats — use psql for .sql files instead.

Key Takeaways

• Plain SQL dumps are simple and readable; custom-format (-Fc) dumps are smaller and support selective/partial restores.
• pg_dump backs up one database; pg_dumpall covers every database plus server-wide roles.
• --schema-only gives you the structure without any data — useful for cloning an empty environment.