How to Use pg_dump in PostgreSQL: Backup Your Database Easily

Use the pg_dump command-line tool to create a backup of a PostgreSQL database by specifying the database name and output file. For example, pg_dump mydb > backup.sql saves the database mydb to a file called backup.sql.

Syntax

The basic syntax of pg_dump is:

• pg_dump [options] dbname - Dumps the database named dbname.
• -f filename - Specifies the output file to save the dump.
• -U username - Connects as the specified user.
• -h hostname - Connects to the specified host.
• -p port - Connects to the specified port.
• -F format - Sets the output format (plain, custom, directory, tar).

This command creates a backup of the database that you can restore later.

bash

pg_dump [options] dbname

# Example:
pg_dump -U postgres -h localhost -p 5432 -F c -f backup.dump mydb

Example

This example shows how to back up a database named mydb to a plain SQL file called backup.sql. It connects as user postgres on the local machine.

bash

pg_dump -U postgres -h localhost -p 5432 -F p -f backup.sql mydb

Common Pitfalls

Common mistakes when using pg_dump include:

• Not specifying the correct user with -U, causing authentication errors.
• Forgetting to specify the output file with -f, which sends the dump to standard output.
• Using the wrong format option, which may cause restore issues.
• Not having proper permissions to access the database.

Always check connection details and permissions before running pg_dump.

bash

Wrong usage (no output file):
pg_dump mydb

Right usage (with output file):
pg_dump -U postgres -f backup.sql mydb

Quick Reference

Option	Description	Example
-U username	Connect as specified user	pg_dump -U postgres mydb
-h hostname	Connect to specified host	pg_dump -h localhost mydb
-p port	Connect to specified port	pg_dump -p 5432 mydb
-f filename	Write output to file	pg_dump -f backup.sql mydb
-F format	Set output format (p, c, d, t)	pg_dump -F c -f backup.dump mydb

Key Takeaways

Use pg_dump with the database name and output file to back up your PostgreSQL database.

Specify connection options like -U, -h, and -p to connect properly.

Choose the right output format with -F depending on your restore needs.

Always verify you have the correct permissions and connection details before dumping.

Remember to save the dump output to a file using -f to avoid losing the backup.