Efficient PostgreSQL Backups with pg_dump

pg_dump is a key tool for PostgreSQL database backups. This guide offers a succinct summary with practical examples to help you get started.

SQL Script Format

pg_dump -U admin -d company -f company_backup.sql

Restore with:

psql -d new_company -f company_backup.sql

Directory-Format Archive

pg_dump -U admin -d company -F d -f company_backup

Creates a folder with .dat.gz files.

Data Only Export

pg_dump -U admin -d company -f company_backup.sql --data-only

Schema Specific Export

pg_dump -U admin -d company -n 'p*' -f company_backup.sql

FAQ

Output location of pg_dump?

Outputs to stdout by default; redirect with >.

Location of pg_dump.exe?

Typically found in C:\\Program Files\\PostgreSQL\\<version>\\bin\\pg_dump.exe.

Running pg_dump from a remote server?

Yes, with -h, -U, -d options and remote access permissions.

Difference between pg_dump and pg_dumpall?

pg_dump is for single databases; pg_dumpall covers all databases on a server.

Summary

Master pg_dump for PostgreSQL backups. For an in-depth guide, read the article A Complete Guide to pg_dump With Examples, Tips, and Tricks.