PostgreSQL WAL activities
Tags: postgres, database, sql, monitoring
Published: 2022-01-31 22:31:34

PostgreSQL is ACID-compliant, ACID meaning
> 
**A**  - Atomicity
**C**  - Consistency
**I**  - Isolation
**D**  - Durability

PostgreSQL has various internal implementations enforcing **ACID** but our focus here is on its **Durability** implementation.

#### Durability
Durability guarantees that transactions that have committed are stored permanently. Such transactions will survive any fault to any component of the database system or failure to the whole system.

Every relational database management system has different ways of enforcing durability but the concept is generally the same.

PostgreSQL uses WAL(Write-Ahead Log) to implement **Durability**. 
Modifications are written to the WAL files before they are written to the data files. This way if the database system should crash for any reason, we are sure not to loose any committed transactions, we simply perform what we call crash recovery.

Both streaming replication and archive recovery rely on WAL files.

WAL files are written to `pg_xlog` (**< PG10**) and `pg_wal`(**>= PG10**).

There are various configuration as regards to WAL. You can checkout the documentation at [WAL configation](https://www.postgresql.org/docs/14/wal-configuration.html)

#### WAL archival
WAL files can be archived. This simply means copying the WAL files somewhere usually outside of the database server. This serves two main purposes:
- Recovery with prior restoration of a basebackup.
- Replication through archive recovery.

Have a look at [Archiving](https://www.postgresql.org/docs/14/runtime-config-wal.html#:~:text=20.5.3.%C2%A0-,Archiving,-archive_mode%20(enum))

WAL files can also be read through `pg_xlogdump`(**< PG10**) and `pg_waldump`(**>= PG10**).

#### Monitoring WAL activities
PostgreSQL does not provide any catalog out of the box for monitoring WAL activities but we can monitor through snapshotting and comparison. 
Few activities we can monitor are:
- WAL generation rate
- WAL archival rate
- WAL archival lag
- WAL recovery rate

We can achieve the above with psql **watch** meta command. We get the actual metrics after a complete cycle(here I used 60s).
##### WAL generation rate
> **PG >= 10**
```
WITH wal_data AS (
    SELECT
        now()::timestamp(0),
        pg_wal_lsn_diff(
            x,
            current_setting($$my.wal_loc$$, true)::pg_lsn
        ) AS wal_data_generated,
        set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
    FROM pg_current_wal_lsn() AS l(x)
)
SELECT
    now,
    round(wal_data_generated / y.bytes_per_wal_segment, 2) AS wal_files_generated_per_minute,
    pg_size_pretty(wal_data_generated) AS wal_data_generated_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_generated IS NOT NULL
\watch 60
```
> **PG < 10**
```
WITH wal_data AS (
    SELECT
        now()::timestamp(0),
        pg_xlog_location_diff(
            x,
            current_setting($$my.wal_loc$$, true)::pg_lsn
        ) AS wal_data_generated,
        set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
    FROM pg_current_xlog_location() AS l(x)
)
SELECT
    now,
    round(wal_data_generated / y.bytes_per_wal_segment, 2) AS wal_files_generated_per_minute,
    pg_size_pretty(wal_data_generated) AS wal_data_generated_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_generated IS NOT NULL
\watch 60
```
##### WAL archival rate
> **PG >= 10**
```
WITH archival_data AS (
    SELECT
        now()::timestamp(0),
        CASE
            WHEN current_setting('archive_mode') IN ('on', 'always') THEN
            pg_wal_lsn_diff(
                regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn,
                current_setting($$my.wal_loc$$, true)::pg_lsn
            )
            ELSE NULL
        END AS wal_data_archived,
        CASE
            WHEN current_setting('archive_mode') IN ('on', 'always') THEN
            set_config($$my.wal_loc$$::text, regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn::text, false)
            ELSE NULL
        END AS wal_pos
    FROM pg_stat_archiver
)
SELECT
    now,
    round(wal_data_archived / y.bytes_per_wal_segment, 2)AS wal_files_archived_per_minute,
    pg_size_pretty(wal_data_archived) AS wal_data_archived_per_minute
FROM archival_data, pg_control_init() AS y
WHERE wal_data_archived IS NOT NULL
\watch 60
```
> **PG < 10**
```
WITH archival_data AS (
    SELECT
        now()::timestamp(0),
        CASE
            WHEN current_setting('archive_mode') IN ('on', 'always') THEN
            pg_xlog_location_diff(
                regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn,
                current_setting($$my.wal_loc$$, true)::pg_lsn
            )
            ELSE NULL
        END AS wal_data_archived,
        CASE
            WHEN current_setting('archive_mode') IN ('on', 'always') THEN
            set_config($$my.wal_loc$$::text, regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn::text, false)
            ELSE NULL
        END AS wal_pos
    FROM pg_stat_archiver
)
SELECT
    now,
    round(wal_data_archived / y.bytes_per_wal_segment, 2)AS wal_files_archived_per_minute,
    pg_size_pretty(wal_data_archived) AS wal_data_archived_per_minute
FROM archival_data, pg_control_init() AS y
WHERE wal_data_archived IS NOT NULL
\watch 60
```
##### WAL archival lag
> **PG >= 10**
```
SELECT
    now()::timestamp(0),
    CASE
        WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
        (round(pg_wal_lsn_diff(pg_current_wal_lsn(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric,2) - 1
        )
        WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
        (round(pg_wal_lsn_diff(pg_last_wal_replay_lsn(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric,2) - 1
        )
        ELSE 0
    END AS wal_files_ready_to_be_archived,
    CASE
        WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
        pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
        )
        WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
        pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_replay_lsn(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
        )
        ELSE '0 MB'
    END AS archival_lag
FROM pg_stat_archiver, pg_control_init() AS y
```
> **PG < 10**
```
SELECT
    now()::timestamp(0),
    CASE
        WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
        (round(pg_xlog_location_diff(pg_current_xlog_location(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric, 2) - 1
        )
        WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
        (round(pg_xlog_location_diff(pg_last_xlog_replay_location(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) / y.bytes_per_wal_segment::numeric, 2) - 1
        )
        ELSE 0
    END AS wal_files_ready_to_be_archived,
    CASE
        WHEN current_setting('archive_mode') IN ('on', 'always') AND NOT pg_is_in_recovery() THEN
        pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
        )
        WHEN current_setting('archive_mode') IN ('on', 'always') AND pg_is_in_recovery() THEN
        pg_size_pretty(pg_xlog_location_diff(pg_last_xlog_replay_location(),
            regexp_replace(last_archived_wal, $$^\w{8}(\w{8})(0{6})(\w{2})$$, $$\1/\3\2$$)::pg_lsn) - y.bytes_per_wal_segment
        )
        ELSE '0 MB'
    END AS archival_lag
FROM pg_stat_archiver, pg_control_init() AS y
```
##### WAL recovery rate
> **PG >= 10**
```
WITH wal_data AS (
    SELECT
        now()::timestamp(0),
        CASE
            WHEN pg_is_in_recovery() THEN
            pg_wal_lsn_diff(
                x,
                current_setting($$my.wal_loc$$, true)::pg_lsn
            )
            ELSE NULL
        END AS wal_data_replayed,
        set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
    FROM pg_last_wal_replay_lsn() AS l(x)
)
SELECT
    now,
    round(wal_data_replayed / y.bytes_per_wal_segment, 2) AS wal_files_replayed_per_minute,
    pg_size_pretty(wal_data_replayed) AS wal_data_replayed_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_replayed IS NOT NULL
\watch 60
```
> **PG < 10**
```
WITH wal_data AS (
    SELECT
        now()::timestamp(0),
        CASE
            WHEN pg_is_in_recovery() THEN
            pg_xlog_location_diff(
                x,
                current_setting($$my.wal_loc$$, true)::pg_lsn
            )
            ELSE NULL
        END AS wal_data_replayed,
        set_config($$my.wal_loc$$::text, x::text, false) AS wal_pos
    FROM pg_last_xlog_replay_location() AS l(x)
)
SELECT
    now,
    round(wal_data_replayed / y.bytes_per_wal_segment, 2) AS wal_files_replayed_per_minute,
    pg_size_pretty(wal_data_replayed) AS wal_data_replayed_per_minute
FROM wal_data, pg_control_init() AS y
WHERE wal_data_replayed IS NOT NULL
\watch 60
```
Example output:
- WAL generation rate
```
                 Mon Jan 31 22:16:15 2022 (every 60s)

 now | wal_files_generated_per_minute | wal_data_generated_per_minute
-----+--------------------------------+-------------------------------
(0 rows)

Time: 40.007 ms
                         Mon Jan 31 22:17:15 2022 (every 60s)

         now         | wal_files_generated_per_minute | wal_data_generated_per_minute
---------------------+--------------------------------+-------------------------------
 2022-01-31 21:17:16 |                         266.23 | 4260 MB
(1 row)

Time: 27.568 ms
```
- WAL archival rate
```
now | wal_files_archived_per_minute | wal_data_archived_per_minute
-----+-------------------------------+------------------------------
(0 rows)

Time: 62.165 ms
                        Mon Jan 31 23:22:13 2022 (every 60s)

         now         | wal_files_archived_per_minute | wal_data_archived_per_minute
---------------------+-------------------------------+------------------------------
 2022-01-31 22:22:13 |                        213.00 | 3408 MB
(1 row)

Time: 44.091 ms
```
- WAL archival lag
```
         now         | wal_files_ready_to_be_archived | archival_lag
---------------------+--------------------------------+--------------
 2022-01-31 21:41:19 |                           1.02 | 16 MB
(1 row)

Time: 35.998 ms
```
- WAL recovery rate
```
                Mon Jan 31 22:42:12 2022 (every 60s)

 now | wal_files_replayed_per_minute | wal_data_replayed_per_minute
-----+-------------------------------+------------------------------
(0 rows)

Time: 31.892 ms
                        Mon Jan 31 22:43:12 2022 (every 60s)

         now         | wal_files_replayed_per_minute | wal_data_replayed_per_minute
---------------------+-------------------------------+------------------------------
 2022-01-31 21:43:13 |                        251.53 | 4024 MB
(1 row)

Time: 25.209 ms
```