PGBadger | Postgresql log analysis made easy

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
• Documentation

How to use pgBadger

Install pgBadger

brew install pgbadger Link

Set up logging in postgresql

This can be done in multiple ways (You can refer to pgbadger documentation for more details pgBadger postgres 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.

# 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

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

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

View the report

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

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.