For log analysis, pgbadger (https://pgbadger.darold.net/) is the best tool I've come across. Just be sure to adjust your log_line_prefix to include information such as client host/IP, role, database and error codes. Turn on other settings such as logging checkpointing, temp file generation and locking. The more you log, the more that pgbadger can help analyze. Just keep an eye on your log generation since you don't want that to in turn affect your database. If you can write the logs to a different mount point. that can isolate that IO from the database.
For trending other things, I'd recommend starting with something like Prometheus (https://prometheus.io/) & Grafana (https://grafana.com/). Get an exporter set up (sql_exporter is a good one - https://github.com/burningalchemist/sql_exporter) and set up alerts and trending your metrics so you can go back and look at history when those alerts fire. If you need to start scaling bigger, then you can start looking into something like Datadog and since you'll already have a better idea of what you actually need to monitor by then, it won't seem nearly as overwhelming jumping to something like that.
We are currently working on a migration project from DB2 to PostgreSQL. Post-migration, we’re observing several performance issues such as long-running queries and occasional instance crashes. It also appears that some application-side workloads may not be optimized for PostgreSQL.
From a DBA perspective, I’m looking to proactively identify problem areas—such as:
Long-running queries
Jobs/stored procedures consuming high temp space
Queries resulting in sequential scans due to missing indexes
Lock waits, deadlocks, and memory-heavy operations
We already have key parameters enabled (pg_stat_statements, pg_buffercache, etc.), and PostgreSQL is generating logs in .csv format. However, the main challenge is efficiently analyzing these logs and identifying performance bottlenecks at scale (databases ranging from ~1TB to 15TB).
We currently don’t have third-party monitoring tools like Datadog, so I’m looking for recommendations on free or lightweight tools and best practices to:
Parse and analyze PostgreSQL logs (especially CSV logs)
Identify top resource-consuming queries and patterns
Correlate temp usage, memory pressure, and query behavior
Generate actionable insights for the engineering team
Any suggestions on tools, scripts, or approaches that have worked well in similar large-scale environments would be greatly appreciated.
