PostgreSQL Tutorial

Backup and Restore Concepts: pg_dump and pg_restore

A database with no backup strategy is one hardware failure or mistaken DELETE away from permanent data loss. PostgreSQL supports two fundamentally different approaches to backups — logical and physical — and understanding the difference determines which tool is right for a given recovery scenario.

Why it matters

Disks fail, people run a DELETE without a WHERE, and ransomware exists. A database without tested backups is one accident away from losing everything. Knowing how to take a backup, and above all having restored one, is a basic responsibility for anyone who runs a database.

Logical vs. Physical Backups

A logical backup captures the data as a sequence of SQL statements (or an equivalent portable format) that can recreate the schema and rows — it is database-version-and-platform independent, human-inspectable in plain SQL form, and lets you restore a single table or database rather than everything at once. A physical backup copies the actual data files PostgreSQL stores on disk (via tools like pg_basebackup, or filesystem/volume snapshots), producing an exact byte-for-byte copy that restores much faster for very large databases but must be restored to a compatible PostgreSQL version and architecture, and generally restores the entire cluster rather than one table.

pg_dump and pg_restore

pg_dump dbname > backup.sql produces a logical, plain-SQL dump of one database. Using the -Fc (custom format) flag instead produces a compressed, non-plain-text archive that supports selective and parallel restore: pg_dump -Fc dbname > backup.dump. pg_restore -d newdb backup.dump then rebuilds the database from that custom-format archive, and importantly, pg_restore can selectively restore just one table or schema from the archive with -t tablename, something a plain SQL dump makes far more tedious.

Choosing an Approach

Logical backups (pg_dump) are the right default for most applications: they are simple, portable across PostgreSQL versions, and support fine-grained restores. Physical backups (pg_basebackup plus continuous WAL archiving) become necessary for very large databases where minimizing downtime and restore time matters, and they enable point-in-time recovery — replaying the write-ahead log to restore the database to any specific moment, not just the moment the backup was taken.

Example

Creating a compressed logical backup and restoring it into a new database

SQL

-- Create a custom-format dump (from the terminal, not psql)
-- pg_dump -Fc -d bookstore -f bookstore.dump

-- Create an empty target database, then restore into it
-- createdb bookstore_restored
-- pg_restore -d bookstore_restored bookstore.dump

Output

pg_dump: dumping contents of table "public.authors"
pg_restore: creating TABLE "public.authors"
pg_restore: creating CONSTRAINT "authors_pkey"
-- restored database now matches the source at dump time

Common Mistakes

Relying only on plain-text pg_dump output for a very large database, missing out on the parallel restore and selective-table restore that the custom (-Fc) format enables.
Assuming a physical backup (data directory copy or filesystem snapshot) can be restored onto a different PostgreSQL major version — physical backups are tied to a specific version and platform, unlike logical dumps.
Never testing a restore until an actual emergency, discovering only then that the backup file is corrupted, incomplete, or missing required extensions/roles.
Treating a single full pg_dump as sufficient for point-in-time recovery — recovering to an arbitrary moment (not just the dump's snapshot time) requires continuous WAL archiving with a physical base backup.

Exercise

This exercise uses PostgreSQL's command-line tools, so it needs a PostgreSQL installation and is run in a terminal, not in the playground. Write the commands to: take a compressed, custom-format backup of the database shop into the file shop.dump; create an empty database named shop_restored; and restore the backup into it.

Expected result: shop_restored contains the same tables and data as shop.

SQL · starter code

# 1. back up the database shop to shop.dump in custom format

# 2. create the empty database shop_restored

# 3. restore shop.dump into shop_restored

Show hints

The custom format is chosen with -Fc, and the output file with -f.
A custom-format file is restored with pg_restore, naming the target database with -d.

Show solution

SQL · solution

# 1. back up the database shop to shop.dump in custom format
pg_dump -Fc -d shop -f shop.dump

# 2. create the empty database shop_restored
createdb shop_restored

# 3. restore shop.dump into shop_restored
pg_restore -d shop_restored shop.dump

Quick quiz

1. What kind of backup does pg_dump produce?

A. A physical copy of the data files
B. A snapshot of the disk
C. A replica server
D. A logical backup: the SQL statements or archive needed to recreate one database

Show answer

D. pg_basebackup is the tool that takes a physical copy of the whole cluster.

2. What does a physical backup combined with WAL archiving make possible?

A. Point-in-time recovery: restoring the database to any chosen moment
B. Faster queries
C. Smaller tables
D. Restoring a single table only

Show answer

A. The base backup is restored and the archived WAL is replayed up to the chosen time.

3. When can a backup be trusted?

A. As soon as the command finishes
B. Only after it has been restored successfully in a test
C. When the file is large
D. When it is stored on the same server

Show answer

B. An untested backup may be incomplete or unusable, and it should be stored away from the server.

Interview questions

What is the difference between logical and physical backups?

Show answer

A logical backup, made with pg_dump, exports the contents of a database as SQL or an archive. It is portable across versions and machines and can restore individual tables, but it is slow for large databases and reflects only the moment it was taken. A physical backup, made with pg_basebackup, copies the data files of the whole cluster. It is faster for large systems and, with archived WAL, allows recovery to any point in time.

What makes a good backup strategy?

Show answer

Automated, regular backups; copies kept in a different location from the database server; retention that matches how far back you may need to go; point-in-time recovery for systems where losing a day of data is unacceptable; and, most important, regular restore tests. Two numbers define the requirement: how much data the business can afford to lose, and how long it can afford to be down.

Key Points to Remember

Logical backups (pg_dump) store portable SQL/data and allow selective, version-flexible restores.
Physical backups copy raw data files, restoring faster for huge databases but tied to a compatible PostgreSQL version.
pg_dump's custom format (-Fc) enables compression, parallel dumps, and selective restore via pg_restore.
Point-in-time recovery requires physical base backups combined with continuous WAL archiving, not logical dumps alone.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs your SQL on SQLite in your browser. Standard SQL works; a few features that exist only in PostgreSQL or MySQL do not. Your code for each topic is kept in this browser.

Open a blank playground

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering

About the authors More tutorials LinkedIn