Enabling SQL Query Logging in a PostgreSQL Docker Container

Logging all SQL queries in PostgreSQL can be incredibly helpful when you're trying to debug application behavior, understand how an ORM or BaaS (like Strapi) generates SQL under the hood, or reverse engineer third-party tools. This guide walks you through turning on query logging in a PostgreSQL Docker container.

🐘 Why Enable Logging?

• ✅ Debug and trace execution flow
• ✅ Understand SQL performance bottlenecks
• ✅ Reverse engineer queries from tools like Strapi
• ✅ Learn how complex apps construct queries

🐳 Step-by-Step: Enable Logging in Dockerized PostgreSQL

1. Use a Docker-Compose Setup

version: "3.8"
services:
  db:
    image: postgres
    restart: always
    shm_size: 2048mb
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432
    command: ["postgres", "-c", "log_statement=all"]
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./postgresql.conf:/var/lib/postgresql/data/postgresql.conf
      - ./pg_log:/var/lib/postgresql/data/pg_log/

2. Configure postgresql.conf

Make sure the postgresql.conf file includes the following lines:

log_statement = 'all'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
logging_collector = on
log_min_error_statement = error

This configuration:

• Logs all SQL queries
• Saves logs to the pg_log directory inside the container
• Enables the PostgreSQL logging collector

📁 Logs will be available in your host's ./pg_log directory.

3. Restart the Container

Run either of the following:

docker-compose up -d --force-recreate
# or
docker-compose restart db

4. Find the Logs

Once the container restarts, query logs will appear in:

./pg_log/postgresql-YYYY-MM-DD_HHMMSS.log

📝 Pro Tips

• You can temporarily enable logging using SQL:

  SET log_statement = 'all';

• To include execution time, also add:

  log_duration = on

• To customize log line formatting, try:

  log_line_prefix = '%m [%p] %u@%d '

🧪 Example Repository

A working example of this setup is available here: https://github.com/MakersAll8/docker-postgres/tree/main

✅ Conclusion

Enabling SQL query logging in PostgreSQL is a simple but powerful technique. Whether you're debugging, auditing, or reverse-engineering, seeing the actual queries being executed provides deep insight into how your application communicates with the database.

Want to take it further? Try setting log_min_duration_statement to only log slow queries, or integrate tools like pgBadger for analyzing logs!