Configuring PostgreSQL 16 Logging
1️⃣ Why Enable Logging?
Track slow queries and bottlenecks
Audit database activity
Debug errors and deadlocks
Analyze query performance trends
2️⃣ Configure postgresql.conf for Effective Logging
Edit your postgresql.conf (usually /var/lib/pgsql/16/data/postgresql.conf):
# Enable logging
logging_collector = on
# Log directory and filenames
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# Log rotation to keep logs manageable
log_rotation_age = 1d
log_rotation_size = 100MB
# What to log
log_min_duration_statement = 500 # Log queries slower than 500 ms
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
# Include errors, checkpoints, connections
log_checkpoints = on
log_connections = on
log_disconnections = on
# Log locks and deadlocks for troubleshooting
log_lock_waits = on
Restart PostgreSQL after changes:
sudo systemctl restart postgresql-16
3️⃣ Log File Location & Permissions
Logs will be stored under /var/lib/pgsql/16/data/pg_log/
Ensure PostgreSQL has write permission
Use sudo ls -lh /var/lib/pgsql/16/data/pg_log/ to check log files
📄 Page 2: Analyzing Logs with pgBadger
4️⃣ Install pgBadger
On CentOS 9:
sudo dnf install perl perl-Time-HiRes perl-DBI perl-JSON
wget https://github.com/darold/pgbadger/releases/latest/download/pgbadger-
12.8.tar.gz
tar -xvzf pgbadger-12.8.tar.gz
cd pgbadger-12.8
perl Makefile.PL
make
sudo make install
Or install from source or package manager if available.
5️⃣ Generate Reports with pgBadger
Analyze log files and generate an HTML report:
pgbadger /var/lib/pgsql/16/data/pg_log/postgresql-2025-10-10_*.log -o
/tmp/pgbadger_report.html
Open report in browser:
xdg-open /tmp/pgbadger_report.html
6️⃣ What pgBadger Reports Include
Top slowest queries
Query execution times & frequency
Lock and deadlock analysis
Connections and disconnections
Checkpoints and WAL activity
Errors and warnings
7️⃣ Automate Regular Reporting (Optional)
Create a cron job to run pgBadger daily:
crontab -e
Add line:
0 2 * * * /usr/local/bin/pgbadger /var/lib/pgsql/16/data/pg_log/postgresql-$
(date +\%Y-\%m-\%d)_*.log -o /var/www/html/pgbadger_report_$(date +\%Y-\%m-\
%d).html
Serve reports via web server or check manually.
Summary Checklist
Step Command/Config
Enable logging logging_collector=on + log settings in postgresql.conf
Restart PostgreSQL sudo systemctl restart postgresql-16
Install pgBadger Download + build or dnf install perl-*
Run pgBadger report pgbadger /path/to/logs -o report.html
Automate reports (optional) Add cron job to run daily
