PostgreSQL Backups Simplified with pg_dump

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 in C:\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 article A Complete Guide to pg_dump With Examples, Tips, and Tricks.