variantcore

Do you need solve some problems with your queries ?

Or perhaps you are just curious what your ORM is doing behind the scene ?

Here you’ll find how to configure your docker-compose environment to help you understand what statements database is executing.

How we can use this ?

• Tracking slow queries
• Finding locks
• Getting more familiar with your ORM no matter if its (Django ORM or Flask or whatever else)

1. How to write docker-compose file

version: '3.5'
services:
  db:
    container_name: kp_test_container
    restart: always
    image: postgres:latest
    environment:
      POSTGRES_DB: kp_test
      POSTGRES_USER: kp_test
      POSTGRES_PASSWORD: kp_test
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data/
      - logs:/var/log/
    command: postgres -c log_destination='csvlog' -c logging_collector=on -c log_statement=all -c log_directory='/var/log/postgresql' -c log_filename='postgresql-%a.log'
volumes:
  pgdata:
  logs:

Bear in mind to setup your own database name(POSTGRES_USER) / user(POSTGRES_USER) name and password(POSTGRES_PASSWORD).

These settings are living in environment variables because when you run image for 1st time. Script is using these env variables to setup database.

We got 2 volumes:

• pgdata – for all data related to Postgres
• logs – for our csv log files

Below explanation of most important part of configuration

• command: – defines what command will be executed after launching docker-compose up
• postgres -c log_destination=’csvlog’ – defines how to store logs
• -c logging_collector=on – need to be “on” to collect logs
• -c log_statement=all – we will catch all queries not just ex DDLs
• -c log_directory=’/var/log/postgresql’ – place to store logs (bear in mind access righs)
• -c log_filename=’postgresql-%a.log’ – log file name ex. postgresql-Mon.log

2. How to build project

docker-compose build

3. How to launch project

docker-compose up

At the end you should see such log

4. How to connect with container

Container name was adjusted in docker-compose file(kp_test_container)

docker exec -it kp_test_container bash

5. How to track log file

We used -c log_filename='postgresql-%a.log' so we expect postgresql-Tue.log (its Tuesday for me)

tail -f postgresql-Tue.log 

6. Connect to database

Use whatever database IDE tool

7. Execute queries

Now you can try to

• login using bad password
• login using good password
• do some selects on non existing tables / existing tables

So as you see everything is getting logged.

Where to go from here

Perhaps you need more precise configuration or you just don’t want to configure Postgres using docker command. In this case you should be