Logical and physical database backups

Objectives

In this unit you'll learn the difference between logical and physical database backups. 

While this overview is created with MySQL, MariaDB, and Postgres in mind, the concepts apply to other database systems as well.

At the end of the unit you'll be able to:

  • Describe the difference between logical and physical database backups.
  • List the advantages and disadvantages of each type of backup.
  • Choose the most appropriate type of backup for a given scenario.
     
Prerequisites
  • None.

Introduction

In the world of database backups, the words dump, backup, and snapshot are often used interchangeably, and there does not seem to be a standard definition.

We use the following definitions:

Backup

A database backup is a partial or complete copy of a database's structure (schema) and/or data. 
Backups come in two types: dumps and snapshots.

Dump

A database dump is a logical backup that's human-readable and typically contains both a database's structure (schema) and data, but could also only contain either the schema or the data, or even only part of the data (only selected tables, or all tables except certain tables).

Snapshot

A database snapshot is a physical backup in binary format that typically contains an entire database's schema and data.

At first glance there doesn't seem to be much difference between dumps and snapshots. We'll clarify both in the next sections.
 

Logical backups (database dumps)

A logical backup is copy of the database's structure and data in human-readable format. It typically contains:

  • The SQL statements that define the database structure (tables and their columns).
  • The SQL statements that insert the exported data into the tables.

In other words, a logical backup is like a script or program: it contains all the instructions necessary to recreate the tables and insert the data.

Logical backups are often used to:

  • backup/restore only a subset of the database tables
  • duplicate a local database and deploy it to a different server
  • export data, change some of it with search/replace operations, and then reimport the modified data.

Logical backups are plain text files, so you can also use them to search and replace bits of text across your entire data set before importing.

However, as database sizes grow, exporting and importing logical backups can become slow and impact the database's performance.

Tools

You can use mysqldump / mariadb-dump (MySQL/MariaDB), and pg_dump (Postgres) to create logical database backups.

Physical backups (database snapshots)

Physical backups create copies of the binary data files that contain your database's data and configuration.

Importing and exporting physical backups tends to be much faster than using logical backups because: 

  • Physical backups are usually smaller (no statements to recreate the database structure).
  • The process does not involve executing hundreds or thousands of SQL statements to insert the data.

Physical backups are often used to restore a complete database in case of server failure, or to create a new replica node in more complex  scenarios.

It is  not recommended to use physical backups if you want to recreate a local database on a different server; logical backups are much better suited for that.

Tools

xtrabackup (Percona / MySQL), mariabackup (MariaDB), and pg_basebackup (Postgres) are examples of tools that create physical backups.
 

Conclusion

A logical database backup is an automatically generated script that you can run on a different database server to recreate database structure and data. This the default approach to move databases between servers.

A physical database backup is a physical copy of a database's binary data files. In emergency scenarios you can instantly replace your faulty/corrupt database files with your backed up files to restore the database to a previous state. 
 

Activity 1

You want to create daily backups of your database so you can restore a recent version if the database gets corrupted. Which type of backup is most suited for this?

Both logical and physical backups are suited for this purpose, but physical backups tend to be more performant, especially as the size of your database grows.

Hide hints
show hints

Activity 2

You have developed a web application locally, and want to set it up on a publicly accessible server. 

Which type of backup is most suited recreate your local database on the new server? Why?

Logical backup, because they are more flexible and can deal better with platform differences and different database versions. 

Hide hints
show hints

Activity 3

Of the two types of database backups, which type tends to produce the smallest backup files, and why?

Physical backups, because they don't contain all the individual SQL statements to recreate the structure and insert the data.

Hide hints
show hints

Activity 4

Of the two types of database backups, which one tends to import faster? 

Physical backups, because don't need to execute all the individual SQL statements to create the structure and import the data.
 

Hide hints
show hints

Summary

Database dumps are logical backups.

  • They usually contain all the SQL statements necessary to reconstruct the database structure and data.
  • They are more flexible and versatile.
  • They are more resistant to problems due to different database versions
  • The import/export process tends to be longer.


Database snapshots are physical backups. 

  • They are a copy of the database's binary data files and contain the whole structure and all of the data.
  • They are typically used to restore a database to a previous state when something bad has happened.
  • They are not well-suited for using across different platforms and database versions.
  • The import/export process tend to be more quicker.