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
3. How to launch project
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
-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