Logical vs Physical Backups

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.

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.