pgFouine is a log analyzer for PostgreSQL, it uses the postgreSQL log to create charts and statistics about the usage of a database, provides a useful visualization of the most expensive, common and heavy queries.
To configure pgfouine you need two things, change the log configuration of postgresql and has a working syslog log. Unfortunately the default image for postgreSQL has a data volume pointing to the
/var/lib/postgresql directory, and the default configuration file is in
/var/lib/postgresql/data/postgresql.conf. Everything after a
VOLUME command is override at runtime, so you can’t not change the default file in the build process, this means that a simple Dockerfile is not enough.
In the source repo, the issues #105, and the merge #127 allows a user to expand the configuration through the
.sample file, this can be a good option, in this entry we use another way, changing the only postgrseql configuration file, and configuring a full rsyslog.
First we need a working postgresql configuration file, for example we can get one from a container
Now, we need to configure postgres to log all to syslog:
- Set the log method. Change
#log_destination = 'stderr'to:
log_destination = 'syslog'
- Uncomment the following lines:
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
- Set the parameter
0(to log all), this can be further tuned to log only the expensive queries.
- Set the
log_line_prefix = '%t [%p]: [%l-1] ', this is recommended by the pgFouine team.
This configuration is all that pgFouine needs from the postgreSQL log system, now, we need to use this configuration file in the postgreSQL instance, To allow us to change the configuration file, we need to get the docker-entrypoint.sh from the repo, and change it to copy the configuration file in every startup:
- Add to your Dockerfile
ADD postgresql.conf /postgresql.conf
- When the scripts ask if the parameter is
postgres(see this gist for more details):
With this, we have postgreSQL in a docker container configured to use rsyslog as the log facility. Now we need to install
rsyslog, add the installation steps to the Dockerfile:
With the current configuration, postgreSQL floods the
/var/log/syslog, to prevent this (this step is optional), we need to configure syslog to ignore the
local0 (the configured facility) when logging to
syslog. To change the configuration file, we can go with
sed and replace, or grab a working file for our container (if you execute docker at this moment, you will get a container with syslog, with a good default config file to grab), and modify the lines (the config file is in
And replace with:
Then, we add the configuration file to the proper location in our Dockerfile:
Now, you can run it!, to check if everything works fine, you can execute:
P.D.: I has a problem with the syslog service, it will not start at the startup, so I change the
docker-entrypoint.sh to start the service, see the final docker-entrypoint here.
This is my final Dockerfile:
The final files can be found in this gists.