Logical Backups with pg_dump
Dump schemas and tables in plain, custom, and directory formats — and understand why -Fc is the default that scales
pg_dump takes a logical backup: a script or archive that reconstructs the objects it captured, statement by statement, rather than copying raw data files. That makes it portable across PostgreSQL versions and even operating systems — a logical dump taken from this VM can be restored into a brand-new major version running on completely different hardware, something a physical backup can never do.\n\npg_dump supports several output formats. Plain format is a flat SQL script — readable, greppable, but monolithic: restoring it means feeding the whole file through psql from top to bottom. Custom format (-Fc) is a compressed, structured archive that pg_restore can filter, reorder, and parallelize. Directory format (-Fd) splits that same structure into one file per table inside a directory, which is what unlocks true parallel dumping with --jobs — plain and custom format are always single-threaded on the dump side, no matter how many CPUs are available.\n\nIn this lab you will dump the same schema three ways, verify a custom-format dump without restoring anything, and measure what --jobs actually buys you once a schema has enough tables to parallelize across.
Locate Your Tools Before You Need Them
pg_dump --version
pg_restore --version
Confirming these exist and match your server version takes five seconds now. Discovering they are missing, or a mismatched version, in the middle of an actual incident costs a lot more.
Three Formats, Three Trade-offs
# Plain: a flat SQL script
pg_dump -U postgres -d beer_db --schema=inventory -f /tmp/inventory_plain.sql
# Custom: compressed, structured, filterable — the default choice
pg_dump -U postgres -d beer_db --schema=inventory -Fc -f /tmp/inventory.dump
# Directory: one file per table — the only format that dumps in parallel
pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 4 -f /tmp/inventory_dir
Plain format can only be restored by replaying the whole script with psql -f. Custom and directory format are restored with pg_restore, which can filter by table, reorder objects, and run in parallel — plain format can do none of that.
Filtering: Schema and Table
# One schema only
pg_dump -U postgres -d beer_db --schema=inventory -Fc -f /tmp/inventory.dump
# One table only
pg_dump -U postgres -d beer_db --table=inventory.products -Fc -f /tmp/products_only.dump
Parallel Dumps with --jobs
Only directory format supports --jobs / -j. pg_dump forks one worker process per job, each dumping a different table concurrently:
time pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 1 -f /tmp/inventory_dir1
time pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 4 -f /tmp/inventory_dir4
The speedup depends on having enough tables of meaningful size to spread across workers — a schema with one huge table and nothing else barely benefits from -j at all, since that one table is still dumped by a single worker.
Verifying a Dump Without Restoring It
pg_restore --list /tmp/inventory.dump
This prints the dump's table of contents — every object it captured — without touching any database. It is the fastest way to confirm a dump is structurally valid immediately after taking it, before you ever need it for a real restore.
Logical vs. Physical Backup
A logical backup (pg_dump) captures a database as a sequence of commands that reconstruct its objects — portable across PostgreSQL versions and platforms, but only as fast as replaying those commands. A physical backup (pg_basebackup, covered in Lab 2.3.4) copies the actual data files — faster for large databases, but tied to the exact same major version and architecture it came from.
Custom Format (-Fc)
A compressed, PostgreSQL-specific archive format that stores each database object as a separate entry with dependency metadata. Unlike plain SQL, it can be filtered, reordered, and partially restored by pg_restore without ever touching the parts you did not ask for.
--jobs / Parallel Dump
Only directory format supports parallel dumping. Each job is a separate worker process dumping a different table at the same time — the total wall-clock time approaches the size of the single largest table, not the sum of every table.
🔧 Confirm Your Tools
Check that pg_dump and pg_restore are present on this system and note their version before relying on them.
pg_dump --versionstudent@lab:~$ pg_dump --version pg_dump (PostgreSQL) 18.4
🔎 Orient: What Is in inventory?
List the tables in the inventory schema and their row counts before dumping anything.
psql -U postgres -d beer_db -c "SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname = 'inventory' ORDER BY relname;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname = 'inventory' ORDER BY relname;" SET relname | n_live_tup -----------------+------------ product_reviews | 80000 products | 20000 stock_movements | 100000 (3 rows)
📄 Plain-Format Dump
Dump the inventory schema in plain SQL format, then look at the raw file — it is just text.
pg_dump -U postgres -d beer_db --schema=inventory -f /tmp/inventory_plain.sqlhead -n 15 /tmp/inventory_plain.sqlstudent@lab:~$ pg_dump -U postgres -d beer_db --schema=inventory -f /tmp/inventory_plain.sql student@lab:~$ head -n 15 /tmp/inventory_plain.sql -- -- PostgreSQL database dump -- \restrict a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6 -- Dumped from database version 18.4 -- Dumped by pg_dump version 18.4 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET transaction_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on;
📦 Custom-Format Dump
Dump the same schema in custom format — the recommended default for nearly every use case.
pg_dump -U postgres -d beer_db --schema=inventory -Fc -f /tmp/inventory.dumpstudent@lab:~$ pg_dump -U postgres -d beer_db --schema=inventory -Fc -f /tmp/inventory.dump
📏 Compare File Sizes
Compare the plain and custom dump file sizes — custom format is compressed by default.
ls -lh /tmp/inventory_plain.sql /tmp/inventory.dumpstudent@lab:~$ ls -lh /tmp/inventory_plain.sql /tmp/inventory.dump -rw-r--r-- 1 student student 1.2M Jun 26 16:04 /tmp/inventory.dump -rw-r--r-- 1 student student 11.0M Jun 26 16:04 /tmp/inventory_plain.sql
⏱️ Directory Format, Single-Threaded
Dump the schema in directory format with a single job, timed — this is your baseline.
time pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 1 -f /tmp/inventory_dir1student@lab:~$ time pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 1 -f /tmp/inventory_dir1 real 0m 4.71s user 0m 1.43s sys 0m 0.04s
⚡ Directory Format, Parallel
Repeat the same dump with 4 parallel jobs and compare the elapsed time.
time pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 4 -f /tmp/inventory_dir4student@lab:~$ time pg_dump -U postgres -d beer_db --schema=inventory -Fd -j 4 -f /tmp/inventory_dir4 real 0m 4.88s user 0m 1.43s sys 0m 0.07s
🎯 Dump a Single Table
Dump only the products table in custom format — useful when you need to restore or migrate one object, not an entire schema.
pg_dump -U postgres -d beer_db --table=inventory.products -Fc -f /tmp/products_only.dumpstudent@lab:~$ pg_dump -U postgres -d beer_db --table=inventory.products -Fc -f /tmp/products_only.dump
🔍 Verify Without Restoring: pg_restore --list
List the contents of the custom-format schema dump without restoring anything, to confirm it is structurally valid.
pg_restore --list /tmp/inventory.dumpstudent@lab:~$ pg_restore --list /tmp/inventory.dump ; ; Archive created at 2026-06-26 16:04:24 UTC ; dbname: beer_db ; TOC Entries: 28 ; Compression: gzip ; Dump Version: 1.16-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 18.4 ; Dumped by pg_dump version: 18.4 ; ; ; Selected TOC Entries: ; 12; 2615 19011 SCHEMA - inventory postgres 237; 1259 19029 TABLE inventory product_reviews postgres 236; 1259 19028 SEQUENCE inventory product_reviews_id_seq postgres 3037; 0 0 SEQUENCE OWNED BY inventory product_reviews_id_seq postgres 235; 1259 19013 TABLE inventory products postgres 234; 1259 19012 SEQUENCE inventory products_id_seq postgres 3038; 0 0 SEQUENCE OWNED BY inventory products_id_seq postgres 239; 1259 19072 TABLE inventory stock_movements postgres 238; 1259 19071 SEQUENCE inventory stock_movements_id_seq postgres 3039; 0 0 SEQUENCE OWNED BY inventory stock_movements_id_seq postgres 2866; 2604 19032 DEFAULT inventory product_reviews id postgres 2864; 2604 19016 DEFAULT inventory products id postgres 2868; 2604 19075 DEFAULT inventory stock_movements id postgres 3028; 0 19029 TABLE DATA inventory product_reviews postgres 3026; 0 19013 TABLE DATA inventory products postgres 3030; 0 19072 TABLE DATA inventory stock_movements postgres 3040; 0 0 SEQUENCE SET inventory product_reviews_id_seq postgres 3041; 0 0 SEQUENCE SET inventory products_id_seq postgres 3042; 0 0 SEQUENCE SET inventory stock_movements_id_seq postgres 2873; 2606 19038 CONSTRAINT inventory product_reviews product_reviews_pkey postgres 2871; 2606 19022 CONSTRAINT inventory products products_pkey postgres 2875; 2606 19079 CONSTRAINT inventory stock_movements stock_movements_pkey postgres 2876; 2606 19039 FK CONSTRAINT inventory product_reviews product_reviews_product_id_fkey postgres 2877; 2606 19080 FK CONSTRAINT inventory stock_movements stock_movements_product_id_fkey postgres
Lab 2.3.1 complete. You can now take logical backups in every pg_dump format and verify them before you need them:\n\n\n pg_dump --version : ✅ tools confirmed before relying on them\n Plain format (-Fp) : ✅ flat SQL script, single-threaded restore\n Custom format (-Fc) : ✅ compressed, filterable — the default choice\n Directory format (-Fd) : ✅ one file per table, the only parallel format\n --schema / --table : ✅ filtered dumps\n -j / --jobs : ✅ parallel dump measured against single-threaded\n pg_restore --list : ✅ verified a dump without restoring it\n
Enable JavaScript to run the live terminal and track your progress.