PostgreSQL WAL activities

#postgres #database #sql #monitoring

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

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

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