94

I have a docker container based on Postgres's official docker image. I want to see the incoming queries when I look at the logs of the docker container using docker logs -f. This is my Dockerfile:

FROM postgres:11.1-alpine

COPY mock_data.sql /docker-entrypoint-initdb.d/mock_data.sql

ENV PGDATA=/data

and this is the part of my docker-compose.yml file related to this service:

version: '3'
services:
  mock_data:
    image: mock_data
    container_name: mock_data
    ports:
         - 5434:5432/tcp

What is the eaasiest way to include the incoming queries in docker logs?

svakili
  • 1,909
  • 4
  • 19
  • 24

5 Answers5

163

If using Docker Compose, you can add this line to your docker-compose.yaml file:

command: ["postgres", "-c", "log_statement=all"]

and all your queries will be written into the container log file.

Robin Daugherty
  • 7,115
  • 4
  • 45
  • 59
sprat
  • 1,754
  • 1
  • 10
  • 4
51

Setting log_destination to stderr did the trick for me without creating a new image:

version: "2.2"
services:
  db:
    image: postgres:12-alpine
    command: ["postgres", "-c", "log_statement=all", "-c", "log_destination=stderr"]

And then I was able to trace the statements using docker-compose logs -f db.
Should work with other versions too, but I only tested it with postgres:12-alpine.

Florian F
  • 4,044
  • 3
  • 30
  • 31
35

In case of running docker run directly from docker command, try follow command:

# single line to copy
docker run -d -e POSTGRES_USER=user -e POSTGRES_PASSWORD=pass -e POSTGRES_DB=postgres -p 5432:5432 --name db postgres:10 postgres -c log_statement=all

# split to read. 
docker run -d \
    -e POSTGRES_USER=user \
    -e POSTGRES_PASSWORD=pass \
    -e POSTGRES_DB=postgres \
    -p 5432:5432 \
    --name db \
    postgres:10 postgres \
    -c log_statement=all

The tailing part is overriding command as described here overriding docker file

Good luck!

baxx
  • 3,956
  • 6
  • 37
  • 75
longbkit
  • 1,218
  • 1
  • 13
  • 20
13

Refer to this, you should enable logging_collector, then you can see incoming queries in log_directory's log_filename.

And to enable it in docker logs, you had to make some trick to make it, one solution is as follows:

wrapper.sh:

#!/usr/bin/env bash
mkdir /logs
touch /logs/postgresql.log
chmod -R 777 /logs
tail -f /logs/* &
/docker-entrypoint.sh "$@"

Above will use tail to monitor /logs/postgresql.log which will later be used by postgresql's logging_collector, and show it docker logs.

Dockerfile:

FROM postgres:11.1-alpine
COPY wrapper.sh /
RUN chmod +x /wrapper.sh
ENTRYPOINT ["/wrapper.sh"]
CMD ["postgres", "-c", "logging_collector=on", "-c", "log_directory=/logs", "-c", "log_filename=postgresql.log", "-c", "log_statement=all"]

Above will use customize wrapper.sh, it will first monitor the postgre log, print it, and then contiune to execute the default docker-entrypoint.sh to start postgresql server.

After container start, show logs before incoming queries:

orange@orange:~/abc$ docker build -t abc:1 .
orange@orange:~/abc$ docker run -idt abc:1
orange@orange:~/abc$ docker ps
CONTAINER ID        IMAGE                  COMMAND                  CREATED             STATUS              PORTS               NAMES
c9112eb785e5        abc:1                  "/wrapper.sh postgre…"   2 seconds ago       Up 1 second         5432/tcp            loving_joliot
orange@orange:~/abc$ docker logs loving_joliot
The files belonging to this database system will be owned by user "postgres".
......
2019-07-13 03:38:14.030 UTC [46] LOG:  database system was shut down at 2019-07-13 03:38:13 UTC
2019-07-13 03:38:14.034 UTC [10] LOG:  database system is ready to accept connections

Simulate some incoming queries, and see logs again:

orange@orange:~/abc$ docker exec -it -u postgres loving_joliot psql -c "SELECT datname FROM pg_database;"
  datname
-----------
 postgres
 template1
 template0
(3 rows)
orange@orange:~/abc$ docker logs loving_joliot
The files belonging to this database system will be owned by user "postgres".
......
2019-07-13 03:38:14.030 UTC [46] LOG:  database system was shut down at 2019-07-13 03:38:13 UTC
2019-07-13 03:38:14.034 UTC [10] LOG:  database system is ready to accept connections
2019-07-13 03:41:22.859 UTC [62] LOG:  statement: SELECT datname FROM pg_database;

You can see above we simulate a sql execute SELECT datname FROM pg_database;, and in docker logs we could already see this sql.

atline
  • 28,355
  • 16
  • 77
  • 113
  • what is this line in `wrapper.sh` file doing? `/docker-entrypoint.sh "$@"` because it is giving error `/docker-entrypoint.sh: not found` – uberrebu Aug 12 '21 at 20:12
  • This the default entrypoint in base image `postgres:11.1-alpine`: `docker run --rm postgres:11.1-alpine ls -alh /docker-entrypoint.sh`: `lrwxrwxrwx 1 root root 34 Feb 5 2019 /docker-entrypoint.sh -> usr/local/bin/docker-entrypoint.sh`. But this answer is ok just when the default postgres image don't have the `log_statement` feature (Which I don't know as that time); but if as the accepted answer mentioned, it already has embed feature for this log record, then use accept answer is enough. – atline Aug 13 '21 at 02:14
  • i was able to get the real time logs without creating entrypoint script like you have..just needed to have `log_statement=all` and `pg_stat_statements.track = all` and that is all for postgres:13 – uberrebu Aug 13 '21 at 06:30
  • YES< just like the accepted answer, please move step to that one. – atline Aug 13 '21 at 06:33
11

According to the documentation of the official Postgres image (section Database Configuration) you can either

  • inject a custom configuration file (e.g. by means of adjusting the default configuration file) or
  • start the container setting the configuration parameters required.

In the latter case simply start the container with

$ docker run -d --name some-postgres postgres -c log_statement=all

It makes use of Overriding Dockerfile image defaults.

If you'd like to enable additional logging options check out the Error reporting and Logging section of the PostgreSQL docs.

damb
  • 151
  • 1
  • 5