Logging

Logging

Auswertung

pgBadger

• http://dalibo.github.io/pgbadger/

Beispielscript

#!/bin/bash
outdir=/var/www/reports
begin=$(date +'%Y-%m-%d %H:00:00' -d '-1 day')
end=$(date +'%Y-%m-%d %H:00:00')
outfile="$outdir/daily-$(date +'%H').html"

pgbadger -q -b "$begin" -e "$end" -o "$outfile "
  /var/log/postgres.log.1 /var/log/postgres.log

grep

egrep "FATAL|ERROR" /var/log/postgres.log

pg_{statstatement}– Echtzeitauswertung

Weiterführende Informationen:

https://www.postgresql.org/docs/current/static/pgstatstatements.html

Adminrechte um das Modul zu aktivieren

In der postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Als superuser:

CREATE EXTENSION pg_stat_statements;

Auswertung: meist benutzten Abfragen

SELECT query FROM pg_stat_statements ORDER BY calls DESC;

Welche Abfragen benötigen die meiste Zeit

SELECT query, total_time/calls AS avg, calls
       FROM pg_stat_statements ORDER BY 2 DESC;