# Physical vs Logical Backups in PostgreSQL

[Supabase](https://yomu.fyi/company/supabase) · Angelico de los Reyes · Jul 7, 2020

**Type:** Explainer

## Summary

PostgreSQL backups fall into two distinct categories: logical backups, which convert data into SQL command files, and physical backups, which copy the underlying file system storage. Logical backups generated with pg\_dump or pg\_dumpall allow single-database targeting and provide the only practical route for migrating across major Postgres versions with differing internal storage formats. Conversely, physical backups suit larger databases where long-running logical exports can degrade concurrent query performance and risk failure. Physical backups also integrate with Write Ahead Log files through tools like WAL-G to support Point in Time Recovery and minimize Recovery Point Objectives. Selecting between these two strategies depends on whether administrative simplicity, version portability, cluster size, or precise disaster recovery takes precedence.

## Context

Database administrators need appropriate backup strategies for PostgreSQL databases depending on database size, migration requirements, and recovery point objectives.

## Approach / What changed

PostgreSQL supports logical backups via SQL export utilities like pg\_dump and pg\_dumpall, as well as physical backups created by copying underlying data files and pairing them with Write Ahead Log files.

## Takeaways

- Logical backups generated via pg\_dump or pg\_dumpall export SQL commands, making them essential for migrating across major PostgreSQL versions where internal storage formats differ.
- Running logical backups on databases larger than a few gigabytes risks degraded query performance, long execution times, and errors that can render the backup unusable.
- Physical backups combined with Write Ahead Log files enable Point in Time Recovery, which minimizes Recovery Point Objectives during disaster recovery.

**Tags:** [Postgres](https://yomu.fyi/topic/postgres), [Reliability](https://yomu.fyi/topic/reliability)

[Read original post](https://supabase.com/blog/postgresql-physical-logical-backups)