PostgreSQL Backup and Restore

What Does Backup Mean in PostgreSQL?

Creating a backup means saving a copy of your PostgreSQL data—just in case something gets lost, corrupted, or needs to be moved elsewhere. It's like taking a snapshot of your database for future recovery.

Why It's Crucial

Recover lost content

Clone data to another machine

Test changes safely

Upgrade with confidence

Common Backup Methods

PostgreSQL provides several ways to archive your data. Below are three main tools:

1. Using pg_dump (Single Database Export)

This command extracts one specific database into a file. The output can be plain SQL or a custom format.

pg_dump mydb > mydb_backup.sql

What It Does:

Saves the structure and entries of mydb into a script that can be re-run to recreate the same database.

2. pg_dumpall (Complete Cluster Export)

Want to export every database at once? Use:

pg_dumpall > full_backup.sql

Purpose:

Stores user accounts, roles, and all available databases into one big SQL file.

3. Custom Format Backup

You can also store the backup in a non-text format (e.g., compressed binary) like this:

pg_dump -Fc mydb -f mydb_backup.dump

Benefit:

Faster to restore; lets you reload tables individually or selectively.

Restoring Data

Just as important as saving data is bringing it back when needed.

1. Reloading from SQL Backup

psql mydb < mydb_backup.sql

This feeds the saved script into your existing database.

2. Using pg_restore for Custom Dumps

pg_restore -d mydb mydb_backup.dump

This command takes a binary-style backup and repopulates your database.

Tips Before You Begin

• Always double-check usernames and target database names.
• Ensure the PostgreSQL service is running.
• Use the same or newer PostgreSQL version for restoration.
• Assign proper permissions to prevent failed restores.

Scheduled Backups

To automate backups, use system schedulers like:

• cron on Linux/macOS
• Task Scheduler on Windows

Example crontab entry (daily backup at 2am):

0 2 * * * pg_dump mydb > /backups/mydb_$(date +\%F).sql

Summary Table