PostgreSQL WAL activities

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)

(0 rows)

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

(1 row)

Time: 27.568 ms

- WAL archival rate

(0 rows)

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

(1 row)

Time: 44.091 ms

- WAL archival lag

(1 row)

Time: 35.998 ms

- WAL recovery rate

            Mon Jan 31 22:42:12 2022 (every 60s)

(0 rows)

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

(1 row)

Time: 25.209 ms