pg_dump is an essential tool for creating PostgreSQL backups. This guide highlights key features and examples to streamline your backup process.

SQL Script Backup

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

Restore using:

psql -d new_company -f company_backup.sql

Directory-Format Archive

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

Produces a directory with.dat.gz files.

Export Data Only

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

Export Specific Schemas

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

FAQ

Where does pg_dump output?
Outputs to stdout; redirect with>.

Path to pg_dump.exe?
Found inC:\Program Files\PostgreSQL\<version>\bin\pg_dump.exe.

Can pg_dump be run remotely?
Yes, using-h,-U, and-d options with remote access enabled.

pg_dump vs. pg_dumpall?
pg_dump backs up single databases;pg_dumpall backs up all databases on the server.

Conclusion

pg_dump simplifies PostgreSQL backups. For more detailed guidance, read the articleA Complete Guide to pg_dump With Examples, Tips, and Tricks.