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.