PostgreSQL

Backup/Restore Commands

PostgreSQL provides robust backup tools. Learn to create consistent backups and restore them efficiently using pg_dump and pg_restore.

11 commands

Pro Tips

Use -Fc (custom format) for smaller, faster, and parallel-restorable backups.

Use -j N with pg_restore for parallel restore, significantly faster on large DBs.

Common Mistakes

pg_dump creates consistent snapshots, but large databases may affect performance.

Commands

Dump database

$ pg_dump -U postgres mydb > backup.sql

Export database to SQL file.

Dump compressed

$ pg_dump -Fc -U postgres mydb > backup.dump

Export in custom compressed format.

Dump table

$ pg_dump -t users -U postgres mydb > users.sql

Export specific table.

Dump schema only

$ pg_dump -s -U postgres mydb > schema.sql

Export schema without data.

Dump data only

$ pg_dump -a -U postgres mydb > data.sql

Export data without schema.

Dump all databases

$ pg_dumpall -U postgres > all_dbs.sql

Export all databases including roles.

Restore SQL

$ psql -U postgres mydb < backup.sql

Restore from SQL file.

Restore custom format

$ pg_restore -U postgres -d mydb backup.dump

Restore from custom format dump.

Restore parallel

$ pg_restore -j 4 -U postgres -d mydb backup.dump

Parallel restore with 4 jobs.

Physical backup with progress

$ pg_basebackup -D /backup/dir -Ft -z -P

Create compressed tar backup with progress display.

Streaming basebackup

$ pg_basebackup -D /backup -F p -X stream

Physical backup with streaming WAL.