Share via:

Post Views: 0

WAL Internals in PostgreSQL

Write-Ahead Log (WAL) is a very important term in transaction processing. In PostgreSQL, it is also known as a transaction log. A log is a record of all the events or changes and WAL data is just a description of changes made to the actual data. So, it is ‘data about data or metadata’.

The WAL mechanism was first implemented in version 7.1 to mitigate the impacts of server crashes. It also made possible the implementation of the Point-in-Time Recovery (PITR) and Streaming Replication (SR). The term ‘Write-Ahead Log’ implies that any change that you make to the database must first be appended to the log file, and then the log file should be flushed to the disk.

Architecture:

A WAL segment is a 16 MB file, by default, and it is internally divided into pages of 8192 bytes (8 KB).

The first page has a header-data defined by the structure XLogLongPageHeaderData, while the headings of all other pages have the page information defined by the structure XLogPageHeaderData. Following the page header, WAL records are written on each page from the beginning in descending order. See Fig above.

The first WAL segment file is 000000010000000000000001. If the first one is filled up with the writing of WAL records, the second one 000000010000000000000002 would be provided. Files of the successor are used in ascending order in succession, after 0000000100000000000000FF has been filled up, the next one 000000010000000100000000 will be provided. In this way, whenever the last 2-digit carries over, the middle 8-digit number increases one. Also, remember every WAL record written also consists of its own header.

WAL files are placed under the data directory of PG. In the recent versions, pg_wal is the folder under which you can find all the WAL files located ($PGData/pg_wal).

WAL Administration in Postgres

Let us take some simple examples to understand how to deal with WAL Logs.

Ex1: If using your DB, you want to find the current WAL which is in use, you can run the following command. Pg_current_wal_lsn will list the current file which is being used.

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
-----------------------------------------------------------
000000010000000000000001
(1 row)

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());

pg_walfile_name

-----------------------------------------------------------

000000010000000000000001

(1 row)

Ex2: In case, you would like to do a manual switch of the WAL file, you will have to run the pg_switch_wal function. Look at the fig below, I have switched the log file and it has now moved to 02 as my current WAL file.

postgres=# SELECT pg_switch_wal( );
  pg_switch_wal
------------------------------------------------------------
  0/1BAEOA8
(1 row)

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
  pg_walfile_name
-------------------------------------------------
  000000010000000000000002
(1 row)

postgres=# SELECT pg_switch_wal( );

pg_switch_wal

------------------------------------------------------------

0/1BAEOA8

(1 row)

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());

pg_walfile_name

-------------------------------------------------

000000010000000000000002

(1 row)

Switch it again and see the result.

postgres=# select pg_current_wal_lsn( );
     pg_current_wal_isn
-------------------------------------------
     0/3000140
(1 row)

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name
---------------------------------------------------------
     000000010000000000000003
(1 row)

postgres=# select pg_current_wal_lsn( );

pg_current_wal_isn

-------------------------------------------

0/3000140

(1 row)

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());

pg_walfile_name

---------------------------------------------------------

000000010000000000000003

(1 row)

PostgreSQL switches to a new WAL segment file under the following conditions:

The WAL segment has been filled
The function pg_switch_wal has been
archive_mode is enabled and the time set to archive_timeout has been

After they are switched out, WAL files can either be removed or recycled—i.e., renamed and reused for the future. The number of WAL files that the server would retain at any point in time depends on server configuration and server activity.

Ex3: You can see these files created physically under the pg_wal directory. As stated earlier, the pg_wal directory is created under the data directory which resides under your PG home.

Here

PG home is /dev/PG10
$PGData is /dev/PG10/data

You can see 3 WAL segment file 16MB of size has been created.

[postgres@gg-demo-vm pg_wal]$ ls -ltr
total 49152
drwx-------2 postgres postgres 40 Oct 25 05:29 archive_status
-rw--------1 postgres postgres 16777216 Dec 22 05:56 000000010000000000000001
-rw--------1 postgres postgres 16777216 Dec 22 05:56 000000010000000000000002
-rw--------1 postgres postgres 16777216 Dec 22 05:56 000000010000000000000003
[postgres@gg-demo-vm pg _wal]$ pwd
/dev/PG10/data/pg_wal

[postgres@gg-demo-vm pg_wal]$ ls -ltr

total 49152

drwx-------2 postgres postgres 40 Oct 25 05:29 archive_status

-rw--------1 postgres postgres 16777216 Dec 22 05:56 000000010000000000000001

-rw--------1 postgres postgres 16777216 Dec 22 05:56 000000010000000000000002

-rw--------1 postgres postgres 16777216 Dec 22 05:56 000000010000000000000003

[postgres@gg-demo-vm pg _wal]$ pwd

/dev/PG10/data/pg_wal

You can also check the count of WAL segment files with the below command directly from the database prompt.

postgres=# SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';
count
--------
3
(1 row)

postgres=# SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';

count

--------

(1 row)

Ex4: Contents of the WAL files can be read by using the pg_waldump utility. You need to run the utility along with the WAL segment file name to check what exactly is stored in the WAL files.

See the fig below, I have tried to filter out only INSERT commands from the WAL file. This utility is available from 9.3+ version onwards.

Ex5: The number of WAL segment files in the pg_wal directory depends on min_wal_size, max_wal_size, and the amount of WAL generated in previous checkpoint cycles. When old log segment files are no longer needed, they are removed or recycled (that is, renamed to become future segments in the numbered sequence).

You can check if the archiving of the WAL is enabled or not using pg_settings.

postgres=# select name, setting from pg_settings where name like 'archive%' ;

name                | setting
---------------------------------------------------------------------------------------
archive_command     | (disabled)
archive_mode        | off
archive_timeout     | 0

(3 rows)

postgres=# select name, setting from pg_settings where name like 'archive%' ;

name | setting

---------------------------------------------------------------------------------------

archive_command | (disabled)

archive_mode | off

archive_timeout | 0

(3 rows)

You can run the following commands to change the archive mode. You would need to set the archive_command parameter carefully.

postgres=# alter system set archive_mode-on;
ALTER SYSTEM
postgres=# alter system set archive_command='test! -f /dev/PG10/data/pg_wal/archive/%f && cp %p /dev/PG10/data/pg_wal/archive/%f';
ALTER SYSTEM
postgres=#

postgres=# alter system set archive_mode-on;

ALTER SYSTEM

postgres=# alter system set archive_command='test! -f /dev/PG10/data/pg_wal/archive/%f && cp %p /dev/PG10/data/pg_wal/archive/%f';

ALTER SYSTEM

postgres=#

postgres=# select name, setting from pg_settings where name like 'archive%' ;
name            | setting
-------------------------------------------------------------------------------------------------------
archive_command | test ! -f /dev/PG10/data/pg_wal/archive/%f && cp %p /dev/PG10/data/pg_wal/archive/%f
archive_mode    | on
archive_timeout | 0
(3 rows)

postgres=# select name, setting from pg_settings where name like 'archive%' ;

name | setting

-------------------------------------------------------------------------------------------------------

archive_command | test ! -f /dev/PG10/data/pg_wal/archive/%f && cp %p /dev/PG10/data/pg_wal/archive/%f

archive_mode | on

archive_timeout | 0

(3 rows)

These parameters would need the Postgres server bounce to take effect. Alternatively, you can set these parameters directly in the PostgreSQL.conf file.