# Postgres backups: Logical vs. Physical an overview

DevFeed: [Postgres backups: Logical vs. Physical an overview](<https://devfeed.tech/articles/postgres-backups-logical-vs-physical-an-overview-41198.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2017/09/03/postgres-backups-physical-vs-logical/>)

Author: Map

Published: 2017-09-03T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [backups](<https://devfeed.tech/topics/backups.md>), [Database](<https://devfeed.tech/topics/database.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [data](<https://devfeed.tech/topics/data.md>)

Tags: [backing-up](<https://devfeed.tech/tags/backing-up.md>), [backup](<https://devfeed.tech/tags/backup.md>), [backups](<https://devfeed.tech/tags/backups.md>), [checksums](<https://devfeed.tech/tags/checksums.md>), [dump](<https://devfeed.tech/tags/dump.md>), [environments](<https://devfeed.tech/tags/environments.md>), [logical](<https://devfeed.tech/tags/logical.md>), [overview](<https://devfeed.tech/tags/overview.md>), [pg-dump](<https://devfeed.tech/tags/pg-dump.md>), [portable](<https://devfeed.tech/tags/portable.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [production](<https://devfeed.tech/tags/production.md>), [sql](<https://devfeed.tech/tags/sql.md>), [wal](<https://devfeed.tech/tags/wal.md>)

## AI overview

This article explains PostgreSQL's two backup types: logical backups, which are portable and can target selected tables but add database load, and physical backups, which consist of the database's bytes on disk. It also discusses checksums, corruption detection, and the role of the write-ahead log.

## Source excerpt

It's not a very disputed topic that you should backup your database, and further test your backups. What is a little less discussed, at least for Postgres, is the types of backups that exist. Within Postgres there are two forms of backups and understanding them is a useful foundation for anyone working with Postgres. The two backup types are Physical: which consist of the actual bytes on disk, Logical: which is a more portable format. Let's dig into each a bit more so you can better assess which makes sense for you. Logical backups Logical backups are the most well known type within Postgres. This is what you get when you run pg_dump against a database. There are a number of different formats you can get from logical backups and Postgres does a good job of making it easy to compress and configure this backup how you see fit. When a logical backup is run against a database it is not throttled, this introduces a noticable load on your database. As it's reading the data from disk and generating (in layman terms) a bunch of SQL INSERT statements, it has to actually see the data. It's of note that older Postgres databases (read: prior to 9.3) there were no checksums against your database. Checksums are just one tool for you to help check against data corruption. Because a logical dump has to actually read and generate the data to insert it will discover any corruption that exists for you. This portable format is also very useful to pull down copies from production to different environments. I.e. if you need a copy of production data down on your local laptop pg_dump is the way to do it. Logical backups are also database specific, but then allow you to dump only certain tables. All in all logical backups bring some good features, but come at two cost: Load on your system The backup contains data as of the time when it ran Physical backups Physical backups are another option when it comes to backing up your database. As we mentioned earlier it is the physical bytes on disk