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'
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

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

Scenario

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.

Solution

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