Mastering PostgreSQL Backups with pg_dump

pg_dump is a powerful PostgreSQL tool for creating backups. This guide provides a concise overview, with examples, on how to use pg_dump to manage your data effectively.

Dump a Database Into an SQL Script Format:

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

Creates a company_backup.sql file. To restore:

psql -d new_company -f company_backup.sql

Directory-Format Archive:

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

Produces a company_backup folder with .dat.gz files.

Export Data Only:

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

Include Only Some Schemas:

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

FAQ

Where does the output of pg_dump go?

By default, pg_dump outputs to stdout. Redirect to a file using >.

What is the path to pg_dump.exe?

On Windows, it’s usually in C:\\Program Files\\PostgreSQL\\<version>\\bin\\pg_dump.exe.

Can pg_dump run from a remote server?

Yes, use the -h, -U, and -d options and ensure remote connections are allowed.

pg_dump vs. pg_dumpall?

pg_dump backs up individual databases, while pg_dumpall backs up all databases on a server.

Short summary

pg_dump is essential for PostgreSQL backups. For more details and advanced options, read the article A Complete Guide to pg_dump With Examples, Tips, and Tricks.