How to Backup PostgreSQL Database: Simple Guide

To backup a PostgreSQL database, use the pg_dump command-line tool which exports the database to a file. You can run pg_dump -U username -F c -b -v -f backup_file database_name to create a compressed backup file safely.

Syntax

The basic syntax of pg_dump is:

• pg_dump -U username -F format -b -v -f output_file database_name

Where:

• -U username: specifies the database user.
• -F format: sets the backup format, e.g., c for custom (compressed), p for plain SQL.
• -b: includes large objects in the backup.
• -v: verbose mode, shows progress.
• -f output_file: file path to save the backup.
• database_name: the name of the database to backup.

bash

pg_dump -U username -F c -b -v -f backup_file database_name

Example

This example backs up a database named mydb using user postgres into a compressed file mydb_backup.dump. It shows progress in the terminal.

bash

pg_dump -U postgres -F c -b -v -f mydb_backup.dump mydb

Output

pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: dumping contents of table public.mytable
pg_dump: finished item
pg_dump: finished item

Common Pitfalls

Common mistakes when backing up PostgreSQL databases include:

• Not specifying the correct user with -U, causing authentication errors.
• Using plain SQL format (-F p) without compression, resulting in large files.
• Forgetting to include large objects with -b, losing binary data.
• Running pg_dump without proper permissions or while the database is locked.

Always test your backup by restoring it to ensure it works.

bash

Wrong: pg_dump -U wronguser -f backup.sql mydb
Right: pg_dump -U correctuser -F c -b -v -f backup.dump mydb

Quick Reference

Option	Description	Example
-U username	Database user to connect as	-U postgres
-F format	Backup format: c=custom, p=plain SQL	-F c
-b	Include large objects	-b
-v	Verbose mode	-v
-f file	Output file path	-f backup.dump
database_name	Name of the database to backup	mydb

Key Takeaways

Use pg_dump with the -F c option for compressed backups.

Always specify the correct user with -U to avoid authentication errors.

Include large objects with -b to backup all data.

Test backups by restoring them to ensure data safety.

Use verbose mode -v to monitor backup progress.