Log all queries in the official Postgres docker image

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?

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.

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.

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

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!

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.

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.