What is WAL? WAL is an abbreviation of Write-Ahead Log. That is how PostgreSQL named the transaction log (also known as REDO log). Every modern database system has one. It is basically a history of operations that was executed by a DBMS. It improves performance (you only need to flush one file per transaction) and guarantees ACID properties (if the DBMS was in an inconsistent state -- because of software crash, for example -- it reapplies all transactions that are committed but whose changes are not in the database files). PostgreSQL introduced the transaction log in version 7.1 (2001). It opted to implement only REDO log (there is no UNDO log on PostgreSQL).
The transaction log files are stored in $PGDATA/pg_xlog directory. They are named as 000000020000070A0000008E. They are all hexadecimal digits (0123456789ABCDEF). The first 8 digits identifies the timeline, the following 8 digits identifies the (logical) xlog file and the last ones represents the (physical) xlog file (aka segment). The physical files in pg_xlog directory are not actually the xlog files; PostgreSQL calls it segments. By default, a logical xlog file has 255 segments (16 MB each, 4080 MB in total) So in our example, the 0000070A and 0000008E are the logical xlog file and segment file, respectively. When segment file 000000FE is filled up the logical xlog file is incremented by 1 (i.e. 0000070B) and the segment file sequence starts up again (i.e. 00000000) -- 000000020000070B00000000.
What means 68A/16E1DA8? 68A is the (logical) xlog file and 16E1DA8 is the offset inside the logical xlog file. It means that the offset goes from 00000000 to FF000000 (4278190080 bytes = 4080 MB). When it reaches FF000000, it means we are in the last segment (aka physical xlog file) and the xlog file number will be incremented by 1 (i.e. 68B) and the offset will be restarted (00000000).
PostgreSQL provides a set of functions [1] [2] that deals with the transaction log location. For example, converting transaction log location to segment file name:
postgres=# select pg_xlogfile_name('68A/16E1DA8');
pg_xlogfile_name
--------------------------
000000020000068A00000001
(1 row)
Converting transaction log location to segment file name and the offset inside that segment.
postgres=# select * from pg_xlogfile_name_offset('68A/16E1DA8');
file_name | file_offset
--------------------------+-------------
000000020000068A00000001 | 7216552
(1 row)
If I want to know which segment file the PostgreSQL server is writing the transaction log now:
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000020000074B000000E4
(1 row)
If you have a replication scenario, you can obtain the receive and replay lags. It is a simple hexadecimal math: master transaction log location minus replica transaction log location.
In the replica:
postgres=# select pg_last_xlog_receive_location();
pg_last_xlog_receive_location
-------------------------------
74B/E4D1C628
(1 row)
postgres=# select pg_last_xlog_replay_location();
pg_last_xlog_replay_location
------------------------------
74B/E4D1C628
(1 row)
In the master:
postgres=# select pg_current_xlog_location();
pg_current_xlog_location
--------------------------
74B/E4D3B070
(1 row)
If they report the same xlog file (74B), it is a matter of E4D3B070 - E4D1C628 = 1EA48 (125512 bytes). It means that the replica is roughly 123 kB behind the master. Receive and replay locations aren't the same all the time; if the replica can't keep up with writing receive location > replay location.What about different xlog files? Some tools use the following formula:
(FFFFFFFF * xlog_master + offset_master) - (FFFFFFFF * xlog_replica + offset_replica)
It's wrong ¹. Why? Because the offset goes up to FF000000 (and not FFFFFFFF). It means that the above formula reports 16 MB times the difference between xlog numbers when the xlog numbers aren't the same. The correct formula is:
(FF000000 * xlog_master + offset_master) - (FF000000 * xlog_replica + offset_replica)
Let's suppose the master and replica transaction location are 67E/AFE198 and 67D/FECFA308, respectively. The correct lag is 14696080 bytes and not 31473295 bytes (the difference between results is 16 MB!).
¹ I will send patches to fix check_postgres and Munin. If you know another tool that is using the wrong formula, let me know or even better, the tool's author. FYI, both tools were fixed in a few hours. Thanks, Greg and Magnus!
