Backups are one of the most critical tasks in PostgreSQL administration. Two of the most powerful built-in tools for this are pg_dump and pg_restore. Whether you’re migrating databases, setting up automated backups, or just experimenting locally, mastering these two utilities is absolutely essential!

In this article we'll walk through what pg_dump and pg_restore are, common commands you'll use in real world engineering, and best practices for reliablity and speed.

Backing Up The Database

pg_dump creates a consistent snapshot of a PostgreSQL database. It does not lock the database, and you can run it while the system is live. Here are some commmon output formats you can use with pg_dump:

Format	Flag	Description
Custom	Fc	The most flexible, used with pg_restore. You will use this 80% of the time.
Directory	Fd	Parallel dumps and restores. Best for enterpise level massive databases. You will use 15% of the time.
Tar	Ft	Creates a tar archive. Can be restored with pg_restore but does NOT support parallel restore. You will use barely 5% of the time.
Plain SQL	Fp	Readable SQL file, must use psql to restore. Hardly ever used, small databases when you need to inspect something.

Here are some common pg_dump examples.

Dump an entire database in custom format:

pg_dump -U username -h host -p 5432 -Fc mydb > mydb.dump

Dump a specific table:

pg_dump -U username -h host -t public.customers -Fc mydb > customers.dump

Dump schema only:

pg_dump -U username -s -Fc mydb > schema_only.dump

Dump data only:

pg_dump -U username -a -Fc mydb > data_only.dump

Dump using parallel jobs (directory format):

pg_dump -U username -Fd mydb -j 4 -f backup_dir/

Dump using tar format:

pg_dump -U username -Ft mydb > mydb.tar

Here is a breakdown of the common flags we used in the examples above:

Flag	Meaning	Example	Notes
-U	Username to connect with	-U username	Required when not using the default OS user
-h	Host address	-h host	Can be hostname or IP
-p	Port number	-p 5432	Only needed if not using default port 5432
-Fc	Custom output format	-Fc mydb > mydb.dump	Recommended general-purpose format; used with pg_restore
-Fd	Directory output format	-Fd backup_dir/	Required for parallel jobs using -j
-Fp	Plain SQL output format	-Fp mydb > backup.sql	Readable SQL; restored with psql instead of pg_restore
-t	Dump a specific table	-t public.customers	Can be used multiple times for multiple tables
-n	Dump a specific schema	-n sales	Useful for partial backups or multi-schema databases
-s	Schema-only dump	-s -Fc mydb > schema_only.dump	Dumps only database structure (DDL)
-a	Data-only dump	-a -Fc mydb > data_only.dump	Dumps only table data without schema
-j	Number of parallel jobs	-j 4	Works only with directory format (-Fd)

Restoring The Database

pg_restore allows you to restore PostgreSQL backups created in custom, directory, or tar formats. Unlike plain SQL dumps, these formats support selective restores, parallel jobs, and more control over what gets restored.

Restore into an existing database:

pg_restore -U username -d mydb mydb.dump

Create the database automatically, then restore:

pg_restore -U username --create -d postgres mydb.dump

Restore only a specific table:

pg_restore -U username -d mydb -t public.customers mydb.dump

Restore only the schema (DDL):

pg_restore -U username -d mydb --schema-only mydb.dump

Restore using parallel jobs (directory format only):

pg_restore -U username -d mydb -j 4 backup_dir/

Here is a breakdown of the common flags we used in the pg_restore examples above:

Flag	Meaning	Example	Notes
-U	Username to connect with	-U username	Required when not using the default OS user
-d	Target database to restore into	-d mydb	Must exist unless using --create
--create	Create the database before restoring	--create -d postgres	Restores into a newly created database
-t	Restore a specific table	-t public.customers	Can be used multiple times
--schema-only	Restore only database structure (DDL)	--schema-only mydb.dump	No data restored
-j	Number of parallel jobs	-j 4	Only works with directory format backups

Conclusion

Mastering pg_dump and pg_restore is essential for any PostgreSQL DBA or developer. They provide powerful, flexible options for backing up and restoring your databases with minimal downtime. Here are some best practices to keep in mind:

• Prefer Custom Format (-Fc) for most backups
• Use Directory Format (-Fd) for very large databases
• Store backups off-host (cloud or network storage)
• Automate backups using cron or scheduler
• Test restores regularly
• Compress and encrypt backups for security and to save on space and transfer speeds

In the end, you are only as good as your backups. Take them often, and make sure to test them regularly. Run through “fire drills” by restoring backups in a safe environment so that if an emergency arises, you know exactly how to restore your database, you should be able to do this in your sleep. You don’t want to be in a high-stress situation, scrambling and unsure of the steps. Reliable backups and practiced restores are the biggest difference between a minor hiccup and a major disaster.