PGBadger | Postgresql log analysis made easy
Tags: postgres, pgbadger
Published: 2023-07-08 06:27:37

---
title: PGBadger | Postgresql log analysis made easy
canonical_url: https://adityanaik.dev/blog/2023/07/02/pgbadger-postgresql-log-analysis-made-easy/
description: Use pgbadger for postgresql log analysis with ease and get a detailed report. It is a must-have tool for postgresql log analysis.
tags:
  - postgresql
  - pgbadger
---
## Why the need for postgresql query log analysis?

While writing sql for any flow, we sometimes focus on the correctness of the query and not on the performance of the query. Sometimes, performance issues creep in when updating/iterating a flow iteratively over multiple devlopment cycles. It becomes important to analyze the performance of the queries, especially to handle any scaling. This is where query log analysis comes in.

In one of our cases, a complicated workflow was significantly slow when we load tested it. We were trying to figure out the bottleneck both in code structure and with query performance. Our go-to tool for query performance analysis became `pgBadger`.

## What is a postgresql query log?

A query log is a log of all the queries that are executed on the database. It is a good practice to enable query logging on the database server. This helps in debugging and performance analysis. The query log contains the following information:

- Query
- database user
- database name
- date and time of query execution
- time taken to execute the query etc.

## What is pgBadger?

The most succinct description comes from `pgBadger` itself:

> pgBadger is a PostgreSQL log analyzer built for speed with fully detailed reports and professional rendering.
> It outperforms any other PostgreSQL log analyzer.

links -

- [github](https://github.com/darold/pgbadger)
- [Documentation](https://pgbadger.darold.net/)

## How to use pgBadger

### Install pgBadger

`brew install pgbadger` [Link](https://formulae.brew.sh/formula/pgbadger)

### Set up logging in postgresql

This can be done in multiple ways (You can refer to `pgbadger` documentation for more details [pgBadger postgres configuration](https://github.com/darold/pgbadger#postgresql-configuration)):

#### 1. Edit postgresql.conf

We need to edit the `postgresql.conf` file to enable logging. We can do this by editing the file directly or by using the `ALTER SYSTEM` command.

```bash
# Edit postgresql.conf
vim /usr/local/var/postgres/postgresql.conf

# OR

# Edit postgresql.conf using ALTER SYSTEM
psql -U postgres -c "ALTER SYSTEM SET log_destination = 'stderr';"
psql -U postgres -c "ALTER SYSTEM SET logging_collector = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_directory = 'logs';"
psql -U postgres -c "ALTER SYSTEM SET log_filename = 'postgresql.log';"
psql -U postgres -c "ALTER SYSTEM SET log_min_duration_statement = 0;"
psql -U postgres -c "ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';"
psql -U postgres -c "ALTER SYSTEM SET log_checkpoints = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_connections = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_disconnections = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_lock_waits = 'on';"
psql -U postgres -c "ALTER SYSTEM SET log_temp_files = 0;"
psql -U postgres -c "ALTER SYSTEM SET log_autovacuum_min_duration = 0;"
psql -U postgres -c "ALTER SYSTEM SET log_error_verbosity = 'verbose';"
```

Make sure you edit the `postgresql.conf` file and not the `postgresql.auto.conf` file. The `postgresql.auto.conf` file is generated by postgresql and will be overwritten when the server restarts.

**OR**

#### 2. Update docker-compose.yml

We update our `docker-compose.yaml` file

```yaml
mydb:
  image: 'postgres:11'
  ports:
    - '5432:5432' # machine:image
  command:
    [
      'postgres',
      '-c',
      'logging_collector=on',
      '-c',
      'log_destination=stderr',
      '-c',
      'log_directory=logs',
      '-c',
      'log_filename=postgresql.log',
      '-c',
      'log_min_duration_statement=0',
      '-c',
      'log_line_prefix=%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ',
      '-c',
      'log_checkpoints=on',
      '-c',
      'log_connections=on',
      '-c',
      'log_disconnections=on',
      '-c',
      'log_lock_waits=on',
      '-c',
      'log_temp_files=0',
      '-c',
      'log_autovacuum_min_duration=0',
      '-c',
      'log_error_verbosity=verbose',
    ]
environment:
  TZ: 'Europe/Stockholm'
POSTGRES_USER: 'postgres'
POSTGRES_PASSWORD: 'postgres'
POSTGRES_DB: 'db_dev'
volumes:
  - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down
  - ./:/var/lib/postgresql/data/logs/ # persist logs even if container shuts down, logs will show up in the current directory
  - ./postgresql.conf:/etc/postgresql/postgresql.conf # custom postgresql config
networks:
  - default
```

#### Restart the database

`docker-compose down && docker-compose up -d` [Link](https://docs.docker.com/compose/reference/down/)

### Generate some queries

Start using your application and generate some logs. You can also generate some logs by running some queries manually OR even running load tests if you have them.

### Run pgBadger

Just ask `pgBadger` to digest logs and create a report at `./report.html`.

`pgbadger -I -O ./report.html ./postgresql.log` [Link](https://pgbadger.darold.net/usage.html)

### View the report

Open the report in your browser and you will see something like this:

![a screenshot of pgbadger dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jytb9p8kovcjahiowe96.png)


It has heaps of information. You can see the top queries, slow queries, errors, etc.

## Conclusion

`pgBadger` is a great tool to analyze postgresql query logs. It is easy to use and provides a lot of information. It is a must-have tool for any postgresql developer really.