DEV Community

Cover image for PostgreSQL WAL activities
Bolaji Wahab
Bolaji Wahab

Posted on • Updated on

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**
Enter fullscreen mode Exit fullscreen mode

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**
Enter fullscreen mode Exit fullscreen mode

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**
Enter fullscreen mode Exit fullscreen mode

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**
Enter fullscreen mode Exit fullscreen mode

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**
Enter fullscreen mode Exit fullscreen mode

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**
Enter fullscreen mode Exit fullscreen mode

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**
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
             Mon Jan 31 22:16:15 2022 (every 60s)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode
            Mon Jan 31 22:42:12 2022 (every 60s)
Enter fullscreen mode Exit fullscreen mode
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

Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
dineshparva profile image
dinesh reddy

very useful thanks

Collapse
 
bolajiwahab profile image
Bolaji Wahab • Edited

Good to know you found it useful.