Syed Jafer K

When you run a database, your data is everything. It powers your applications, analytics, and often, your entire business.
Now imagine a sudden power failure, a disk crash, or a developer accidentally executing DROP DATABASE.

That’s why backups are not optional they are your safety net.

In this article, we’ll break down PostgreSQL backups from the ground up: what they are, their types, how they work, and how to restore them all explained in plain, practical language.

What Is a Backup

A backup is simply a copy of your data at a specific point in time.
If your database crashes or becomes corrupted, you can use this backup to restore it.

In PostgreSQL, backups can be taken in several ways depending on your requirements whether you want a logical copy (like exporting data to SQL) or a physical copy of the database files.

Types of Backups

PostgreSQL supports two main types of backups,

1. Logical Backups

Logical backups export your database as SQL statements.
When restored, PostgreSQL replays these SQL statements to recreate the data and schema.

Tools

• pg_dump → for a single database
• pg_dumpall → for all databases in the cluster with roles, configurations.

A logical backup file looks something like this,

CREATE TABLE users (...);
INSERT INTO users VALUES (...);

When you restore it, PostgreSQL executes these commands to rebuild the database.

Advantages

• Easy to move between PostgreSQL versions
• Can backup specific tables or schemas
• Portable across systems

Disadvantages

• Slow for large databases
• Restoration can take time as it replays every SQL statement

2. Physical Backups

Physical backups are exact copies of PostgreSQL’s data directory in binary form.
They include everything—data files, WAL logs, and configuration files.

Tools and Methods

• pg_basebackup (recommended)
• File system-level backups using rsync or similar tools
• Storage snapshots (for example, AWS EBS snapshots)

This type of backup copies the entire /var/lib/postgresql/data directory.

Advantages

• Fast to restore
• Supports Point-In-Time Recovery (PITR)
• Ideal for production systems

Disadvantages

• Not human-readable
• Tied to the same PostgreSQL version and architecture.

Using pg_dump for Logical Backups

The most common way to take a backup is using pg_dump.

Example,

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

Explanation

• -U postgres: connect as the postgres user
• -F c: use the custom compressed format
• -f: specify output file
• mydb: name of the database

To restore this backup,

pg_restore -U postgres -d mydb_restored mydb_backup.dump

To back up a single table,

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

Using pg_basebackup for Physical Backups

For complete physical backups, use the pg_basebackup command.

pg_basebackup -U postgres -D /backups/pgdata -Fp -Xs -P

Explanation

• -D: destination directory
• -Fp: plain file format
• -Xs: include WAL files
• -P: show progress

This creates a full copy of the PostgreSQL data directory, including WAL files, allowing for easy restoration.

To restore

1. Stop PostgreSQL.
2. Replace the existing data directory with the backup.
3. Start PostgreSQL again.

Point-In-Time Recovery (PITR)

Sometimes, you may need to restore the database to a specific moment in time for example, just before an accidental table drop.

This is possible using Point-In-Time Recovery (PITR).

Steps:

1. Take a base backup using pg_basebackup.
2. Enable WAL archiving in postgresql.conf

archive_mode = on
archive_command = 'cp %p /path_to_wal_archive/%f'

3. When restoring, use your base backup and replay WAL logs up to the desired time.

Set recovery target,

recovery_target_time = '2025-11-05 14:25:00'

Start PostgreSQL, and it will recover to that exact timestamp.

I have tried both logical backups and physical backups but not hybrid ones.

Backups may seem unimportant until something goes wrong. The moment your production database crashes, you will appreciate the time you invested in setting up proper backups.

Take backups. Automate them. Test them. In PostgreSQL, durability is peace of mind.

Do you have any nightmares on backups in postgres ? Please share in comments we can discuss.