July 10, 2019 at 14:11WAL in PostgreSQL: 2. Prerecord log
Last time, we met with the device of one of the important objects of shared memory, the buffer cache. The possibility of losing information from RAM is the main reason for the need for recovery from a failure. Today we’ll talk about these tools.
Alas, miracles do not happen: in order to survive the loss of information in RAM, everything necessary must be written to a disk (or other non-volatile device) in a timely manner.
Therefore, this is what has been done. Along with data changes, a journal of these changes is also kept . When we change something on a page in the buffer cache, we create a record in the log about this change. The record contains the minimum information sufficient so that, if necessary, the change can be repeated.
For this to work, the journal entry must necessarily go to disk before the modified page gets there. Hence the name: PreRecord magazine (write-ahead log).
If a failure occurs, the data on the disk is in an inconsistent state: some pages were written earlier, some later. But there remains a journal that can be read and re-performed by those operations that have already been completed before the failure, but whose result did not reach the disk.
You need to log all operations, during which there is a risk of inconsistency on the disk in the event of a failure. In particular, the following actions are logged:
Not logged:
Prior to PostgreSQL 10, hash indexes were not logged (they only served to map hash functions to different data types), but now this has been fixed.
Logically, a journal can be thought of as a sequence of records of various lengths. Each record contains data about a certain operation, preceded by a standard header . The title, among other things, indicates:
The data itself has a different format and meaning. For example, they can represent some fragment of a page that needs to be written over its contents with a certain offset. The specified resource manager “understands” how to interpret the data in its record. There are separate managers for tables, for each type of index, for transaction status, etc. A complete list of them can be obtained if desired by the command
On disk, the log is stored as files in the $ PGDATA / pg_wal directory. Each file defaults to 16 MB. Size can be increased to avoid a large number of files in one directory. Prior to PostgreSQL 11, this could only be done when compiling the source code, but now the size can be specified when initializing the cluster (key
Log entries fall into the current file in use; when it ends, the next one begins to be used.
Special buffers are allocated for the log in the server’s shared memory. The size of the journal cache is set by the wal_buffers parameter (the default value implies automatic configuration: 1/32 of the buffer cache is allocated).
The journal cache is arranged like a buffer cache, but it works primarily in the ring buffer mode: entries are added to the "head" and written to the disk from the "tail".
The recording (“tail”) and insertion (“head”) positions show the functions pg_current_wal_lsn and pg_current_wal_insert lsn respectively:
In order to refer to a specific record, the data type pg_lsn (LSN = log sequence number) is used - this is a 64-bit number representing the byte offset before the record relative to the beginning of the log. LSN is output as two 32-bit numbers in hexadecimal notation.
You can find out in which file we will find the desired position, and with what offset from the beginning of the file:
The file name consists of two parts. The upper 8 hexadecimal digits show the number of the time branch (it is used when restoring from the backup), the remainder corresponds to the highest LSN digits (and the remaining lower LSN digits indicate the offset).
Log files can be viewed on the file system in the $ PGDATA / pg_wal / directory, but starting with PostgreSQL 10 they can also be seen with a special function:
Let's see how journaling occurs and how proactive recording is provided. Create a table:
We will look at the header of the table page. To do this, we need an already familiar extension:
Let's start the transaction and remember the insertion position in the log:
Now let's do some operation, for example, update the line:
This change was recorded in the log, the insertion position has changed:
To ensure that the modified data page is not pushed to disk earlier than the journal entry, the LSN of the last journal entry related to this page is stored in the page header:
Keep in mind that the journal is common to the entire cluster, and new entries fall into it all the time. Therefore, the LSN on the page may be less than the value that the pg_current_wal_insert_lsn function just returned. But nothing happens in our system, so the numbers are the same.
Now complete the transaction.
The commit record also goes to the log, and the position changes again:
Commit changes the status of a transaction in a structure called XACT (we already talked about it ). Statuses are stored in files, but they also use their own cache, which occupies 128 pages in shared memory. Therefore, for XACT pages, the LSN of the last journal entry has to be tracked. But this information is not stored in the page itself, but in RAM.
At some point, the created journal entries will be written to disk. In which one - we will talk another time, but in our case this has already happened:
After this point, the data and XACT pages can be pushed out of the cache. But if it were required to force them out earlier, it would be detected and the journal entries would be forced to be recorded first.
Knowing the two LSN positions, you can get the size of the journal entries between them (in bytes) by simply subtracting one position from the other. You just need to cast the positions to pg_lsn type:
In this case, the line update and commit required 108 bytes in the log.
In the same way, you can estimate how much journal entries are generated by the server per unit of time at a certain load. This is important information that will be required during setup (which we will talk about next time).
Now we will use the pg_waldump utility to look at the created log entries.
The utility can work with the LSN range (as in this example) and select records for the specified transaction. It should be run on behalf of the postgres OS user, since she needs access to the log files on the disk.
Here we see the headers of the two entries.
The first is the HOT_UPDATE operation , related to the Heap resource manager. The file name and page number are indicated in the blkref field and match the updated table page:
The second entry is COMMIT, related to the Transaction Resource Manager.
Not the most readable format, but you can figure it out if necessary.
When we start the server, the postmaster process starts first, and it, in turn, starts the startup process, the task of which is to ensure recovery if a failure occurs.
To determine if recovery is required, startup looks into the special control file $ PGDATA / global / pg_control and looks at the cluster status. We can check the status ourselves using the pg_controldata utility:
A neatly stopped server will have the status “shut down”. If the server does not work, and the status remains “in production”, this means that the DBMS has fallen and then recovery will be automatically performed.
For recovery, the startup process will sequentially read the log and apply entries to the pages, if necessary. You can verify the need by comparing the LSN of the page on the disk with the LSN of the journal entry. If the LSN of the page is larger, then the record is not necessary. But in fact - it’s not even possible, because the records are designed for strictly consistent application.
Changing pages during recovery occurs in the buffer cache, as during normal work - for this postmaster starts the necessary background processes.
Similarly, journal entries apply to files: for example, if a record says that the file must exist, but it does not exist, the file is created.
Well, at the very end of the recovery process, all non-journaled tables are overwritten with "dummies" from their init layers .
This is a very simplified presentation of the algorithm. In particular, we have not said anything about where to start reading journal entries (this conversation will have to be postponed until the checkpoint is considered).
And the last clarification. "Classic" recovery process consists of two phases. In the first phase (roll forward), journal entries are rolled, and the server repeats all the work lost during the failure. On the second (roll back), transactions that were not committed at the time of the failure are rolled back. But PostgreSQL does not need a second phase. As we considered earlier , due to the peculiarities of the implementation of multi-version transactions do not need to be rolled back physically; it is enough that the fix bit will not be set in XACT.
To be continued .
Journal
Alas, miracles do not happen: in order to survive the loss of information in RAM, everything necessary must be written to a disk (or other non-volatile device) in a timely manner.
Therefore, this is what has been done. Along with data changes, a journal of these changes is also kept . When we change something on a page in the buffer cache, we create a record in the log about this change. The record contains the minimum information sufficient so that, if necessary, the change can be repeated.
For this to work, the journal entry must necessarily go to disk before the modified page gets there. Hence the name: PreRecord magazine (write-ahead log).
If a failure occurs, the data on the disk is in an inconsistent state: some pages were written earlier, some later. But there remains a journal that can be read and re-performed by those operations that have already been completed before the failure, but whose result did not reach the disk.
Why not force the data pages themselves to be written to disk, why do double jobs instead? It turns out so effective.
First of all, a log is a sequential stream of data to write. Even HDDs do pretty well with sequential recording. But the record of the data itself is random, because the pages are scattered across the disk more or less randomly.
Secondly, a journal entry can be much smaller than a page.
Thirdly, when recording, you don’t have to worry about ensuring that the data on the disk remains consistent at any arbitrary point in time (this requirement greatly complicates life).
And fourthly, as we will see later, the journal (since it exists) can be used not only for recovery, but also for backup and replication.
You need to log all operations, during which there is a risk of inconsistency on the disk in the event of a failure. In particular, the following actions are logged:
- changing pages in the buffer cache (as a rule, these are tables and index pages) - since the changed page does not immediately go to disk;
- committing and canceling transactions - the status change occurs in the XACT buffers and also does not immediately reach the disk;
- file operations (creating and deleting files and directories, for example, creating files when creating a table) - since these operations must occur simultaneously with data changes.
Not logged:
- operations with non-journalized (unlogged) tables - their name speaks for itself;
- operations with temporary tables - it makes no sense, since the lifetime of such tables does not exceed the lifetime of the session that created them.
Prior to PostgreSQL 10, hash indexes were not logged (they only served to map hash functions to different data types), but now this has been fixed.
Logical device
Logically, a journal can be thought of as a sequence of records of various lengths. Each record contains data about a certain operation, preceded by a standard header . The title, among other things, indicates:
- The transaction number to which the record belongs.
- resource manager - the component of the system responsible for recording;
- checksum (CRC) - allows you to determine data corruption;
- record length and link to the previous record.
The data itself has a different format and meaning. For example, they can represent some fragment of a page that needs to be written over its contents with a certain offset. The specified resource manager “understands” how to interpret the data in its record. There are separate managers for tables, for each type of index, for transaction status, etc. A complete list of them can be obtained if desired by the command
pg_waldump -r list
Physical device
On disk, the log is stored as files in the $ PGDATA / pg_wal directory. Each file defaults to 16 MB. Size can be increased to avoid a large number of files in one directory. Prior to PostgreSQL 11, this could only be done when compiling the source code, but now the size can be specified when initializing the cluster (key
--wal-segsize). Log entries fall into the current file in use; when it ends, the next one begins to be used.
Special buffers are allocated for the log in the server’s shared memory. The size of the journal cache is set by the wal_buffers parameter (the default value implies automatic configuration: 1/32 of the buffer cache is allocated).
The journal cache is arranged like a buffer cache, but it works primarily in the ring buffer mode: entries are added to the "head" and written to the disk from the "tail".
The recording (“tail”) and insertion (“head”) positions show the functions pg_current_wal_lsn and pg_current_wal_insert lsn respectively:
=> SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn();
pg_current_wal_lsn | pg_current_wal_insert_lsn
--------------------+---------------------------
0/331E4E64 | 0/331E4EA0
(1 row)
In order to refer to a specific record, the data type pg_lsn (LSN = log sequence number) is used - this is a 64-bit number representing the byte offset before the record relative to the beginning of the log. LSN is output as two 32-bit numbers in hexadecimal notation.
You can find out in which file we will find the desired position, and with what offset from the beginning of the file:
=> SELECT file_name, upper(to_hex(file_offset)) file_offset
FROM pg_walfile_name_offset('0/331E4E64');
file_name | file_offset
--------------------------+-------------
000000010000000000000033 | 1E4E64
\ /\ /
ветвь 0/331E4E64
времени
The file name consists of two parts. The upper 8 hexadecimal digits show the number of the time branch (it is used when restoring from the backup), the remainder corresponds to the highest LSN digits (and the remaining lower LSN digits indicate the offset).
Log files can be viewed on the file system in the $ PGDATA / pg_wal / directory, but starting with PostgreSQL 10 they can also be seen with a special function:
=> SELECT * FROM pg_ls_waldir() WHERE name = '000000010000000000000033';
name | size | modification
--------------------------+----------+------------------------
000000010000000000000033 | 16777216 | 2019-07-08 20:24:13+03
(1 row)
Forward write
Let's see how journaling occurs and how proactive recording is provided. Create a table:
=> CREATE TABLE wal(id integer);
=> INSERT INTO wal VALUES (1);
We will look at the header of the table page. To do this, we need an already familiar extension:
=> CREATE EXTENSION pageinspect;
Let's start the transaction and remember the insertion position in the log:
=> BEGIN;
=> SELECT pg_current_wal_insert_lsn();
pg_current_wal_insert_lsn
---------------------------
0/331F377C
(1 row)
Now let's do some operation, for example, update the line:
=> UPDATE wal set id = id + 1;
This change was recorded in the log, the insertion position has changed:
=> SELECT pg_current_wal_insert_lsn();
pg_current_wal_insert_lsn
---------------------------
0/331F37C4
(1 row)
To ensure that the modified data page is not pushed to disk earlier than the journal entry, the LSN of the last journal entry related to this page is stored in the page header:
=> SELECT lsn FROM page_header(get_raw_page('wal',0));
lsn
------------
0/331F37C4
(1 row)
Keep in mind that the journal is common to the entire cluster, and new entries fall into it all the time. Therefore, the LSN on the page may be less than the value that the pg_current_wal_insert_lsn function just returned. But nothing happens in our system, so the numbers are the same.
Now complete the transaction.
=> COMMIT;
The commit record also goes to the log, and the position changes again:
=> SELECT pg_current_wal_insert_lsn();
pg_current_wal_insert_lsn
---------------------------
0/331F37E8
(1 row)
Commit changes the status of a transaction in a structure called XACT (we already talked about it ). Statuses are stored in files, but they also use their own cache, which occupies 128 pages in shared memory. Therefore, for XACT pages, the LSN of the last journal entry has to be tracked. But this information is not stored in the page itself, but in RAM.
At some point, the created journal entries will be written to disk. In which one - we will talk another time, but in our case this has already happened:
=> SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn();
pg_current_wal_lsn | pg_current_wal_insert_lsn
--------------------+---------------------------
0/331F37E8 | 0/331F37E8
(1 row)
After this point, the data and XACT pages can be pushed out of the cache. But if it were required to force them out earlier, it would be detected and the journal entries would be forced to be recorded first.
Knowing the two LSN positions, you can get the size of the journal entries between them (in bytes) by simply subtracting one position from the other. You just need to cast the positions to pg_lsn type:
=> SELECT '0/331F37E8'::pg_lsn - '0/331F377C'::pg_lsn;
?column?
----------
108
(1 row)
In this case, the line update and commit required 108 bytes in the log.
In the same way, you can estimate how much journal entries are generated by the server per unit of time at a certain load. This is important information that will be required during setup (which we will talk about next time).
Now we will use the pg_waldump utility to look at the created log entries.
The utility can work with the LSN range (as in this example) and select records for the specified transaction. It should be run on behalf of the postgres OS user, since she needs access to the log files on the disk.
postgres$ /usr/lib/postgresql/11/bin/pg_waldump -p /var/lib/postgresql/11/main/pg_wal -s 0/331F377C -e 0/331F37E8 000000010000000000000033
rmgr: Heap len (rec/tot): 69/ 69, tx: 101085, lsn: 0/331F377C, prev 0/331F3014, desc: HOT_UPDATE off 1 xmax 101085 ; new off 2 xmax 0, blkref #0: rel 1663/16386/33081 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 101085, lsn: 0/331F37C4, prev 0/331F377C, desc: COMMIT 2019-07-08 20:24:13.945435 MSK
Here we see the headers of the two entries.
The first is the HOT_UPDATE operation , related to the Heap resource manager. The file name and page number are indicated in the blkref field and match the updated table page:
=> SELECT pg_relation_filepath('wal');
pg_relation_filepath
----------------------
base/16386/33081
(1 row)
The second entry is COMMIT, related to the Transaction Resource Manager.
Not the most readable format, but you can figure it out if necessary.
Recovery
When we start the server, the postmaster process starts first, and it, in turn, starts the startup process, the task of which is to ensure recovery if a failure occurs.
To determine if recovery is required, startup looks into the special control file $ PGDATA / global / pg_control and looks at the cluster status. We can check the status ourselves using the pg_controldata utility:
postgres$ /usr/lib/postgresql/11/bin/pg_controldata -D /var/lib/postgresql/11/main | grep state
Database cluster state: in production
A neatly stopped server will have the status “shut down”. If the server does not work, and the status remains “in production”, this means that the DBMS has fallen and then recovery will be automatically performed.
For recovery, the startup process will sequentially read the log and apply entries to the pages, if necessary. You can verify the need by comparing the LSN of the page on the disk with the LSN of the journal entry. If the LSN of the page is larger, then the record is not necessary. But in fact - it’s not even possible, because the records are designed for strictly consistent application.
There are exceptions. Some records are formed as a full page image (FPI, full page image), and it is clear that such an image can be applied to a page in any state - it will still erase everything that was there. Another change in the status of a transaction can be applied to any version of the XACT page - therefore, inside such pages there is no need to store LSN.
Changing pages during recovery occurs in the buffer cache, as during normal work - for this postmaster starts the necessary background processes.
Similarly, journal entries apply to files: for example, if a record says that the file must exist, but it does not exist, the file is created.
Well, at the very end of the recovery process, all non-journaled tables are overwritten with "dummies" from their init layers .
This is a very simplified presentation of the algorithm. In particular, we have not said anything about where to start reading journal entries (this conversation will have to be postponed until the checkpoint is considered).
And the last clarification. "Classic" recovery process consists of two phases. In the first phase (roll forward), journal entries are rolled, and the server repeats all the work lost during the failure. On the second (roll back), transactions that were not committed at the time of the failure are rolled back. But PostgreSQL does not need a second phase. As we considered earlier , due to the peculiarities of the implementation of multi-version transactions do not need to be rolled back physically; it is enough that the fix bit will not be set in XACT.
To be continued .
