esgn/pgtuned repository overview

⁠PGTuned

See source code on GitHub : https://github.com/esgn/pgtuned⁠

PGTuned is a first attempt at building Docker PostgreSQL/PostGIS images that include basic performance tuning based on available resources and contemplated use-case.
This project includes a bash script equivalent of PGTune⁠.

⁠Using PGTuned images directly from Docker hub

This project builds a small number of versions of the PGTuned image and deploy them to Docker Hub⁠ using the following tags :

• latest corresponds to PostgreSQL 14
• postgis-latest corresponds to PostgreSQL 14 and PostGIS 3
• POSTGRES_VERSION corresponds to a specific PostgreSQL image version (e.g. 12) without PostGIS
• POSTGRES_VERSION-POSTGIS_VERSION corresponds to a specific PostgreSQL image version including a specific PostGIS version (e.g. 12-3)

To use these images simply docker pull esgn/pgtuned:tag and run.

⁠Running PGTuned image

POSTGRES_PASSWORD environment variable is compulsory to run the official PostgreSQL image and therefore the PGTuned image.
All other environment variables of the official PostgreSQL Docker image may also be used (POSTGRES_USER, POSTGRES_DB, ...).

In addition, the following environment variables may be provided to tune PostgreSQL with pgtune.sh :

• DB_TYPE : If not provided web will be used as default DB_TYPE
• TOTAL_MEM : If not provided pgtune.sh will try to determine the total memory automatically
• CPU_COUNT : If not provided pgtune.sh will try to determine the cpu count automatically
• MAX_CONN : If not provided 200 wil be used as default maximum client connections number
• STGE_TYPE : If not provided pgtune.sh will try to determine the storage type automatically
• PG_VERSION : Should not be necessary as Docker image PG_MAJOR environment variable will be used by default

Default command line for running the PGTuned image with default pgtune.sh options :

docker run -d -e POSTGRES_PASSWORD=secret --name pgtuned esgn/pgtuned:latest

Command line example for running the PGTuned image with 2GB of RAM, mixed database type, 4 cpu cores and ssd storage :

docker run -d -e POSTGRES_PASSWORD=secret -e TOTAL_MEM=2GB -e DB_TYPE=mixed -e CPU_COUNT=4 -e STGE_TYPE=ssd --name pgtuned esgn/pgtuned:latest

You can check PostgreSQL parameter (here work_mem) by using such a command once the pgtuned container is up and running :

user@machine:$ docker exec -ti pgtuned psql -U postgres -W -c "show work_mem;"
Password: 
 work_mem 
----------
 1310kB
(1 row)

You can also check the content of the postgresql.conf file generated by pgtune.sh by running :

user@machine:$ docker exec -ti pgtuned cat /var/lib/postgresql/data/postgresql.conf
# DB Version: 14
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 3927732 kB
# CPUs num: 2
# Connections num: 200
# Data Storage: hdd

max_connections = 200
shared_buffers = 981933kB
effective_cache_size = 2945799kB
maintenance_work_mem = 245483kB
checkpoint_completion_target = 0.9
... more lines displayed after this ...