PostgreSQL Replication's second chapter understands the transaction log of PostgreSQL (5)

The internal structure of the 2.5 xlog

We will use transactions throughout this book and give you a more technical insight into what is happening if you work, we have added this part of the internal working mechanism that specializes in dealing with Xlog. We will try to avoid going down to level C, as this will be beyond the scope of this book, but we will provide you with the insight that you want to be deep enough.

2.5.1 Understanding Xlog Records

The changes made to Xlog are record-based. What does that mean? Let's say you're adding a row of data to a table:

test=# INSERT into T_test VALUES (1, ' Hans ');

INSERT 0 1

In this example, we are inserting a table that contains two columns. For this example, we want to assume that these two columns are indexed.

Remember what we learned before: The purpose of xlog is to keep these data files secure. Therefore, this operation will trigger a series of xlog entries. First, the data file associated with the table will be written. The index-related entries are then created. The last commit record is sent to the log.

Not all Xlog records are equal: There are various types of xlog records (for example, Heap,btree,clog,storage,gin, and Standby records, just to name a few).

Xlog Records Backward links. This way, each entry points to the previous entry in the file. In this way, we can fully believe that we have found the end of the record as long as we want to point to the pointer to the previous entry.

make XLOG with certainty

As you can see, a change could trigger many xlog entries. This is true for all kinds of statements, such as a large DELETE statement that can easily cause millions of changes. The reason is that PostgreSQL cannot simply write the SQL itself to the log; it must actually record the physical changes made to the table. Why is this? Consider the following example:

test=# DELETE from t_test WHERE ID > Random ();

DELETE 5335

The function random generates different output each time it is called, so we can't just write the SQL to the log, because if it is executed during replay, it does not guarantee that we will provide the same result.

Make Xlog Reliable

In the entire database instance, Xlog itself is one of the most critical and sensitive parts. Therefore, we must take special care to ensure that all possible things are done to protect it. In the event of a crash, if there is no xlog, the DB instance will usually encounter misfortune.

Internally, PostgreSQL takes special precautions to deal with Xlog:

• Use the CRC32 checksum

• Disable Signal

• Space allocation

Preferred, each Xlog record contains a CRC32 checksum. This allows us to check the integrity of the log at boot time. The last write operation before the crash is completely non-actionable, so a checksum definitely helps to solve the problem. The checksum is calculated automatically by PostgreSQL, and the user does not need to be concerned about this explicit function.

In addition to the checksum, PostgreSQL temporarily disables the signal when writing xlog. This gives security some extra level and, to some extent, reduces the likelihood of a foolish extreme situation.

Finally, PostgreSQL uses a fixed-size xlog. The size of the Xlog is determined by checkpoint segments and Checkpoint_completion_target.

The PostgreSQL transaction log size is calculated as follows:

Checkpoint_segments + wal_keep_segments + 1 files

The important thing is that if the size of something is fixed, it rarely runs out of space.

[in the case of transaction log replication, if the transaction log cannot be archived, we can run out of Xlog directory space.] ]

You can learn more about this topic in the next section.

2.5.2 LSN and shared buffer interaction

If you are repairing a table, you must make sure that the table is repaired in the correct order, which is a disaster if a row of data is deleted before it actually occurs. So Xlog gives you the order of all changes. Internally, this order is reflected by the logical sequence number (LSN). LSN is essential for xlog. Each Xlog entry is assigned directly to an LSN.

In one of the previous chapters, we have discussed the consistency level. Synchronous_commit is set to OFF, even if the Xlog record has not been flushed to disk, the client will get an OK. Because the changes must be reflected in the cache, and because the write xlog must be done before the data table is written, the system must ensure that all blocks that are not in the shared cache can be immediately written out. LSN will guarantee that if the corresponding changes have been written to Xlog, we can only write blocks of data from the shared cache to the data file. Writing Xlog is fundamental, after a crash, violating this rule will certainly cause problems.

Debug the Xlog and put them together

Now that we know the basic workings of xlog, we can put them together and look at the Xlog. To PostgreSQL9.2, it works as follows: We want to compile PostgreSQL from the source code. Before we do this, we should modify the files located in Src/include/pg_config_manual.h. In about 250 lines, we can cancel the Wal_debug comment and compile it properly. This will allow us to set up a client variable called Wal_debug:

test=# SET client_min_messages to log;

SET

test=# SET wal_debug to on;

SET

In addition, we will set up client_min_messages to ensure that log messages will reach our clients.

We use the following table structure for our tests:
test=# \d T_test

Table "Public.t_test"

Column | Type | Modifiers

--------+---------+-----------

ID | Integer |

name | Text |

Indexes:

"IDX_ID" Btree (ID)

"Idx_name" Btree (name)

If PostgreSQL is compiled properly (only in this way), we will see information about Xlog on the screen:

test=# INSERT into T_test VALUES (1, ' Hans ');

Log:insert @ 0/17c4680:prev 0/17c4620; XID 1009; Len 36:heap-

Insert (init): Rel 1663/16384/16394; Tid 0/1

Log:insert @ 0/17c46c8:prev 0/17c4680; XID 1009; Len 20:btree

-Newroot:rel 1663/16384/16397; Root 1 Lev 0

Log:insert @ 0/17c4700:prev 0/17c46c8; XID 1009; Len 34:btree

-Insert:rel 1663/16384/16397; Tid 1/1

Log:insert @ 0/17c4748:prev 0/17c4700; XID 1009; Len 20:btree

-Newroot:rel 1663/16384/16398; Root 1 Lev 0

Log:insert @ 0/17c4780:prev 0/17c4748; XID 1009; Len 34:btree

-Insert:rel 1663/16384/16398; Tid 1/1

Log:insert @ 0/17c47c8:prev 0/17c4780; XID 1009; Len 12:

Transaction-commit:2013-02-25 18:20:46.633599+01

Log:xlog Flush Request 0/17c47f8; Write 0/0; Flush 0/0

As described in this chapter, PostgreSQL will first add a row to the table itself (heap). The Xlog then contains all the entries related to the index. Finally, add a commit record.

In summary, 156 bytes have been successfully written to Xlog, which goes far beyond the data we actually added. Consistency, performance (indicators) and reliability are all equipped with price tags.

2.6 Summary

In this chapter, you have learned the purpose of PostgreSQL's things log. We have extensively discussed the data formats on disk and some very important topics such as consistency and performance. In the next chapter, we'll need all of these topics when we copy our first database.

The next chapter will be built on the topic you just learned, focusing on instant recovery. The goal is to get PostgreSQL back to a specific point in time and provide data that seems to have never happened in the back of the transaction.

Original address: http://www.cnblogs.com/songyuejie/p/4743344.html

Original address: http://www.cnblogs.com/songyuejie/p/4743344.html

PostgreSQL Replication's second chapter understands the transaction log of PostgreSQL (5)