Postgres (Datenbank Server)

Ich bin inzwischen ein kleiner Postgres Fans geworden und mag die Datenbank inzwischen viel lieber als MySQL.
Hier seht ihr mein Postgres Server Setup. Der Server dient als Datenhaltung für diverse Apps in meinem Homelab.
Zur einfachen Administration nutze ich noch einen pgadmin Server.

docker-compose.yml

Für das Monitoring meiner Datenbank-Schemen nutze ich einen Prometheus Exporter. Das Postgres Image habe ich leicht angepasst.
Wenn ihr keine speziellen Anpassungen an Postgres vornehmen möchtet, kann man einfach das fertige Image direkt nutzen (siehe auskommentierte Zeile).

services:
  postgres:
    container_name: postgres
    #image: postgres:16
    build: ./docker
    #network_mode: host
    ports:
      - 5432:5432
    command:
      - postgres

      # See https://www.youtube.com/watch?v=pvPkLTobK0c

      - -c
      - shared_buffers=4GB

      - -c
      - max_connections=200

      - -c
      - work_mem=10MB

      - -c
      - maintenance_work_mem=500MB

      - -c
      - wal_buffers=32MB

      - -c
      - effective_cache_size=5GB

      - -c
      - checkpoint_timeout=600

      # SSD
      - -c
      - random_page_cost=1.1

      - -c
      - shared_preload_libraries=pg_stat_statements,vector

      - -c
      - ssl=on

      - -c
      - ssl_cert_file=/certs/postgres.muench.lan.crt

      - -c
      - ssl_key_file=/certs/postgres.muench.lan.key
    restart: unless-stopped
    env_file:
      - postgres.env
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./certs:/certs
  
  postgres-exporter:
    container_name: postgres-exporter
    image: prometheuscommunity/postgres-exporter
    restart: unless-stopped
    volumes:
      - ./exporter/queries.yaml:/queries.yaml
    env_file:
      - exporter.env
    ports:
      - "9187:9187"
    networks:
      - default
    depends_on:
      - postgres
    
volumes:
  postgres-data:

postgres.env

Super-User:

POSTGRES_USER=postgres
POSTGRES_PASSWORD=xxxxxxxxxxxxxxxx

docker/Dockerfile

Ich verwende die Extensions pgvector (Vektor-Datenbank) und PostGIS (Geo-Daten).
Daher erstelle ich mein eigenes Docker-Image in dem beide Extensions installiert werden.

ARG POSTGIS_MAJOR=3
ARG PG_MAJOR=16
ARG PG_VECTOR_VERSION=0.8.0

FROM postgres:$PG_MAJOR

# Set ARGs for build
ARG POSTGIS_MAJOR
ARG PG_MAJOR
ARG PG_VECTOR_VERSION

# Install runtime and build dependencies
RUN apt-get update && apt-get install -y \
    postgresql-16-postgis-3 \
    postgresql-16-pgvector && \
    rm -rf /var/lib/apt/lists/*

postgres.conf

Meine Einstellungen habe ich für die Nutzung in einem LXC Container angepasst. Der LXC Container läuft in einem Proxmox Cluster. Ich habe dem Container 10 GB RAM zugeteilt. Die Einstellungen hier sind genau auf meine Bedürfnisse ausgelegt und müssen entsprechend für euer Setup angepasst werden.

# Memory settings
shared_buffers = 2GB              # 20% of RAM
work_mem = 16MB                   # Memory for sorting operations
maintenance_work_mem = 512MB      # Memory for maintenance tasks like vacuum

# CPU settings
max_parallel_workers_per_gather = 2 # Number of parallel workers per query
max_parallel_workers = 4            # Max parallel workers for entire instance
max_worker_processes = 8            # Max worker processes (should match cores + parallel workers)

# WAL settings
wal_buffers = 16MB                 # Buffer size for WAL (can be auto-tuned)
checkpoint_completion_target = 0.7 # Checkpoint frequency
effective_io_concurrency = 200     # I/O concurrency for SSDs

# Disk settings
random_page_cost = 1.1             # Set lower for SSDs, higher for spinning disks
effective_cache_size = 1GB         # Approx 70% of RAM, including OS cache

# Autovacuum settings
autovacuum_max_workers = 3         # Number of autovacuum workers
autovacuum_naptime = 1min          # How often autovacuum runs

# Connection settings
max_connections = 100              # Adjust based on expected traffic; lower for LXC