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.

Git hook – checking branch name

Have you ever faced situation where you made wrong commit message and missed that and been embarrassed because some typos ?

Thankfully there are ways to avoid this. Called Git hooks.

In order to check commit message you can put script – content below into
.git/hooks/prepare-commit-msg

#!/bin/sh

RED='\033[0;31m'
NC='\033[0m' # No Color


get_initials () { array=( $(id -F) ); echo ${array[1]:0:1}${array[0]:0:1}; };

INITIALS=$(get_initials)
BRANCH="$(git rev-parse --abbrev-ref HEAD)"


if ! grep -E "$INITIALS - $BRANCH - " $1; then
    echo "Your commit message should contain initials - branch name - description. Like below"
    printf "${RED}KP - ${BRANCH} - added new fixtures, changes on view${NC}\n"
    exit 1
fi

So if we try to execute following command:

git commit -m "AD - 32 - test of git hook"

We will see

output when on wrong branch / wrong initials

Because my initials are KP and branch i should use is XX.
So when I execute proper command:

git commit -m "KP - XX - added new fixtures, changes on view"

I should see:

KP - XX - added new fixtures, changes on view
[XX 8f80226] KP - XX - added new fixtures, changes on view
 1 file changed, 0 insertions(+), 0 deletions(-)
 delete mode 100644 xx

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

Jinja2==2.10.1
MarkupSafe==1.1.1

Template

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 create_dockerfile.py --base-img centos -n centos_base -m "Kamil Pazik pazik.kamil@gmail.com" -p tcpdump nmap
execution of the script

The script created Dockerfile

FROM centos
LABEL maintainer="Kamil Pazik pazik.kamil@gmail.com"

RUN yum install -y  tcpdump nmap

Repository