pg_dump PostgreSQL Backups: A Quick and Practical Guide

Ensuring a reliable backup of your PostgreSQL database is a crucial part of database management. The pg_dump utility provides a flexible and efficient way to back up databases while maintaining consistency, even when the database is actively in use.

In this guide, we’ll walk through how pg_dump works, its essential commands, and best practices for PostgreSQL backups.

How pg_dump works and why it’s useful

The pg_dump tool extracts a PostgreSQL database into a backup file. This file can later be used to restore the database to the same or another PostgreSQL server.

Why use pg_dump?

• Supports different backup formats (SQL, directory, tar, and custom)
• Ensures data consistency during active usage
• Allows partial backups (select tables, schemas, or data only)
• Works with local and remote databases

With these capabilities, pg_dump is a reliable choice for managing PostgreSQL backups effectively.

Essential pg_dump commands for PostgreSQL backups

Let’s explore some useful pg_dump commands to handle various backup scenarios.

Backup a full database as an SQL file using this command:

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

This command creates an SQL file containing the database structure and data.

Export only the schema without data:

pg_dump -U admin -d company --schema-only -f company_schema.sql

This is useful when you need to recreate the database structure without data.

Dump data only without schema with this command:

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

This creates an SQL file with COPY or INSERT statements for data restoration.

Create a compressed backup in custom format:

pg_dump -U admin -d company -Fc -f company_backup.dump

This backup is stored in a compressed, non-human-readable format and must be restored using pg_restore.

Backup a remote PostgreSQL database over SSH with this command:

ssh user@remote_host "pg_dump -U admin -d company" > company_backup.sql

This ensures a secure backup of a remote database.

FAQ

Where is pg_dump output stored?

By default, pg_dump outputs to stdout. You can specify an output file using the -f flag.

How can I exclude specific tables from a backup?

Use the -T option:

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

This excludes the logs table from the backup.

What’s the difference between pg_dump and pg_dumpall?

• pg_dump backs up a single PostgreSQL database.
• pg_dumpall backs up all databases on the PostgreSQL server, including global roles and settings.

How do I restore a pg_dump backup?

For SQL backups, use:

psql -d new_database -f company_backup.sql

For compressed backups, use:

pg_restore -U admin -d new_database company_backup.dump

Conclusion

The pg_dump utility is an essential tool for any PostgreSQL database administrator. Whether you need simple SQL backups, compressed archives, or selective table exports, pg_dump provides the flexibility to manage backups efficiently.

By understanding the various options and best practices, you can ensure that your PostgreSQL data remains secure and recoverable at all times.

For more advanced techniques and PostgreSQL backup strategies, check out the article A Complete Guide to pg_dump With Examples, Tips, and Tricks.