PostgreSQL Backup: Tips & Tricks

Tip: pg_dump for Logical Backups

pg_dump -U myuser -d myapp -Fc > backup.dump

The -Fc format is compressed and supports selective restore.

Gotcha: pg_dump Doesn't Lock Tables

But it does see a consistent snapshot. Long-running dumps can hold back VACUUM.

Tip: Automated Backup Script

#!/bin/bash
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U myuser -d myapp -Fc > /backups/myapp_$DATE.dump
find /backups -name '*.dump' -mtime +7 -delete

Run daily via cron.

Gotcha: Restore Needs the Same Extensions

pg_restore -U myuser -d myapp backup.dump

If the dump includes extensions, they must be available on the target server.

Tip: Point-in-Time Recovery

wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

With WAL archiving, you can restore to any point in time.

Gotcha: pg_dump Doesn't Include Roles

Roles and permissions are cluster-level. Back them up separately:

pg_dumpall --roles-only > roles.sql

Tip: Spatie Laravel Backup Package

composer require spatie/laravel-backup
php artisan backup:run

Handles database + files + cleanup scheduling.

Gotcha: Test Your Backups

A backup you haven't tested is not a backup. Restore to a staging database regularly.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

PostgreSQL backup strategies depend entirely on your recovery requirements. pg_dump is reliable for logical backups, but restoring a multi-terabyte database can take days. I've adopted a layered approach: daily pg_dump for schema and small tables, continuous WAL archiving for point-in-time recovery, and physical replication for failover. Test your restore procedure quarterly — an untested backup is not a backup.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)