TIL Postgres: Logical vs. Physical Backups

As anyone who has been put in charge of a database knows, setting up backups and recovering from them are perhaps essential components of one's skillset. You probably would have already tried one or more of the many tools and ways in Postgres to achieve them, some of which we will cover in future posts. All can be categorized into two types of backups: logical and physical. In this week's edition of TIL Postgres, we will take a quick look at each one and discuss the situations in which they are best used for.

Logical Backups

This form of backup is typically achieved by translating all the data into a set of SQL commands and writing it into a single file. This can then be fed to any database cluster to recreate everything. In your CLI, performing logical backups can be as easy as:

pg_dump db_name > file_name.sql

for a single database, and:

pg_dumpall > file_name.sql

for an entire database cluster. Both the pg_dump and pg_dumpall utilities have their respective additional options for you to choose from and set up your desired logical backup setting. Recovering from them is comparably as simple:

psql -d db_name -f file_name.sql

What is it good for?

Simpler and quicker way of performing backups

As shown above, a single command is enough to perform a logical backup, and another to recover from it. As a novice with databases, this would be an ideal and non-intimidating start in making sure that your database is backed up at all times.

Migration between different major versions of Postgres

If you are planning to migrate to a different major version of Postgres (for eg. from Postgres 11 to Postgres 12), logical backups via pg_dumpall would surely be your tool of choice. This is mainly because internal data storage formats may differ between major versions. This is the basis of physical backups, hence eliminating it as an option. We'll go deeper into migrations and how to perform them in another post.

Backing up a single specific database

With pg_dump, you can constantly just back up the database of your choice without having to think about the rest.

Physical Backups

Physical backups pertain more to the actual set of files or file systems where all your data are stored. Performing a backup can just involve taking a snapshot of all the files involved by making a copy of them and storing it somewhere safe.

What is it good for?

More ideal for larger databases

As your database grows to the size of a few gigabytes, backing it up through physical backups is more ideal than through logical backups. As explained here, over time, performing logical backups in large databases could lead to degraded performance for other queries. Given the long run time as well to successfully perform a logical backup on a large database, errors have a higher chance of occurring, making the eventual backup unusable.

Achieving Point in Time Recovery

Another form of physical backup called Write Ahead Log (WAL) files can be used together with a backed-up file system to recover a database up to any chosen point in time. When disaster strikes, this would be one of the best options in recreating a database up to the point right before the unfortunate happens. This greatly minimizes Recovery Point Objective (RPO) along the way. Even better, a lot of tools such as WAL-G are readily available to simplify the steps involved in setting everything up.

Conclusion

All in all, logical and physical backups are generated differently from one another. Neither has an advantage over the other. Depending on your needs, each brings unique uses to the table:

Logical	Physical
Simpler way of getting started with backups.	Better way of handling backups for larger database clusters.
Using it to migrate between different major versions of Postgres.	Using it for Point in Time Recovery.
Having the option to back up a single database.

---

TIL Postgres is an ongoing series by Supabase that aims to regularly share snippets of information about PostgreSQL and hopefully provide you with something new to learn. 😉

Any new posts or announcements of our future features and freebies will be made here on DEV first. Follow us so that you don't miss out.