WAL, LSN and File NamesPostgreSQL stores changes that is going to apply to data into the Write Ahead Logs (WALs), that usually are
16 MBeach in size, even if you can configure your cluster (starting from version 11) to different sizes.
PostgreSQL knows at which part of the
16 MBfile (named segment) it is by an offset that is tied to the Log Sequence Number (LSN). Let’s see those in action.
First of all, let’s get some information about the current status:
The server is currently using the WAL file named
testdb=> SELECT pg_current_wal_lsn(), pg_walfile_name( pg_current_wal_lsn() );; -[ RECORD 1 ]------|------------------------- pg_current_wal_lsn | C/CE7BAD70 pg_walfile_name | 000000010000000C000000CE
000000010000000C000000CE. It is possible to see the relationship between the LSN, currently
C/CE7BAD70and the WAL file name as follows. The LSN is made up by three pieces:
Xrepresents the middle part of the WAL file name, one or two symbols;
YYrepresents the final part of the WAL file name;
ZZZZZZare six symbols that represents the offset within the file name.
Therefore, given the LSN
C/CE7BAD70we can assume that the middle part of the WAL file name will be
Cand the last part will be
CE, both zero padded to 8 symbols, so respectively
000000CE. Concatenated togehter, they provide us with a file name that ends with
0000000C000000CE. The initial part of the filename is still missing, and that is the timeline the server is running on, in this case
1, zero padded as the other parts, so
00000001that provides us the final name
To summarize, the following is the correspondance between the single parts:
LSN -> C / CE 7BAD70 WAL -> 00000001 0000000C 000000CE
Please consider that the above example is just to show you the concept, but it is better to use the function pg_walfile_name() to get the exact WAL file name from an LSN since WAL switch may lead to incorrect result from the LSN “manual decoding”.
The final part of the LSN is the offset within the WAL file, and it does suffice to convert it to
intto get an idea:
You can get the same information with the special function
testdb=> SELECT ( x'7BAD70' )::int AS offset; -[ RECORD 1 ]--- offset | 8105328
pg_walfile_name_offset(), to which you can pass the LSN, and get the current filename and the offset in a single run:
testdb=> SELECT ( x'7BAD70' )::int AS offset_computed, pg_walfile_name_offset( 'C/CE7BAD70' ); -[ RECORD 1 ]----------|----------------------------------- offset_computed | 8105328 pg_walfile_name_offset | (000000010000000C000000CE,8105328)
To summarize, given a specific LSN the database is (and must be) clearly aware of the WAL file segment the LSN refers to and to the exact offset, within such file, where the data can be found.