0Pricing
SQL Academy · Lesson

Logical vs Physical Backups

pg_dump and base backups.

Logical vs Physical Backups is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Database Backup?

A backup is a copy of your database data that can be used to restore the system after data loss, corruption, or disaster. Without reliable backups, a single hardware failure or accidental DELETE can permanently destroy months or years of data.

PostgreSQL provides two broad categories of backup strategies: logical backups and physical backups. Each has distinct characteristics, use cases, and trade-offs that every DBA must understand.

Logical Backups Explained

A logical backup exports the database as human-readable SQL statements — CREATE TABLE, INSERT, COPY, and similar commands. The most common tool for this in PostgreSQL is pg_dump.

Because the output is plain SQL, a logical backup is portable: you can restore it to a different PostgreSQL version, a different operating system, or even selectively restore individual tables or schemas. The trade-off is that dumping and restoring large databases can be slow.

Using pg_dump for a Logical Backup

The pg_dump utility is run from the command line, not inside SQL. It connects to a running PostgreSQL server and exports the chosen database. You can output plain SQL, a custom compressed format, or a directory format.

The SQL below simulates what a logical backup captures — the structure and data of a table as reproducible statements.

-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)

CREATE TABLE orders (
    id        SERIAL PRIMARY KEY,
    customer  TEXT        NOT NULL,
    amount    NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (customer, amount, created_at) VALUES
    ('Alice',  149.99, '2024-01-15 09:30:00+00'),
    ('Bob',     89.50, '2024-01-16 14:00:00+00'),
    ('Carol',  210.00, '2024-01-17 11:15:00+00');

pg_dump Output Formats

pg_dump supports four output formats, each suited to different restore workflows:

  • plain — a plain SQL script, readable in any text editor.
  • custom — a compressed binary format; the most flexible, supports parallel restore.
  • directory — one file per table, supports parallel dump and restore.
  • tar — a tar archive of the directory format.

The custom format is recommended for large databases because pg_restore can restore objects in parallel using -j N workers.

-- Checking which databases exist before choosing what to back up
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM   pg_database
WHERE  datname NOT IN ('template0', 'template1')
ORDER  BY pg_database_size(datname) DESC;

Restoring a Logical Backup

A plain-SQL logical backup is restored with psql. A custom-format backup requires pg_restore. Both tools replay the SQL statements to recreate tables, indexes, constraints, and data.

Because logical backups contain SQL, you can edit them before restoring — for example, to restore only one table, or to change a schema name. This flexibility is one of the biggest advantages of the logical approach.

-- After restoring a backup, verify row counts match expectations
SELECT
    schemaname,
    relname           AS table_name,
    n_live_tup        AS estimated_rows
FROM  pg_stat_user_tables
ORDER BY n_live_tup DESC;

Physical Backups Explained

A physical backup (also called a base backup) copies the raw data files that PostgreSQL uses on disk — the pages, WAL (Write-Ahead Log) segments, and configuration files. The result is a binary snapshot of the entire cluster at a point in time.

Physical backups are typically much faster to restore for large databases because there is no SQL re-execution; PostgreSQL simply reads the files back into place and replays WAL to reach a consistent state.

pg_basebackup: Taking a Physical Backup

pg_basebackup is the standard PostgreSQL tool for physical backups. It streams the data directory from a running primary server over a replication connection. You need a replication-privileged user and wal_level set to replica or higher.

Inside the database you can query replication settings to confirm the server is configured correctly before attempting a base backup.

-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM   pg_settings
WHERE  name IN (
    'wal_level',
    'max_wal_senders',
    'archive_mode',
    'archive_command'
)
ORDER  BY name;

WAL Archiving and Point-in-Time Recovery

A base backup captures a moment in time. To recover to any arbitrary point after that backup, PostgreSQL replays archived WAL segments — this is called Point-in-Time Recovery (PITR).

When archive_mode = on and archive_command is configured, PostgreSQL copies completed WAL segments to an archive location. During recovery, restore_command fetches those segments back so the server can replay them up to the desired target time.

-- Inspect current WAL position and archive status
SELECT
    pg_current_wal_lsn()                        AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())        AS current_wal_file,
    archived_count,
    failed_count,
    last_archived_wal,
    last_archived_time
FROM  pg_stat_archiver;

Comparing Logical vs Physical Backups

Choosing between logical and physical backups depends on your requirements:

  • Logical (pg_dump): portable across versions, supports partial restore, human-readable, but slow for large databases and no sub-transaction granularity.
  • Physical (pg_basebackup + WAL): fast restore for large clusters, supports PITR, version-specific (must restore to same major version), and restores the entire cluster — you cannot restore a single table.

Production environments typically use both: nightly physical base backups with continuous WAL archiving, plus periodic logical dumps for portability and surgical restores.

Verifying Backup Integrity

A backup that has never been tested is not a backup — it is a hope. Always validate backups by restoring them to a test environment and verifying data.

For logical backups, a quick integrity check is to count rows and compare checksums. For physical backups, PostgreSQL 14+ introduced pg_verifybackup which checks the manifest file written by pg_basebackup.

-- After a test restore, compare row counts across critical tables
SELECT
    relname                              AS table_name,
    n_live_tup                           AS live_rows,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM  pg_stat_user_tables
WHERE  schemaname = 'public'
ORDER  BY n_live_tup DESC
LIMIT  20;

Backup Monitoring and Scheduling

Automating and monitoring backups is as important as taking them. Track when backups last ran, how long they took, and whether they succeeded. PostgreSQL exposes useful metadata for this purpose.

For physical backups, pg_stat_archiver shows the last successful archive and any failures. For logical backups, wrap pg_dump in a script that logs start time, end time, file size, and exit code to a monitoring table or alerting system.

-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
    id          SERIAL PRIMARY KEY,
    backup_type TEXT        NOT NULL CHECK (backup_type IN ('logical', 'physical')),
    started_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at TIMESTAMPTZ,
    size_bytes  BIGINT,
    status      TEXT        NOT NULL DEFAULT 'running',
    notes       TEXT
);

-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;

Logical vs Physical: Quick Check

Test your understanding of logical and physical backup strategies in PostgreSQL.

Lesson Recap: Logical vs Physical Backups

In this lesson you explored two fundamental PostgreSQL backup strategies:

  • Logical backups use pg_dump to export databases as SQL statements. They are portable, human-readable, and support partial restores, but can be slow for very large databases.
  • Physical backups use pg_basebackup to copy raw data files. Combined with WAL archiving they enable fast restores and Point-in-Time Recovery, though they are version-specific and always restore the full cluster.
  • Production systems typically combine both strategies: physical base backups with WAL archiving for fast, granular recovery, plus periodic logical dumps for portability.
  • Always test your restores. An untested backup cannot be trusted in a real disaster scenario.

Understanding these two approaches is essential for designing a robust disaster recovery plan for any PostgreSQL deployment.

Frequently asked questions

Is the “Logical vs Physical Backups” lesson free?

Yes — the full text of “Logical vs Physical Backups” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Logical vs Physical Backups”?

pg_dump and base backups. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Logical vs Physical Backups” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Logical vs Physical Backups
  2. Point-in-Time Recovery
  3. Testing Your Restores
  4. Disaster Recovery Planning
← Back to SQL Academy