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.
Top comments (0)