PostgreSQL PostgreSQL

Powerful open source object-relational database system

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

TODO

TODO: Add PgBouncer container for connection pooling TODO: Add PostgreSQL exporter for Prometheus monitoring TODO: Consider enabling SSL/TLS for encrypted connections TODO: Set up streaming replication for high availability

References

Backup

The host-side backup.yaml target exports all non-template databases and cluster roles to ${DB_BACKUP_ROOT}/database-postgresql/. On this host, it currently discovers mattermost, postgres and productsearch. The daily job includes this target; restore roles and required databases with a compatible PostgreSQL version and paired application files (including Mattermost). See the backup guide for commands, retention and snapshot selection.

Recovery

  1. Recover globals.sql, database-<name>.dump archives and the manifest’s DB inventory. Globals contain password hashes and require private handling.
  2. Use a compatible PostgreSQL destination/client (matching the deployed major version). Restore required roles first, recreate the selected databases with original ownership, then apply their matching custom archives using pg_restore.
  3. Restore each application’s paired files and original credentials before allowing it to connect. Follow Mattermost recovery for its configuration, uploads and plugins, then check database access and app health.

Separate database dumps are not one cross-database transaction. productsearch has DB coverage only; its separate repository’s application recovery dependencies have not been inventoried by this backup setup.

name: postgresql
services:
  postgresql-init:
    image: busybox:1.38.0@sha256:fd7dc98638c8e305f4dc34e979f1c0fdfdcaeb0fbf8fcff77ae834b6da3d7e6e
    container_name: postgresql-init
    command: ["sh", "-c", "chown -R 999:999 /var/lib/postgresql"]
    volumes:
      - ${DOCKER_VOLUMES}/postgresql/data:/var/lib/postgresql

  postgresql:
    image: postgres:18.6-trixie@sha256:74935e72241653ca55e0414067e6d8763aceb8a810eb51b452253ec3dcfc4336
    container_name: postgresql
    restart: unless-stopped
    shm_size: 128mb
    environment:
      TZ: ${TIMEZONE}
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB:-postgres}
      POSTGRES_INITDB_ARGS: --data-checksums
    volumes:
      - ${DOCKER_VOLUMES}/postgresql/data:/var/lib/postgresql
      - ./config/initdb:/docker-entrypoint-initdb.d
    ports:
      - 5432:5432
    networks:
      - proxy
    depends_on:
      postgresql-init:
        condition: service_completed_successfully
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-postgres}"]
      interval: 10s
      timeout: 5s
      retries: 5
    labels:
      traefik.enable: false
      homepage.group: Storage
      homepage.name: PostgreSQL
      homepage.icon: postgresql.png
      homepage.description: "Powerful open source object-relational database system"

networks:
  proxy:
    external: true