Alter Postgres 9.4 configuration options using official postgres docker image

postgres docker

Revision history

I wanted to make my postgres instance log all queries it received.

Postgres 9.4 can be configured using SQL statements like below:

SELECT set_config('log_statement', 'all', true);
ALTER SYSTEM SET log_statement = 'all';

I will be running postgres in a Docker container, and the postgres:9.4 image lets me place executable .sh and .sql files in a special folder inside the container, /docker-entrypoint-initdb.d, that will be run when the container database initialises. These scripts will be executed before the server is listening for requests, the first time it is started.

Mount a configuration file to enable logging of all executed queries:

$ echo "ALTER SYSTEM SET log_statement = 'all';" > conf.sql
$ docker run --name psql-test -v $(pwd)/conf.sql:/docker-entrypoint-initdb.d/conf.sql postgres:9.4

Now, when I execute a query, it shows up in my logs like:

$ docker logs psql-test -f
LOG:  execute <unnamed>:
                        SELECT u.uid, u.username
                        FROM users AS u
                        WHERE u.username = $1
                        LIMIT 1
DETAIL:  parameters: $1 = 'sshow'

References

If you have any comments or feedback, please send me an e-mail. (stig at stigok dotcom)