How to log PostgreSQL queries with Testcontainers

One of the benefits of Testcontainers is the ability to programmatically configure containers.

This blog posts shows how to configure PostgreSQL container to log SQL queries, but you can use similar approach to other containers and use cases.

How to log queries from PostgreSQL? ​

By default, Postgres does not log any queries. It can be configured by switching the log_statement configuration property to all. In similar way, if you want to log all incoming connections - switch log_connections to on. To log disconnections log_disconnections to on. There are quite a few logging related configuration options, I recommend looking into the official PostgreSQL docs.

To put it all together, to run Postgres with Docker and log all connections and statements, run following command:

$ docker run -p 5432:5432 -e POSTGRES_PASSWORD=password postgres:13.3 -c log_connections=on -c log_disconnections=on -c log_statement=all

Let's now see how it can be translated to Testcontainers.

Configuring Docker command with Testcontainers ​

Testcontainers comes higher level module for Postgres which saves us from any low level coding - if we want to stick to defaults:

try (var postgres = new PostgreSQLContainer("postgres:13.3")) {
    postgres.start();
}

Under the hood, Testcontainers run Postgres with command postgres -c fsync=off to speed up startup time (see more).

To enable logging, we need to modify Docker command:

try (var postgres = new PostgreSQLContainer("postgres:13.3")) {
    postgres.setCommand("postgres", "-c", "fsync=off", "-c", "log_statement=all");
    postgres.start();
}

Capturing container logs ​

Now we can hook into container and capture its logs.

postgres.followOutput(<consumer>);

If you use Slf4j, you can just pipe container logs into an Slf4j logger:

Logger LOGGER = LoggerFactory.getLogger(MyComponent.class);
//...

try (var postgres = new PostgreSQLContainer("postgres:13.3")) {
    postgres.setCommand("postgres", "-c", "fsync=off", "-c", "log_statement=all");
    postgres.start();
    postgres.followOutput(new Slf4jLogConsumer(LOGGER));
}

Note that followOutput must be called after container is started. Which also means that you will not get this way access to container startup logs that happen before postgres.start() method finished. To do it call postgres.getLogs() before followOutput(..).

Logger LOGGER = LoggerFactory.getLogger(MyComponent.class);
//...

try (var postgres = new PostgreSQLContainer("postgres:13.3")) {
    postgres.setCommand("postgres", "-c", "fsync=off", "-c", "log_statement=all");
    postgres.start();
    LOGGER.debug(postgres.getLogs()); // prints startup logs
    postgres.followOutput(new Slf4jLogConsumer(LOGGER));
}

Filtering logs ​

There is a chance that you want only selection of logs from Postgres to appear in your logs and you need some kind of filtering. Container#followOutput take simple Java Consumer as a parameter making it easy to plug in filtering:

public class FilteringConsumer implements Consumer<OutputFrame> {
    private final Consumer<OutputFrame> delegate;
    private final Predicate<OutputFrame> predicate;

    public FilteringConsumer(Predicate<OutputFrame> predicate, Consumer<OutputFrame> delegate) {
        this.delegate = delegate;
        this.predicate = predicate;
    }

    @Override 
    public void accept(OutputFrame outputFrame) {
        if (predicate.test(outputFrame)) {
            delegate.accept(outputFrame);
        }
    }
}

And then instead of using Sfl4jLogConsumer directly, we set it as a delegate of FilteringConsumer:

// likely you will use more sophisticated predicate than that
postgres.followOutput(new FilteringConsumer(frame -> frame.getUtf8String().contains("select"), new Slf4jLogConsumer(LOGGER)));

That's it!