PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

Assuming our logs are located at /db/pgdata/pg_log, follow these steps to examine the checkpoint activity:

1. Execute this command to find the most recent log file:

ls -lt /db/pgdata/pg_log/postgres*.log | head -n 1

2. If the latest log is named postgresql-2019-10-16.log, view all the checkpoints in this log with the following command:

grep checkpoint /db/pgdata/pg_log/postgresql-2019-10-16.log

3. Execute the following command to obtain the five longest disk syncs:

grep 'checkpoint complete:' \
     /db/pgdata/pg_log/postgresql-2019-10-16.log \
     | sed 's/.* sync=/sync=/;' \
     | sed 's/total=[0-9.]* s; //; s/; dist.*//;' \
     | sort -n | tail -n 5