Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL pg_dump

PostgreSQL `pg_dump` is a utility for backing up a PostgreSQL database. It generates a text file with SQL commands that can recreate the database and its objects, making it essential for data backup and migration.

Usage

The `pg_dump` utility is used to create a backup of a PostgreSQL database. It is often employed when you need to migrate data, perform routine backups, or move data between environments.

shell
pg_dump [options] dbname > outfile

In this syntax, `pg_dump` is followed by various options and the `dbname`, indicating which database to back up, with the output redirected to `outfile`. Common options include `-U` to specify the user and `-h` to specify the host, offering flexibility for backing up databases across different environments.

Examples

1. Basic Backup

shell
pg_dump mydatabase > mydatabase.sql

This basic command creates a backup of `mydatabase` and stores it in a file named `mydatabase.sql`.

2. Backup with Compression

shell
pg_dump -Fc mydatabase > mydatabase.dump

This example uses the `-Fc` option to create a custom-format backup, which is compressed and more efficient for large databases. The custom format is flexible for partial restores, allowing selective restoration of database objects.

3. Backup with Specific Table

shell
pg_dump -t mytable mydatabase > mytable.sql

Here, the `-t` option specifies that only the table `mytable` from `mydatabase` should be dumped into `mytable.sql`.

Restoring from a Backup

To restore a backup, you can use `pg_restore` for custom-format files or `psql` for plain SQL files.

Restoring a Plain SQL Backup

shell
psql -U username -d dbname -f mydatabase.sql

Restoring from a Custom Format Backup

shell
pg_restore -U username -d dbname mydatabase.dump

Tips and Best Practices

  • Regular Backups. Schedule regular `pg_dump` operations to ensure you always have up-to-date backups.
  • Use Custom Format for Large Databases. The custom format (`-Fc`) is more efficient and provides flexibility for partial restores, which can be crucial for large databases where you might need to restore only specific parts.
  • Test Your Backups. Regularly restore backups in a test environment to ensure data integrity and completeness.
  • Secure Storage. Store backups in a secure location to protect against unauthorized access and data loss.
  • Monitor Output Size. Keep an eye on the size of your dumps to anticipate storage needs and potential issues.

Troubleshooting Common Issues

  • Connection Problems: Ensure the database server is running and accessible. Use `-h` to specify the host if needed.
  • Permission Denied: Check user permissions and use `-U` to specify a user with the necessary access rights.
  • Disk Space: Monitor available disk space to prevent incomplete backups due to space limitations.