Backup and Restore

It doesn't matter how solid your hardware and software is – sooner or later, you will need to go back in time to recover accidentally deleted or damaged data. That is the aim of backups – a safe copy that you can keep for a specific amount of time that allows you to recover from data loss. Being an enterprise-level database cluster, PostgreSQL provides a set of specific tools that allow a database administrator to take care of backups and restorations, and this chapter will show you all the main tools that you can exploit to be sure your data will last any accidental abuse.

Backup and restore isn't a very complex topic, but it's fundamental in any production system and requires careful planning. In fact, with a backup copy, you are holding another exact copy of your database just in case something nasty happens; this extra copy will...

Technical requirements

You need to know about the following to complete this chapter:

• How to interact with command-line tools
• How to inspect your filesystem and the PGDATA directory

The code for this chapter can be found in the following GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL.

Introducing various types of backups and restores

There are mainly two types of backups that apply to PostgreSQL: the logical backup (also known as a cold backup) and the physical backup (also known as a hot backup). Depending on the type of backup you choose, the restore process will differ accordingly.
PostgreSQL ships will all the integrated tools to perform the classical logical backup, which in most cases suffices. However, PostgreSQL can easily be configured to support physical backups, which are useful when the size of the cluster becomes huge, as well as when you have particular needs, as you will discover later in this chapter.

But what is the difference between these two backup methods? As you can imagine, they both achieve the very same aim: allowing you to get a usable "copy" of your data to restore either on the same cluster or against another cluster. However, the difference between the two backup strategies come from the way data is extracted from the cluster...

Exploring logical backups

PostgreSQL ships with all the required tools to perform a logical backup and restore. Many operating systems, including FreeBSD and GNU/Debian, provide scripts and wrappers for the PostgreSQL backup and restore tools to ease the system administrator in scheduling backups and restores. Such scripts and wrappers will not be explained here. For more information, consider reading your operating system's PostgreSQL package documentation.

There are three main applications involved in backup and restore – pg_dump, pg_dumpall, and pg_restore. As you can imagine from their names, pg_dump and pg_dumpall are related to extracting (dumping) the content of a database, thus creating a backup, while pg_restore is their counterpart and allows you to restore an existing backup.

PostgreSQL does not require any special "backup" permissions. In order to perform a backup, the user must have all the required grants to access individual objects, such as tables...

Exploring physical backups

A physical backup is a low-level backup that's taken during the normal operations of the database cluster. Here, low-level means that the backup is somehow performed "externally" inside the backup cluster; that is, at the filesystem level.
As you already know from Chapter 10, Users, Roles, and Database Security, the database cluster requires both the data files contained in PGDATA/base and the write-ahead logs (WALs) contained in PGDATA/wal, as well as a few other files, to make the cluster work properly. The main concept, however, is that the data files and the WALs can make the cluster self-healing and recover from a crash. Hence, a physical backup performs a copy of all the cluster files and then, when the restore is required, it simulates a database crash and makes the cluster self-heal with the WALs in place.

The reason why physical backups are important is that they allow us to effectively clone a cluster, starting from the files it is...

Summary

In this chapter, we learned that PostgreSQL provides advanced tools so that we can perform backups and restorations. Backups are important because, even in a battle-tested and high-quality product such as PostgreSQL, things can go wrong: often, the users may accidentally damage their data, but other times, the hardware or the software could fail miserably. Being able to restore data, partially or fully, is therefore very important and every database administrator should carefully plan backup strategies.

We also learned that PostgreSQL ships with tools for both logical and physical backups. Logical backups are taken by means of reading the data from the database itself, by means of ordinary SQL interactions; physical backups are taken by means of cloning the PGDATA directory either by using operating system tools or PostgreSQL ad hoc solutions. Restoration is performed by specific tools in the case of logical backups, and by the database self-healing mechanism in the case of physical...

Further reading

• PostgreSQL pg_dump tool official documentation: https://www.postgresql.org/docs/12/app-pgdump.html
• PostgreSQL pg_dumpall tool official documentation: https://www.postgresql.org/docs/12/app-pg-dumpall.html
• PostgreSQL pg_restore tool official documentation: https://www.postgresql.org/docs/12/app-pgrestore.html
• FreeBSD 502.pgsql backup script: https://www.freshports.org/databases/postgresql83-server/files/502.pgsql
• PostgreSQL pg_basebackup tool official documentation: https://www.postgresql.org/docs/12/app-pgbasebackup.html
• PostgreSQL pg_verifybackup tool official documentation: https://www.postgresql.org/docs/13/app-pgverifybackup.html
• pgBackRest external tool for physical backups: https://pgbackrest.org/