Backup Databases with pg_dump

You've spent hours designing tables, writing queries, and tuning indexes — but have you ever stopped to ask what happens if your database server dies, a disk corrupts, or someone accidentally runs DROP TABLE in the wrong environment? Without a reliable backup strategy, all that work can vanish in an instant. In this lesson, you'll learn how to back up databases with pg_dump, the standard PostgreSQL utility that lets you create logical, portable backups with a single command. By the end, you'll be able to protect your data with confidence and seamlessly restore it when disaster strikes.

Why Your Database Needs a Backup Strategy Now

Let's face it: most developers don't think about backups until they need one — and by then, it's often too late. Database backups are not a 'nice-to-have' feature; they are a critical part of any application lifecycle. Here's why you should care right now:

• Accidental deletions: A mistyped DELETE or a misapplied migration can wipe out thousands of rows.
• Hardware failures: Drives crash, servers overheat, and cloud instances get terminated — without a backup, your data is gone forever.
• Security incidents: Ransomware or a compromised admin can encrypt or delete your data; a recent backup is your safest recovery path.
• Migration and testing: You'll often need a copy of production data to test schema changes or populate a staging environment.

If you're already nodding, you might be wondering: 'But PostgreSQL has replication and point-in-time recovery — do I really need pg_dump?' Yes, you do. Replication protects against hardware failure but not against logical errors like accidental updates or deletions — those get replicated too. A logical backup created with pg_dump gives you a clean snapshot that you can restore anywhere, independent of server crashes or user mistakes.

By the end of this lesson, you'll understand not just how to run pg_dump, but why it works the way it does — so you can choose the right backup strategy for your projects.

Mental Model: Database Backups as Snapshots and Copies

Think of a database as a living document you're writing in real time. A backup is like taking a photograph of that document at a specific moment. You can put the photo in a safe place, and if the original is torn, burned, or lost, you can use the photo to recreate the document exactly as it looked when the photo was taken.

For PostgreSQL, pg_dump is that camera. It produces a logical backup — a file containing the SQL statements needed to recreate your database structure (tables, indexes, constraints) and the data itself. This is different from a physical backup (like copying the data directory files), but the concept of a snapshot remains the same.

Let's define a few core terms you'll meet throughout this lesson:

• Logical backup: A backup that uses SQL commands to describe data and schema (pg_dump output). It's portable across PostgreSQL versions and even across different operating systems.
• Physical backup: A backup of the raw files that make up the database cluster. Tools like pg_basebackup do this. It's faster for huge databases but not portable across major versions.
• Snapshot: A point-in-time representation of the database state. pg_dump ensures a consistent snapshot by using a single transaction by default.
• Restore: The process of feeding the backup file back into PostgreSQL to recreate the database (psql for plain SQL dumps, pg_restore for custom or tar formats).

Here's a simple diagram in words of the backup and restore flow:

[PostgreSQL Database] --pg_dump--> [backup.sql] --psql/pg_restore--> [Restored Database]

The backup is a blueprint plus the furniture: everything needed to rebuild the room.

Why Use a Logical Backup?

A logical backup from pg_dump is portable — it can be restored on a newer PostgreSQL version, a different architecture, or even a different database engine (with modifications). It also gives you fine-grained control: you can back up a single table, a schema, or the whole database.

Now that you have the mental model, let's see how to put it into practice.

How it Works Step by Step: From Data to Safe File

pg_dump works by connecting to your running PostgreSQL server and extracting the database's logical structure and data into a file. Here's the high-level sequence:

1. Connect to the server: You provide credentials (host, port, user) either via command-line options or environment variables like PGHOST, PGPORT, PGUSER.
2. Snapshot the database: By default, pg_dump starts a single transaction, creating a consistent snapshot at a point in time even if the database is actively being used.
3. Extract schema: It first dumps DDL statements (CREATE TABLE, CREATE INDEX, and so on) to recreate the structure.
4. Extract data: Then it dumps the actual rows as COPY or INSERT statements.
5. Write to file: The output goes to stdout by default, which you redirect to a file, or you can specify a file with -f.

Let's break down the most important options you'll use:

• -U — the user name to connect as
• -h — the host (use localhost or a remote IP)
• -p — the port (default 5432)
• -d — the database name to back up
• -f — the output file name (or use redirection)
• -F — the output format: p (plain SQL), c (custom), d (directory), t (tar)
• -t — to dump only a specific table
• --clean — add DROP ... statements to clean before recreating objects on restore

Understanding the Output Formats

Now let's see these steps in action.

Hands-On Walkthrough: Your First pg_dump Backups

Let's get practical. I assume you have PostgreSQL installed and a database named mydb that you'd like to back up. Open your terminal and follow along.

1. Create a Sample Database (If You Don't Have One)

If you're using the same server from previous lessons, you might already have a database. Otherwise, you can create a quick one:

CREATE DATABASE mydb;
\c mydb
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');

2. Basic Backup to a Plain SQL File

Run the most straightforward command:

pg_dump -U postgres -d mydb -f mydb_backup.sql

You'll be prompted for the password if needed. After completion, take a peek at the file:

head mydb_backup.sql

You should see SQL statements starting with -- comments and SET commands, followed by table creation and data COPY statements. That's your snapshot in plain text.

3. Restore from the Backup

To verify the backup works, let's restore it into a new database:

createdb -U postgres mydb_restore
psql -U postgres -d mydb_restore -f mydb_backup.sql

Now check that the data is there:

psql -U postgres -d mydb_restore -c "SELECT * FROM users;"

You should see the two users. That's it — backup and restore in minutes.

4. Use the Custom Format for Flexible Restores

The plain SQL format is fine for small databases, but the custom format (-F c) offers more power. It's compressed and supports selective restore and parallel restore.

pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

To restore, use pg_restore:

pg_restore -U postgres -d mydb_restore mydb_backup.dump

With the custom format, you can also restore only specific tables:

pg_restore -U postgres -d mydb_restore -t users mydb_backup.dump

5. Automate with a Script (Bonus)

For production, you'll want to automate backups. Here's a simple bash script that creates timestamped backups:

#!/bin/bash
BACKUP_DIR="/var/backups/postgres"
DB_NAME="mydb"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
pg_dump -U postgres -d $DB_NAME -F c -f "$BACKUP_DIR/${DB_NAME}_$TIMESTAMP.dump"
echo "Backup created: $BACKUP_DIR/${DB_NAME}_$TIMESTAMP.dump"

Add it to cron, and you're set. Remember to keep backups on a separate storage or cloud bucket — not the same disk as your database.

Expected Output

For the basic pg_dump command, you won't see output unless there's an error — success is silent. For the restore with psql, you'll see various CREATE TABLE and COPY messages. For pg_restore, it's similar. That silence is a good thing; it means your backup is clean.

Pro tip: Always test your backups by restoring them into a scratch database. A backup that can't be restored is just a collection of bytes.

Compare Options: When to Use Which Format

Now that you've seen both, let's compare the most common backup approaches you'll consider.

For most development and small production databases, custom format is a great default because it offers compression and flexibility. For tiny side projects, plain SQL is fine. For huge enterprise systems, directory format with pg_basebackup (physical backup) might be better — we'll mention that in variations.

Pro tip: If you need to back up all databases on a server, use pg_dumpall. It also includes global data like roles and tablespaces.

Troubleshooting & Edge Cases

Even with a straightforward tool like pg_dump, things can go wrong. Here are the most common issues and how to fix them:

Permission Denied

Symptom: pg_dump: error: permission denied for table users

Cause: The user you're connecting as doesn't have SELECT privilege on the tables. Fix: Grant the necessary privileges or connect as a superuser:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

Connection Refused / Host Not Resolved

Symptom: pg_dump: error: could not connect to server: Connection refused

Cause: The server isn't running, or the host/port is wrong. Fix: Verify PostgreSQL is running (pg_isready), and check host and port flags:

pg_dump -h 127.0.0.1 -p 5433 -U postgres mydb

Role Does Not Exist

Symptom: pg_dump: error: role "username" does not exist

Cause: The user you specified doesn't exist on the server. Fix: Create the role or use an existing user. For remote connections, ensure you use the correct username.

Password Prompt Interrupts Automation

Symptom: When running in cron, you get stuck at a password prompt.

Fix: Use a ~/.pgpass file (with permissions 600) or set PGPASSWORD environment variable (not recommended for security reasons in shared environments).

# ~/.pgpass
localhost:5432:mydb:postgres:supersecret

Disk Space Full

Symptom: The backup fails halfway with write error: No space left on device.

Fix: Check available disk space with df -h, and either clean up or use a different location. Compression with -F c can help reduce file size.

Wrong Database Name

Symptom: pg_dump: error: database "mydb" does not exist

Fix: Double-check the spelling and use \l in psql to list databases.

Restore Errors About Missing Extensions

Symptom: During restore, you get ERROR: extension "uuid-ossp" does not exist.

Fix: Ensure required extensions are installed on the target server, or use the --no-owner option if permissions differ. For example:

psql -U postgres -d mydb_restore -f mydb_backup.sql --set=ON_ERROR_STOP=1

Then manually create the extension first.

Pro tip: When restoring, use --clean if you want to drop existing objects before recreating them. This is useful when restoring into a database that already has the same tables.

What You Learned & What's Next

Congratulations! You've now mastered the core skill of backing up PostgreSQL databases with pg_dump. Let's recap what you accomplished:

• You understand why backups are critical — from accidental mistakes to hardware failures.
• You can explain the mental model of a backup as a consistent snapshot.
• You learned how pg_dump works step by step, from connecting to the database to producing a logical backup file.
• You practiced creating backups in plain SQL and custom formats, and restored them successfully.
• You can compare different backup options and choose the right one for your scenario.
• You're equipped to troubleshoot common issues and edge cases.

These skills directly support the core learning objectives of this lesson — not only do you know how to execute a backup, but you also understand the underlying mechanics, which helps you make informed decisions in production.

Your next step in this track is likely to explore more advanced backup and recovery techniques, such as point-in-time recovery (PITR) using WAL archiving, or setting up streaming replication for high availability. These build on the foundation you've just laid — a solid backup strategy is the first line of defense.

Before moving on, try this mini exercise: create a backup of your mydb database in all four formats (p, c, d, t), inspect the file sizes, and practice restoring each into a separate database. This hands-on practice will solidify your understanding and highlight the trade-offs between formats.

Remember: a backup you've never restored is just a prayer. Keep practicing, and you'll be ready for whatever database disaster comes your way.