CODEWITHSUNDEEP
postgresqldocker
svakili
svakili

Reputation: 2199

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?

Upvotes: 117

Views: 102473

Answers (5)

Florian F
Florian F

Reputation: 4363

Setting log_destination back 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.

Edit: At the time of writing log_destination was not set to stderr in this image. This may have changed later on but this method still works for me even in newer images.

Upvotes: 64

longbkit
longbkit

Reputation: 1308

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!

Upvotes: 44

sprat
sprat

Reputation: 2124

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.

Upvotes: 199

damb
damb

Reputation: 151

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.

Upvotes: 11

atline
atline

Reputation: 31664

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.

Upvotes: 13

Related Questions