Logical Backups with pg_dump
A developer runs a migration against production instead of staging and drops the tariffs table. Pedal's cloud host has disk snapshots, but restoring one would roll back the whole server to last night. What you want is last night's copy of one table. That is the job of a logical backup.
The rule
pg_dump saves one database as rebuild instructions, frozen at one moment.
pg_dump connects like any client and writes out what it would take to rebuild one database: CREATE statements for every object, then the data. Three phrases in the rule carry the rules:
- One database. It never includes roles or tablespaces, which belong to the cluster, and never the server's config files.
- Rebuild instructions. Restoring means re-running them: tables are created, rows loaded, indexes rebuilt. That makes dumps portable to a newer major version or another machine, but a restore of a large database is slow.
- Frozen at one moment. The dump reads from a single snapshot, so it is consistent even while the app keeps writing. Thanks to MVCC it does not block normal reads or writes. It does hold light locks that make
ALTER TABLEorDROP TABLEwait until it finishes.
# custom format: compressed, restore selectively with pg_restore
pg_dump -h db1 -U pedal_backup -d pedal -Fc -f pedal-2026-09-25.dump
# directory format, four parallel jobs for a bigger database
pg_dump -d pedal -Fd -j 4 -f pedal-2026-09-25.dir
# plain SQL, readable, restored with psql
pg_dump -d pedal -Fp -f pedal-2026-09-25.sql
# just the schema, or just one table
pg_dump -d pedal --schema-only -f pedal-schema.sql
pg_dump -d pedal -t public.tariffs -Fc -f tariffs.dump| Format | Flag | Restore with | Parallel |
|---|---|---|---|
| Plain SQL | -Fp |
psql |
no |
| Custom | -Fc |
pg_restore |
restore only |
| Directory | -Fd |
pg_restore |
dump and restore |
| Tar | -Ft |
pg_restore |
no |
The dumping role needs to read every object it dumps. The predefined role pg_read_all_data covers table data; alternatively the owner role runs it.
The shortcut
Before choosing a backup, ask "What will I need to get back, and how fast?" One table or one database, portable across versions → pg_dump. The whole server, fast, or to an exact minute → a physical backup, later in this section.
What do we need?
One table from one database, as it was last night.
The trap is thinking a nightly dump is a full server backup. Without roles, a restored database has objects whose owners and grantees do not exist. Pair it with pg_dumpall --globals-only, covered next.
Drills
Answer fast — trust the rule.
Which of these does pg_dump -d pedal NOT include?
Does a running pg_dump stop the app from inserting rentals?
Which format lets both the dump and the restore run in parallel?
Why are logical dumps a common way to move to a new major version?
Remember
- pg_dump saves one database as rebuild instructions, frozen at one moment.
- Ask what you need back and how fast: one database and portable → pg_dump.
- It never includes roles; pair it with a globals dump.