How to Backup and Restore PostgreSQL Databases

Data is one of the most valuable assets in any system. Hardware failures, human errors, cyberattacks, or software bugs can happen at any time, and without proper backups, data loss can be catastrophic. PostgreSQL provides powerful built-in tools that make backing up and restoring databases reliable and flexible.

In this article, you will learn the different backup strategies available in PostgreSQL, how to perform backups using command-line tools, and how to restore databases safely in various scenarios.

Why Backup and Restore Are Critical in PostgreSQL

Backups are not optional in production systems. A good backup strategy ensures:

• Protection against data loss
• Faster disaster recovery
• Safe database migrations
• Easy testing and development cloning
• Compliance with security and audit requirements

PostgreSQL offers logical and physical backups, each suited to different use cases.

Logical Backups in PostgreSQL

Logical backups extract database objects such as tables, data, and schemas into a portable format. They are created using pg_dump and pg_dumpall.

When to Use Logical Backups

• Small to medium databases
• Migrating between PostgreSQL versions
• Restoring individual tables or schemas
• Cross-platform compatibility

Using pg_dump for Database Backup

pg_dump is the most commonly used PostgreSQL backup tool.

Basic example:

pg_dump -U postgres -d mydb > mydb_backup.sql

Common options:

• -F c – Custom format
• -f – Output file
• -v – Verbose mode

Custom format example:

pg_dump -U postgres -F c -f mydb.backup mydb

Custom format backups support parallel restore and selective recovery.

Backing Up Specific Objects

You can back up individual schemas or tables.

Backup a schema:

pg_dump -U postgres -n public mydb > public_schema.sql

Backup a table:

pg_dump -U postgres -t users mydb > users_table.sql

This is useful for partial restores or targeted recovery.

Using pg_dumpall for Full Cluster Backup

pg_dumpall backs up:

• All databases
• Roles and users
• Tablespaces

Example:

pg_dumpall -U postgres > full_cluster_backup.sql

This is useful for complete system recovery but does not support parallel restore.

Restoring Logical Backups

Restoring from SQL format:

psql -U postgres -d mydb < mydb_backup.sql

Restoring from custom format:

pg_restore -U postgres -d mydb mydb.backup

Selective restore example:

pg_restore -U postgres -t users mydb.backup

Always restore into an empty or newly created database for best results.

Physical Backups in PostgreSQL

Physical backups copy the actual data files used by PostgreSQL. These backups are faster and ideal for large databases.

When to Use Physical Backups

• Large production databases
• Point-in-Time Recovery (PITR)
• High availability systems

Using pg_basebackup

pg_basebackup creates a binary copy of the database cluster.

Example:

pg_basebackup -U replicator -D /backup/pgdata -Fp -Xs -P

Key options:

• -Fp – Plain format
• -Xs – Include WAL files
• -P – Progress display

This tool is commonly used in replication and disaster recovery setups.

Point-in-Time Recovery (PITR)

PITR allows you to restore a database to a specific moment in time.

Requirements:

• Base backup
• WAL archiving enabled
• Restore configuration

This method is essential for recovering from accidental data deletion.

Backup Automation and Scheduling

Manual backups are risky. Automate backups using:

• Cron jobs
• Backup scripts
• Monitoring alerts

Best practices:

• Daily full backups
• Regular restore testing
• Off-site storage
• Backup rotation policies

A backup is only useful if it can be restored.

Security Best Practices for Backups

• Encrypt backup files
• Restrict access permissions
• Mask sensitive data when needed
• Store backups securely off-server
• Monitor backup failures

Backups often contain sensitive data and must be protected.

Common Backup and Restore Mistakes

Avoid these common pitfalls:

• Not testing restore procedures
• Storing backups on the same server
• Ignoring WAL files
• Using outdated backups
• Lacking documentation

Reliable backups require discipline and regular validation.

Choosing the Right Backup Strategy

Scenario	Recommended Method
Small database	pg_dump
Large database	pg_basebackup
Full cluster	pg_dumpall
PITR required	Physical backup + WAL
Migration	Logical backup

Choose based on data size, recovery needs, and infrastructure.

Conclusion

Backing up and restoring PostgreSQL databases is a fundamental responsibility for any database administrator or developer. PostgreSQL’s built-in tools provide flexible options for both logical and physical backups, making it possible to protect data at any scale.

By implementing a solid backup strategy, automating processes, and regularly testing restores, you can ensure that your PostgreSQL databases remain safe, recoverable, and reliable in any situation.