DEV Community

Cover image for PostgreSQL Backup and Restore Workflow for Developers
DbVisualizer
DbVisualizer

Posted on

PostgreSQL Backup and Restore Workflow for Developers

#postgres #backup #restore

PostgreSQL includes built-in tools that make backups and recovery straightforward when used correctly. This overview covers backup creation, restore procedures, and common issues that appear during recovery.

Preparing PostgreSQL for Backup Testing

A simple Docker-based setup makes it easy to practice backup operations.

Key preparation steps:

  • Create a PostgreSQL container
  • Configure database credentials
  • Create a sample database
  • Insert test data 
docker compose -f postgresql.yml up -d
Enter fullscreen mode Exit fullscreen mode

This provides a safe environment for testing backup and restore procedures.

Creating a Backup with pg_dump

The first step in any recovery plan is creating a backup.

A custom-format backup is a common choice because it balances storage efficiency and recovery flexibility.

pg_dump -U dbadmin -F c -f backup.dump surveyplatform
Enter fullscreen mode Exit fullscreen mode

This file can later be restored using pg_restore.

Handling a Failed Restore

Restore failures often reveal missing dependencies rather than damaged backups. A common example is a missing database role. When this happens:

  • Check restore errors carefully
  • Recreate missing roles
  • Remove incomplete restore attempts
  • Retry the restoration 
CREATE ROLE demo;
Enter fullscreen mode Exit fullscreen mode

Resolving ownership issues is frequently enough to complete recovery.

Choosing the Right Backup Format

PostgreSQL supports multiple backup formats. Options include:

  • SQL text backups
  • Custom backups
  • Directory backups
  • TAR backups

Each format targets a different operational need, from debugging to large-scale restoration.

Restoring and Verifying Data

Restoration should always be followed by validation. For SQL backups:

psql -f backup.sql
Enter fullscreen mode Exit fullscreen mode

For custom backups:

pg_restore -d surveyplatform backup.dump
Enter fullscreen mode Exit fullscreen mode

After restoring, run queries against important tables to confirm the expected data exists.

FAQ

Which PostgreSQL backup format should I use for daily backups?

Custom format is often the recommended starting point. It offers compression and flexible recovery options. The format also supports selective restores when needed.

For many workloads, it provides a practical balance between convenience and capability.

How often should I test my PostgreSQL backups?

Backup testing should happen regularly. A monthly restore test can identify problems before they affect production recovery.

Verification queries help confirm backup quality. Testing turns backups into a dependable recovery solution.

Why do I get "role does not exist" errors when restoring backups?

The backup references a database owner that is not available on the target system.

PostgreSQL cannot assign ownership without that role. Creating the missing role resolves the issue. The restore can then complete successfully.

Can I restore a PostgreSQL backup to a different database name?

Yes.

Create the destination database beforehand. Restore the backup into that database. Avoid using options that force the original database name to be recreated.

Conclusion

PostgreSQL's pg_dump and pg_restore tools provide a dependable foundation for backup and recovery. Knowing how to create backups, troubleshoot restores, and verify recovered data helps reduce risk when failures occur. For the complete tutorial, visit PostgreSQL Backup and Restore: Complete Tutorial with pg_dump and pg_restore.

Top comments (0)