This post is the fisrt section of my document and is for beginners.

Suppose that the TABLE_A’s page-data on the storage is corrupted, because the operating system has failed while the background-writer process has been writing the dirty pages. As XLOG records cannot be replayed on the corrupted page, we would need an additional feature.

PostgreSQL supports a feature referred to as full-page writes to deal with such failures. If it is enabled, PostgreSQL writes a pair of the header-data and the entire page as XLOG record during the first change of each page after every checkpoint; default is enabled. In PostgreSQL, such a XLOG record containing the entire page is referred to as backup block (or full-page image).

Let’s describe the insertion of tuples again, but with the full-page-writes enabled. See Figure 4 and the following description.

Figure 4: Full page writes

1. The checkpointer starts a checkpoint process.
2. In the insertion of the first INSERT statement, though PostgreSQL operates in the almost the same manner as in the previous section, this XLOG record is the backup block of this page (i.e. it contains the page entirety), because this is the first writing of this page after the latest checkpoint.
3. As this transaction commits, PostgreSQL operates in the same manner as in the previous section.
4. In the insertion of the second INSERT statement, PostgreSQL operates in the same manner as in the previous section since this XLOG record is not a backup block.
5. When this statement’s transaction commits, PostgreSQL operates in the same manner as in the previous section.
6. To demonstrate the effectiveness of full-page writes, here we consider the case in which the TABLE_A’s page on the storage has been corrupted due to the operating system failure occurred while the background-writer has been writing it into the HDD.

Restart the PostgreSQL server to repair the broken cluster. See Figure 5 and the following description.

Figure 5: Database recovery with backup block

1. PostgreSQL reads the XLOG record of the first INSERT statement and loads the corrupted TABLE_A’s page from the database cluster into the shared buffer pool. In this example, the XLOG record is a backup block, because the first XLOG record of each page is always its backup block according to the writing rule of full-page writes.
2. When a XLOG record is its backup block, another rule of replaying is applied: the record’s data-portion (i.e. the page itself) is to be overwritten onto the page regardless of the values of both LSNs, and the page’s LSN updated to the XLOG record’s LSN. In this example, PostgreSQL overwrites the data-portion of the record to the corrupted page, and updates the TABLE_A’s LSN to LSN_1. In this way, the corrupted page is restored by its backup block.
3. Since the second XLOG record is a non-backup block, PostgreSQL operates in the same manner as the instruction in the previous section.

PostgreSQL can be recovered even if some data write failures have occurred. (Of course, this does not apply if the file-system or media failure has occurred.)

If you want to know more details, please read my document.