pg_dump vs Oracle Export: A Backup Philosophy Shift

Don’t Treat pg_dump Like expdp

pg_dump and Oracle Data Pump look like the same tool — both take a consistent, logical snapshot of a database. Treating them as identical is how you end up with a backup strategy that works in a demo and fails when you actually need to recover. The philosophy is different, and the differences are exactly where the risk lives.

Logical dumps: the pg_dump family

pg_dump is per-database and, by default, single-threaded plain SQL. The format flag changes everything you can do on restore:

# -Fc : custom (compressed, selective + reorderable restore via pg_restore)
pg_dump -Fc -f app.dump appdb

# -Fd + -j : directory format, parallel dump (fast on big DBs)
pg_dump -Fd -j 8 -f app_dir appdb

# restore selectively / in parallel
pg_restore -d appdb -j 8 app.dump
pg_restore -d appdb -t orders app.dump      # one table
pg_restore -l app.dump                       # list, edit, restore a subset

# roles, tablespaces, other GLOBALS live outside a single-db dump:
pg_dumpall --globals-only -f globals.sql

Two things Oracle DBAs miss here: parallelism needs the directory format (-Fd), and globals aren’t included — roles and tablespaces come from pg_dumpall --globals-only. Restore that first or your grants fail.

Physical backup is your real DR — the RMAN analogue

This is the mindset shift. A logical dump is great for migrations, single-table restores and version upgrades — but it is not your production disaster recovery for a large database, because restoring it means re-running every INSERT and rebuilding every index. For fast recovery and point-in-time recovery, you want a physical base backup plus continuous WAL archiving — PostgreSQL’s answer to RMAN + archived redo.

# physical base backup (block-level, like an RMAN copy)
pg_basebackup -D /backups/base -Ft -z -P

# + archive_command shipping WAL  →  restore to any point in time.
# Tools like pgBackRest / Barman wrap this with retention & incrementals.

Logical vs physical: when to use which

• Logical (pg_dump) — migrations, moving between major versions, restoring a single table, portable snapshots. Consistent to one snapshot automatically.
• Physical (basebackup + WAL) — fast recovery of a large database, standby replicas, and PITR to a specific moment.
• No incremental in pg_dump. Incrementals are a physical/WAL concern — don’t try to fake them with logical dumps.