Online database backup can be roughly classified into two categories: logical and physical backups. While both of them have advantages and disadvantages, there is one disadvantage in logical backup; taking too much time for its performance. In particular, it requires a fairy long time to make the backup of a large database, and even more time to restore such a database from backup data. On the contrary, physical backup makes it possible to backup and to restore large databases in a relatively short time so that it is a very important and useful feature in practical systems.
In PostgreSQL, online physical full backup has been available since version 8.0, and a snapshot of a running whole database cluster (i.e. physical backup data) is known as a base backup.
Point-in-Time Recovery (PITR), which has also been available since version 8.0, is the feature to restore a database cluster to any point in time using a base backup and archive logs created by continuous archiving feature. For example, even if you made a critical mistake (e.g. truncating all tables), this feature enables you to restore the database of the point just before the mistake you have made.
In this chapter, following topics are described:
In version 7.4 or earlier, PostgreSQL had supported only logical backups (logical full and partial backups, and data exports).
First of all, the standard procedure to make a base backup using the low-level commands is shown below:
This simple procedure is easy-to-use for database system administrators, because it requires no special tools but common tools such as copy command or a similar archiving tools to create a base backup. In addition, in this procedure, no table locks are required and all users can issue queries without being affected by backup operation. Those are big advantages over other major open source RDBMS.
More simple way to make a base backup is to use the pg_basebackup utility, but it internally issues those low-level commands.
As the pair of those commands is clearly one of the key points for understanding the PITR, we will explore them in the following subsections.
The pg_backup_start and pg_backup_stop commands are defined here: src/backend/access/transam/xlogfuncs.c.
The pg_backup_start prepares for making a base backup. As discussed in Section 9.8, recovery process starts from a REDO point, so the pg_backup_start must do 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 checkpoint might be done a number of times during backup. Hence the pg_backup_start performs the following four operations:
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 (version 11 or later, seven items):
An actual example of backup_label file in version 9.6 is shown in the following:
postgres> cat /usr/local/pgsql/data/backup_label START WAL LOCATION: 0/9000028 (file 000000010000000000000009) CHECKPOINT LOCATION: 0/9000060 BACKUP METHOD: pg_start_backup BACKUP FROM: master START TIME: 11:45:19 GMT LABEL: Weekly Backup
As you may imagine, when you recover a database using this base backup, PostgreSQL takes the ‘CHECKPOINT LOCATION’ out of the backup_label file to read the checkpoint record from the appropriate archive log, and then, gets the REDO point from its record and starts recovery process. (The details will be described in the next section.)
The pg_backup_stop performs the following five operations to complete the backup.
The naming method for backup history file is shown below.
{WAL segment}.{offset value at the time the base backup was started}.backup
Figure 10.2 shows the basic concept of PITR. PostgreSQL in PITR-mode replays the WAL data of the archive logs on the base backup, from the REDO point created by the pg_backup_start up to the point you want to recover. In PostgreSQL, the point to be recovered is referred to as a recovery target.
Fig. 10.2. Basic concept of PITR.Here is the description of how PITR works. Suppose that you made a mistake at 12:05 GMT of . You should remove the database cluster and restore the new one using the base backup you made before that. Then, set the command of the parameter restore_command, and also set the time of the parameter recovery_target_time at the point you made the mistake (in this case, 12:05 GMT) in a recovery.conf (version 11 or earlier) or postgresql.conf (version 12 or later).
# Place archive logs under /mnt/server/archivedir directory. restore_command = 'cp /mnt/server/archivedir/%f %p' recovery_target_time = " 12:05 GMT"
When PostgreSQL starts up, it enters into PITR mode if there are a recovery.conf (version 11 or earlier) or recovery.signal(version 12 or later), and a backup_label in the database cluster.
recovery.conf has been abolished in version 12, and all recovery-related parameters should be written in postgresql.conf. See the official document in detail.
In version 12 or later, when you restore your server from a basebackup, you need to make an empty file called recovery.signal file in the database cluster directory.
$ touch /usr/local/pgsql/data/recovery.signal
The records of commit and abort actions contain the timestamp at which each action has done (XLOG data portion of both actions are defined in xl_xact_commit and xl_xact_abort respectively). Therefore, if a target time is set to the parameter recovery_target_time, PostgreSQL may select whether to continue recovery or not, whenever it replays XLOG record of either commit or abort action. When XLOG record of each action is replayed, PostgreSQL compares the target time and each timestamp written in the record; and if the timestamp exceed the target time, PITR process will be finished.
PITR process is almost the same as the normal recovery process described in Chapter 9; the only differences between them are the following two points:
The outline of PITR process is described as follows:
The records of commit and abort actions contain the timestamp at which each action has done (XLOG data portion of both actions are defined in xl_xact_commit and xl_xact_abort respectively). Therefore, if a target time is set to the parameter recovery_target_time, PostgreSQL may select whether to continue recovery or not, whenever it replays XLOG record of either commit or abort action. When XLOG record of each action is replayed, PostgreSQL compares the target time and each timestamp written in the record; and if the timestamp exceed the target time, PITR process will be finished.
The function read_backup_label is defined in src/backend/access/transam/xlog.c.
The structure xl_xact_commit and xl_xact_abort are defined in src/include/access/xact.h.
Recovery process is a process to make a database cluster in a consistent state, though the cluster is inconsistent. As PITR is based on the recovery process, it can recover the database cluster even if a base backup is a bunch of inconsistent files. This is the reason why we can use common archiving tools without a file system snapshot capability or a special tool.
Timeline in PostgreSQL is used to distinguish between the original database cluster and the recovered ones, and is central concept of PITR. In this section, two things associated with the timeline are described: timelineId and timeline history files.
Each timeline is given a corresponding timelineId, a 4-byte unsigned integer starting at 1.
An individual timelineId is assigned to each database cluster. The timelineId of original database cluster created by the initdb utility is 1. Whenever database cluster recovers, timelineId will be increased by 1. For example, in the example of the previous section, the timelineId of the cluster recovered from the original one is 2.
Figure 10.3 illustrates the PITR process from the viewpoint of the timelineId. First, we remove our current database cluster and restore the base backup made in the past, in order to go back to the starting point of recovery, and such situation is represented in the red arrow curve in the figure. Next, we start the PostgreSQL server which replays WAL data in the archive logs from the REDO point created by the pg_backup_start until the recovery target by tracing along the initial timeline (timelineId 1), and such situation is represented in the blue arrow line in the figure. Then, a new timelineId 2 is assigned to recovered database cluster and PostgreSQL runs on the new timeline.
Fig. 10.3. Relation of timelineId between an original and a recovered database clusters.As briefly mentioned in Chapter 9, the first 8-digit of WAL segment filename is equal to the timelineId of the database cluster created for each segment. When the timelineId is changed, WAL segment filename will also be changed.
Focusing on WAL segment files, the recovery process will be described again. Suppose that we recover the database cluster using two archive logs ‘000000010000000000000009’ and ‘00000001000000000000000A’. The newly recovered database cluster is assigned the timelineId 2, and PostgreSQL creates the WAL segment from ‘00000002000000000000000A’. Figure 10.4 shows this situation.
Fig. 10.4. Relation of WAL segment files between an original and a recovered database clusters.When a PITR process completes, a timeline history file with names like ‘00000002.history’ is created under the archival directory and the pg_xlog subdirectory (in version 10 or later, pg_wal subdirectory). This file records which timeline it branched off from and when.
The naming rule of this file is shown in the following:
"8-digit new timelineId".history
A timeline history file contains at least one line, and each line is composed of the following three items:
A specific example is shown below:
postgres> cat /home/postgres/archivelogs/00000002.history 1 0/A000198 before 12:05:00.861324+00
Meaning as follows:
The database cluster (timelineId=2) is based on the base backup whose timelineId is 1, and is recovered in the time just before ‘ 12:05:00.861324+00’ by replaying the archive logs until the 0/A000198.
In this way, each timeline history file tells us a complete history of the individual recovered database cluster. Moreover, it is also used to PITR process itself. The detail is explained in the next section.
The timeline history file format is changed in version 9.3. Formats of versions 9.3 or later and earlier both are shown below but not in detail.
Later version 9.3:timelineId LSN "reason"Until version 9.2:
timelineId WAL_segment "reason"
The timeline history file plays an important role in the second and subsequent PITR processes. By trying the second time recovery, we will explore how it is used.
Again, suppose that you made a mistake at 12:15:00 in the recovered database cluster whose timelineId is 2. In this case, to recover the database cluster, you should create a new recovery.conf shown below:
restore_command = 'cp /mnt/server/archivedir/%f %p' recovery_target_time = " 12:15:00 GMT" recovery_target_timeline = 2
The parameter recovery_target_time sets the time you made new mistake, and the recovery_target_timeline is set at '2', in order to recover along its timeline.
Restart the PostgreSQL server and enter PITR mode to recover the database at the target time along the timelineId 2. See Fig. 10.5.
Fig. 10.5. Recover the database at 12:15:00 along the timelineId 2.postgres> cat /home/postgres/archivelogs/00000003.history 1 0/A000198 before 12:05:00.861324+00 2 0/B000078 before 12:15:00.927133+00
When you do PITR more than once, you should explicitly set a timelineId for using the appropriate timeline history file.
In this way, timeline history files are not only history logs of database cluster, but also the recovery instruction documents for PITR process.