PostgreSQL log number LSN précis-writers

After figuring out the principle of the log number, a little bit of forgetting about it for a while, simply tidying it up again:

(i) Wal file naming rules

1) Xlog log file (10.1 becomes Pg_wal) in the Pg_xlog directory below the $pgdata directory:

Total 165896
-rw-r--r--1 apple staff 660K 15:46 000000010000000000000013.dump
-RW-------1 apple staff 295B 16:12 000000010000000000000014.00000028.backup
-RW-------1 apple staff 16M 12 15 16:28 000000010000000000000046
-RW-------1 apple staff 16M 12 15 16:30 000000010000000000000047
-RW-------1 apple staff 16M 12 15 16:30 000000010000000000000048
-rw-r--r--1 apple Staff 357K 1 16:27 000000010000000000000044.dump
-RW-------1 apple staff 16M 3 27 09:49 000000010000000000000045
-RW-------1 apple staff 16M 3 27 09:49 000000010000000000000044
DRWX------4 Apple Staff 136B 4 9 10:52 Archive_status

2) The Wal log number is composed of 64 bits, that is, there are 2**64 log number, run out need to reset, but this number has been many, equivalent to 1024P, 1024*1024 T.

The log file consists of 24 16 binary digits, divided into three parts: the time line, LSN high 32 bits, LSN low 32 bit/(2**24) value (that is, the highest 32 bits in low two bits, indicating the LSN starting number of the log file, combined with a specific LSN to facilitate understanding of a point).

(ii) View the specific log number LSN in the database

1) Some of the methods used:

apple=# Select Proname from Pg_proc where proname like ' pg_%_location ';
Proname
---------------------------------
Pg_current_xlog_insert_location
Pg_current_xlog_location
Pg_last_xlog_receive_location
Pg_last_xlog_replay_location
Pg_tablespace_location
(5 rows)

2) The database can be queried for the current log number situation:

apple=# select Pg_current_xlog_location ();
Pg_current_xlog_location
--------------------------
0/45000098
(1 row)

can see:

0 indicates high 32 bits of LSN

45 indicates the last two bits of the corresponding Xlog file

000098 represents the offset byte address of the current LSN in the corresponding Xlog , I remember someone wrote a tool to read the data in the corresponding block in the Xlog and parse it and use it when needed.

The Xlog file corresponding to the log can also be inferred from Current_xlog_location:

Timeline 0000000000000045----> 000000010000000000000045

(iii) LSN when used for recovery

1) Use Pg_controldata to obtain control information from the controls file:

Appledemacbook-pro-2:pg_xlog apple$ Pg_controldata
Pg_control version number:942
Catalog version number:201510051
Database system identifier:6451705940496018968
Database Cluster State:in Production
Pg_control Last Modified: 4/9 10:52:12 2018
Latest Checkpoint location:0/45000028
Prior Checkpoint location:0/446c7878
Latest checkpoint ' s REDO location:0/45000028
Latest checkpoint ' s REDO WAL file:000000010000000000000045
Latest checkpoint ' s timelineid:1
Latest checkpoint ' s prevtimelineid:1
Latest checkpoint ' s Full_page_writes:on
Latest checkpoint ' s nextxid:0/535519
Latest checkpoint ' s nextoid:20278

2) two LSN for recovery

Latest Checkpoint location:0/45000028--Indicates the log number of the checkpoint itself
Prior Checkpoint location:0/446c7878
Latest checkpoint ' s REDO location:0/45000028-Represents the location of the Wal log when checkpoint, and also the starting log number of the log rollback when resuming, That is, starting at 28 bytes in the 45 Wal file, redo the Wal log.

PostgreSQL log number LSN précis-writers