Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

Cover image for PostgreSQL WAL activities
Bolaji Wahab
Bolaji Wahab

Posted on • Edited on

     

PostgreSQL WAL activities

PostgreSQL is ACID-compliant, ACID meaning

A - Atomicity
C - Consistency
I - Isolation
D - Durability

PostgreSQL has various internal implementations enforcingACID but our focus here is on itsDurability 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 implementDurability.
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 topg_xlog (< PG10) andpg_wal(>= PG10).

There are various configuration as regards to WAL. You can checkout the documentation atWAL 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 atArchiving

WAL files can also be read throughpg_xlogdump(< PG10) andpg_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 psqlwatch 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 modeExit 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 modeExit 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 modeExit 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 modeExit 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 modeExit 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 modeExit 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 modeExit 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 modeExit fullscreen mode
             Mon Jan 31 22:16:15 2022 (every 60s)
Enter fullscreen modeExit fullscreen mode
nowwal_files_generated_per_minutewal_data_generated_per_minute

(0 rows)

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

nowwal_files_generated_per_minutewal_data_generated_per_minute
2022-01-31 21:17:16266.234260 MB

(1 row)

Time: 27.568 ms

- WAL archival rate
Enter fullscreen modeExit fullscreen mode
nowwal_files_archived_per_minutewal_data_archived_per_minute

(0 rows)

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

nowwal_files_archived_per_minutewal_data_archived_per_minute
2022-01-31 22:22:13213.003408 MB

(1 row)

Time: 44.091 ms

- WAL archival lag
Enter fullscreen modeExit fullscreen mode
nowwal_files_ready_to_be_archivedarchival_lag
2022-01-31 21:41:191.0216 MB

(1 row)

Time: 35.998 ms

- WAL recovery rate
Enter fullscreen modeExit fullscreen mode
            Mon Jan 31 22:42:12 2022 (every 60s)
Enter fullscreen modeExit fullscreen mode
nowwal_files_replayed_per_minutewal_data_replayed_per_minute

(0 rows)

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

nowwal_files_replayed_per_minutewal_data_replayed_per_minute
2022-01-31 21:43:13251.534024 MB

(1 row)

Time: 25.209 ms

Enter fullscreen modeExit fullscreen mode

Top comments(2)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss
CollapseExpand
 
dineshparva profile image
dinesh reddy
  • Joined

very useful thanks

CollapseExpand
 
bolajiwahab profile image
Bolaji Wahab
Database Engineer | PostgreSQL Lover 🐘 | Opensource Lover
  • Location
    Berlin, Germany
  • Work
    Senior Database Engineer
  • Joined
• Edited on• Edited

Good to know you found it useful.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

Database Engineer | PostgreSQL Lover 🐘 | Opensource Lover
  • Location
    Berlin, Germany
  • Work
    Senior Database Engineer
  • Joined

More fromBolaji Wahab

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp