10.1. Base Backup

First of all, the standard procedure to make a base backup using the low-level commands is as follows:

• (1) Issue the pg_backup_start command (versions 14 or earlier, pg_start_backup).

• (2) Take a snapshot of the database cluster using the archiving command of your choice.

• (3) Issue the pg_backup_stop command (versions 14 or earlier, pg_stop_backup).

This simple procedure is easy for database administrators to use because it requires no special tools other than common tools such as the cp command or a similar archiving tool. In addition, this procedure does not require table locks, so all users can continue to issue queries without being affected by the backup operation. This is a significant advantage over other major open source RDBMSs.

A simpler way to make a base backup is to use the pg_basebackup utility, which internally issues the low-level commands described above.

Because the pg_backup_start and pg_backup_stop commands are so important to understanding PITR, we will explore them in more detail in the following subsections.

10.1.1. pg_backup_start (Ver.14 or earlier, pg_start_backup)

The pg_backup_start command prepares for making a base backup.

As discussed in Section 9.8, the recovery process starts from a REDO point, so the pg_backup_start command must do a checkpoint to explicitly create a REDO point at the start of making a base backup. Moreover, the checkpoint location of its checkpoint must be saved in a file other than pg_control because regular checkpoints might be done a number of times during the backup. Therefore, the pg_backup_start performs the following four operations:

1. Force the database into full-page write mode.

2. Switch to the current WAL segment file (versions 8.4 or later).

3. Do a checkpoint.

4. Create a backup_label file – This file, created in the top level of the base directory, contains essential information about base backup itself, such as the checkpoint location of this checkpoint.

The third and fourth operations are the heart of this command. The first and second operations are performed to recover a database cluster more reliably.

A backup_label file contains the following six items (versions 11 or later, seven items):

• CHECKPOINT LOCATION – This is the LSN location where the checkpoint created by this command has been recorded.

• START WAL LOCATION – This is not used with PITR, but used with the streaming replication, which is described in Chapter 11. It is named ‘START WAL LOCATION’ because the standby server in replication-mode reads this value only once at initial startup.

• BACKUP METHOD – This is the method used to make this base backup.

• BACKUP FROM – This shows whether this backup is taken from the primary or standby server.

• START TIME – This is the timestamp when the pg_backup_start command was executed.

• LABEL – This is the label specified at the pg_backup_start command.

• START TIMELINE – This is the timeline that the backup started. This is for a sanity check and has been introduced in version 11.

postgres> cat /usr/local/pgsql/data/backup_label
START WAL LOCATION: 0/1B000028 (file 00000001000000000000001B)
CHECKPOINT LOCATION: 0/1B000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-1-1 11:45:19 GMT
LABEL: pg_basebackup base backup
START TIMELINE: 1

As you may imagine, when you recover a database using this base backup, PostgreSQL takes the ‘CHECKPOINT LOCATION’ from the backup_label file to read the checkpoint record from the appropriate archive log. It then gets the REDO point from the record and starts the recovery process. (The details will be described in the next section.)

10.1.2. pg_backup_stop (Ver.14 or earlier, pg_stop_backup)

The pg_backup_stop command performs the following five operations to complete the backup:

1. Reset to non-full-page writes mode if it has been forcibly changed by the pg_backup_start command.

2. Write a XLOG record of backup end.

3. Switch the WAL segment file.

4. Create a backup history file. This file contains the contents of the backup_label file and the timestamp that the pg_backup_stop command was executed.

5. Delete the backup_label file. The backup_label file is required for recovery from the base backup, but once copied, it is not necessary in the original database cluster.

{WAL segment}.{offset value at the time the base backup was started}.backup