stevensting
d74f05c0d1
Reviewed-on: #14 Co-authored-by: stevensting <stefan@klasse-methode.it> Co-committed-by: stevensting <stefan@klasse-methode.it>
93 lines
3.9 KiB
YAML
93 lines
3.9 KiB
YAML
version: '3.8'
|
|
|
|
x-postgres-env: &postgres-env
|
|
POSTGRES_DB: bonfire_db
|
|
POSTGRES_USER: postgres
|
|
POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password
|
|
|
|
services:
|
|
app:
|
|
environment:
|
|
<<: *postgres-env
|
|
POSTGRES_HOST: ${STACK_NAME}_db
|
|
secrets:
|
|
- postgres_password
|
|
|
|
db:
|
|
image: ${DB_DOCKER_IMAGE:-ghcr.io/baosystems/postgis}:${DB_DOCKER_VERSION:-17-3.5}
|
|
# give Postgres time to shut down cleanly: the Swarm default (10s) force-kills it mid-shutdown on every redeploy, which wipes the cumulative stats (pg_stat_*) that autovacuum's triggers depend on — a root cause of autovacuum never running on big tables
|
|
stop_grace_period: 2m # NOTE: abra validates the schema before env interpolation, so this duration field can't be env-parameterized
|
|
# static tuning defaults for deployments NOT using the postgres tuner overlay (NOTE: `compose.postgres.tune.yml` computes these from your system resources instead of hardcoding them, so please use it!)
|
|
# Notes:
|
|
# memory defaults are deliberately SAFE-SMALL (fit a 1 or 2GB VPS with the app co-hosted): shared_buffers is allocated at boot and maintenance_work_mem is per vacuum worker, so oversizing can prevent startup or OOM small machines — the tuner overlay right-sizes them instead (~25% / ~6% of the DB's RAM budget);
|
|
# max_connections is deliberately low because Bonfire's Ecto pool is the connection pooler (~25-50 conns; oversizing shrinks usable work_mem);
|
|
# jit off = measured per-query compile tax on Bonfire's many-join queries with no benefit;
|
|
# the autovacuum settings suit Bonfire's soft-delete/append workload where default thresholds never trigger on big tables.
|
|
command: >
|
|
postgres
|
|
-c max_connections=${PG_MAX_CONNECTIONS:-100}
|
|
-c shared_buffers=${PG_SHARED_BUFFERS_MB:-256}MB
|
|
-c effective_cache_size=${PG_EFFECTIVE_CACHE_SIZE_MB:-768}MB
|
|
-c work_mem=${PG_WORK_MEM_MB:-16}MB
|
|
-c maintenance_work_mem=${PG_MAINTENANCE_WORK_MEM_MB:-128}MB
|
|
-c random_page_cost=${PG_RANDOM_PAGE_COST:-1.1}
|
|
-c effective_io_concurrency=${PG_EFFECTIVE_IO_CONCURRENCY:-200}
|
|
-c jit=${PG_JIT:-off}
|
|
-c wal_compression=${PG_WAL_COMPRESSION:-lz4}
|
|
-c default_toast_compression=${PG_TOAST_COMPRESSION:-lz4}
|
|
-c shared_preload_libraries=${PG_PRELOAD_LIBS:-pg_stat_statements}
|
|
-c pg_stat_statements.track=all
|
|
-c track_io_timing=on
|
|
-c track_activity_query_size=16384
|
|
-c autovacuum_vacuum_scale_factor=0.05
|
|
-c autovacuum_vacuum_insert_scale_factor=0.05
|
|
-c autovacuum_vacuum_cost_limit=1000
|
|
-c log_autovacuum_min_duration=0
|
|
-c log_min_duration_statement=${PG_LOG_MIN_DURATION_STATEMENT:-2000}
|
|
-c log_lock_waits=on
|
|
-c log_temp_files=0
|
|
# -c statement_timeout=1800000
|
|
#entrypoint: ['tail', '-f', '/dev/null'] # uncomment when the Postgres DB is corrupted and won't start
|
|
volumes:
|
|
- db-data:/var/lib/postgresql/data
|
|
- type: tmpfs
|
|
target: /dev/shm
|
|
tmpfs:
|
|
size: 1000000000
|
|
# about 1 GB in bytes
|
|
tmpfs:
|
|
- /tmp:size=${DB_MEMORY_LIMIT:-1024}M^
|
|
networks:
|
|
- internal
|
|
environment:
|
|
<<: *postgres-env
|
|
secrets:
|
|
- postgres_password
|
|
healthcheck:
|
|
test: ["CMD-SHELL", "pg_isready -h localhost -U $$POSTGRES_USER"]
|
|
interval: 10s
|
|
timeout: 5s
|
|
retries: 5
|
|
deploy:
|
|
labels:
|
|
backupbot.backup: ${ENABLE_BACKUPS:-true}
|
|
backupbot.backup.pre-hook: "/pg_backup.sh backup"
|
|
backupbot.backup.volumes.db-data.path: "backup.sql"
|
|
backupbot.restore.post-hook: '/pg_backup.sh restore'
|
|
configs:
|
|
- source: pg_backup
|
|
target: /pg_backup.sh
|
|
mode: 0555
|
|
|
|
volumes:
|
|
db-data:
|
|
|
|
configs:
|
|
pg_backup:
|
|
name: ${STACK_NAME}_pg_backup_${PG_BACKUP_VERSION}
|
|
file: pg_backup.sh
|
|
|
|
secrets:
|
|
postgres_password:
|
|
external: true
|
|
name: ${STACK_NAME}_postgres_password_${SECRET_POSTGRES_PASSWORD_VERSION:-v1} |