How to track Sql statements in Postgres (Docker-compose)

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'
    container_name: kp_test_container
    restart: always
    image: postgres:latest
      POSTGRES_DB: kp_test
      POSTGRES_USER: kp_test
      POSTGRES_PASSWORD: kp_test
      - "5432:5432"
      - 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'

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

PyCharm with database plugin

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
tail -f on file

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

How to share credentials among different kubernetes namespaces

kubernetes helm logo


Lets imagine you have couple projects where:

  • Each is in different namespace
  • All projects (namespaces) are sharing common secrets

What would you do then ?
Unfortunately all settings or secrets are assigned just to specific namespace.


  • We must create secrets in one space and copy to another.
  • Lets assume we get TLS secrets from one namespace.
kubectl get secrets

kubectl get secret dev-cert  -o yaml > dev-cert.yml 
# to get base64 encoded file
  • Next what we can do is checking this certificate
cat dev-cert.yml | grep -Pio "(?<= tls.crt: )(.+)" | base64 --decode | openssl x509 -text > dev-cert.processed
# in case of mac install grep and execute ggrep command
# To see issuer
cat dev-cert.processed | grep -i "Issuer"
# To see for which domain certificate is
cat dev-cert.processed | grep -i "CN =" 
  • Remember to do the apply for ingress after that

Solution 2

kubectl get secret dev-cert --namespace=first-namespace --export -o yaml | kubectl apply --namespace=second-namespace -f -

Browser check

In order to check if everything has been done properly you can check your domain in the browser (chrome/firefox). Just by clicking lock button.

Docker image templating with Jinja

Why to template

Sometimes there is a need to create Dockerfiles dynamically.
Perhaps it’s necessary as part of your CI/CD flow. So let’s try to create python script which is templating Dockerfile.

What is Jinja

Jinja is templating system widely used for templating HTML pages. Typical use case when we use Jinja is situation we want to create web app and render dynamic html pages with frameworks (like flask).
But nobody said you cannot use Jinja for creating dynamic Dockers or any other text files 🙂

Installation / requirements.txt

Only one non std lib package we just need is jinja2. Of course after you install jinja2 from pypy it will install MarkupSafe as well (because of dependencies).

pip install jinja2

So at the moment of writing this article Jinja2 (August 2019) is at 2.10 version



In a template file called Dockerfile_template we will be defining places where we will put content dynamically.

  • Typically each code block is open with “{{” and closed with “}}” inside of brackets you need to put variable name ex. base.
  • In case if we have many elements we need to iterate over using {% for and {% endfor %}
FROM {{ base }}
LABEL maintainer="{{ maintainer }}"

RUN yum install -y  {% for pc in packages %}{{pc}} {% endfor %}

So in our case we will dynamically say

  • what base image to use,
  • who is maintainer of docker file,
  • which additional packages to install in container

How to run script

We need to set couple parameters: base-img, tag, maintainer.
As optional we can specify packages

usage of the script

So lets execute

python --base-img centos -n centos_base -m "Kamil Pazik" -p tcpdump nmap
execution of the script

The script created Dockerfile

FROM centos
LABEL maintainer="Kamil Pazik"

RUN yum install -y  tcpdump nmap