Log Analysis Without pgBadger
Configure slow-query logging and csvlog properly, discover pgBadger is not on this box, and build the same report by hand with grep and awk
pgBadger is the standard tool for turning a PostgreSQL csvlog into a browsable slow-query report — but it is a separate Perl program, not part of PostgreSQL itself, and this training VM is a minimal image with neither Perl nor pgBadger installed. The honest first step on any unfamiliar server, exactly as with pgaudit back in Lab 2.2.5, is to check rather than assume: confirm the tool is actually there before building a runbook around it.\n\nThat absence does not excuse skipping log configuration — it changes what happens after the logs exist. This lab configures log_min_duration_statement so only genuinely slow statements get logged, switches to csvlog for structured output, and turns on the logging_collector that actually writes those logs to a file in the first place. Then, instead of pgBadger's HTML report, you will pull the same answer — which queries were slow, and by how much — with grep and awk, tools that are always available.
Confirm Before You Assume
which pgbadger
pgbadger --version
If both come back empty or with "not found", do not write a runbook step that depends on pgBadger existing on this server — verify first, exactly like the pgaudit check in Lab 2.2.5.
Log Only What Matters
ALTER SYSTEM SET logging_collector = on;
ALTER SYSTEM SET log_destination = 'csvlog';
ALTER SYSTEM SET log_min_duration_statement = 200; -- milliseconds
log_min_duration_statement in milliseconds logs only statements that took at least that long — set it too low and every query floods the log; set it to -1 (the default) and nothing gets logged at all regardless of how slow it was.
Why This One Needs a Restart
logging_collector has context postmaster in pg_settings — it controls whether a background process exists at all to receive and write log output, and that process is only ever started or stopped at postmaster startup. log_destination and log_min_duration_statement both take effect on a plain reload; logging_collector does not.
as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w
Finding and Reading the Log
as-postgres ls -la /var/lib/postgresql/18/data/log
as-postgres sh -c 'grep -o "duration: [0-9.]* ms" /var/lib/postgresql/18/data/log/*.csv'
Even inside csvlog's comma-separated format, grep -o with a plain pattern still finds the "duration: N ms" text pgBadger itself would parse — commas inside a quoted CSV field do not break a simple substring match the way they would break naive field-splitting.
Ranking Without pgBadger
as-postgres sh -c "grep -o 'duration: [0-9.]* ms' /var/lib/postgresql/18/data/log/*.csv | awk -F': ' '{print \$2}' | sort -rn"
awk -F': ' splits each matched line on the colon, keeping only the number-and-unit half; sort -rn then ranks them slowest first. This is the exact question pgBadger's headline report answers — it is just one shell pipeline instead of an HTML page.
log_min_duration_statement
The minimum statement duration, in milliseconds, that gets logged. -1 (the default) disables duration-based logging entirely; 0 logs every single statement regardless of speed, which is useful briefly but floods a busy server's logs; a positive value logs only statements at or above that threshold — the setting used for real slow-query hunting.
csvlog
A log_destination option that writes PostgreSQL's log output as comma-separated values with one fixed, documented column layout per line, instead of the free-form text of the stderr destination. It is what pgBadger itself is built to parse — every field (duration, statement, database, user, and more) is unambiguously separated, rather than embedded in prose.
🔎 Check for pgBadger Before Assuming
Confirm whether pgBadger is actually installed on this box before planning around it.
which pgbadgerpgbadger --versionstudent@lab:~$ which pgbadger student@lab:~$ pgbadger --version -sh: pgbadger: not found
⚙️ Configure Slow-Query Logging
Turn on the logging collector, switch to csvlog, and log only statements slower than 200ms.
as-postgres psql -U postgres -c "ALTER SYSTEM SET logging_collector = on;" -c "ALTER SYSTEM SET log_destination = 'csvlog';" -c "ALTER SYSTEM SET log_min_duration_statement = 200;"student@lab:~$ as-postgres psql -U postgres -c "ALTER SYSTEM SET logging_collector = on;" -c "ALTER SYSTEM SET log_destination = 'csvlog';" -c "ALTER SYSTEM SET log_min_duration_statement = 200;" ALTER SYSTEM ALTER SYSTEM ALTER SYSTEM
🔄 Restart to Start the Collector
logging_collector is postmaster-context — apply it with a restart, not a reload.
as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -wstudent@lab:~$ as-postgres pg_ctl restart -D /var/lib/postgresql/18/data -w waiting for server to shut down.... done server stopped waiting for server to start....2026-06-26 16:02:36.452 UTC [138] LOG: redirecting log output to logging collector process 2026-06-26 16:02:36.452 UTC [138] HINT: Future log output will appear in directory "log". done server started
📁 Find the Log Directory
The student account cannot read PGDATA directly — use as-postgres to see what the collector created.
as-postgres ls -la /var/lib/postgresql/18/data/logstudent@lab:~$ as-postgres ls -la /var/lib/postgresql/18/data/log total 16 drwx------ 2 postgres postgres 4096 Jun 26 16:02 . drwx------ 21 postgres postgres 4096 Jun 26 16:02 .. -rw------- 1 postgres postgres 1716 Jun 26 16:02 postgresql-2026-06-26_160236.csv -rw------- 1 postgres postgres 164 Jun 26 16:02 postgresql-2026-06-26_160236.log
🐢 Generate a Mixed-Speed Workload
Run two fast queries and two artificially slow ones, using pg_sleep to guarantee they cross the 200ms threshold.
psql -U postgres -d beer_db -c "SELECT 1;"psql -U postgres -d beer_db -c "SELECT pg_sleep(0.3);"psql -U postgres -d beer_db -c "SELECT pg_sleep(0.5);"psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_class;"student@lab:~$ psql -U postgres -d beer_db -c "SELECT 1;" SET ?column? ---------- 1 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_sleep(0.3);" SET pg_sleep ---------- (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_sleep(0.5);" SET pg_sleep ---------- (1 row) student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM pg_class;" SET count ------- 453 (1 row)
🔍 Extract the Slow Queries
grep the csvlog for duration entries and confirm only the two slow queries were logged.
as-postgres sh -c 'grep -o "duration: [0-9.]* ms" /var/lib/postgresql/18/data/log/*.csv'student@lab:~$ as-postgres sh -c 'grep -o "duration: [0-9.]* ms" /var/lib/postgresql/18/data/log/*.csv' duration: 343.019 ms duration: 506.957 ms
📊 Rank Them, pgBadger-Style
Pull just the durations with awk and sort them slowest first — the same headline number a pgBadger report would lead with.
as-postgres sh -c "grep -o 'duration: [0-9.]* ms' /var/lib/postgresql/18/data/log/*.csv | awk -F': ' '{print \$2}' | sort -rn"student@lab:~$ as-postgres sh -c "grep -o 'duration: [0-9.]* ms' /var/lib/postgresql/18/data/log/*.csv | awk -F': ' '{print \$2}' | sort -rn" 506.957 ms 343.019 ms
Lab 2.4.5 complete. You configured logging correctly and analyzed it without the tool that is normally used for that:\n\n\n pgBadger checked, not assumed : ✅ confirmed absent, adapted the plan\n log_min_duration_statement : ✅ 200ms threshold, verified selective\n csvlog + logging_collector : ✅ configured, restart applied\n grep + awk analysis : ✅ same ranking pgBadger would produce\n
Enable JavaScript to run the live terminal and track your progress.