Install PostgreSQL with Docker Compose

Half the apps on this page want a Postgres database. This is the container most of them expect, with tuning that matters on a small host and a backup method that doesn't just move files around and hope.

What it is

Postgres is the default answer for relational data in self-hosting. JSONB, full-text search, solid extension ecosystem, and a container image that handles initialisation, upgrades and health checks cleanly enough that most people never read the manual.

Before you start

• Version-pin. postgres:17-alpine, not latest. A major-version bump in a latest tag will refuse to start against an old data directory, and that surprises people badly.
• Don't expose 5432 to the internet. If another container needs it, put them on a shared Docker network instead and drop the ports: block entirely.
• Think about the disk. Postgres wants sustained write IOPS and will throttle and checkpoint aggressively on a slow or spinning disk. shm_size matters for parallel queries.

1 — Create the directory

mkdir -p ~/services/postgres/{data,backups}
cd ~/services/postgres
openssl rand -base64 32

2 — Write the compose file

services:
  postgres:
    image: postgres:17-alpine
    container_name: postgres
    restart: unless-stopped
    shm_size: 256mb
    ports:
      - "127.0.0.1:5432:5432"
    environment:
      POSTGRES_USER: CHANGE_ME_admin
      POSTGRES_PASSWORD: CHANGE_ME
      POSTGRES_DB: appdb
      # UTF-8 everywhere; the initdb default is not what you want
      POSTGRES_INITDB_ARGS: "--encoding=UTF8 --locale=C"
      TZ: UTC
      PGTZ: UTC
    volumes:
      - ./data:/var/lib/postgresql/data
    command: >-
      postgres
      -c shared_buffers=512MB
      -c effective_cache_size=1536MB
      -c work_mem=16MB
      -c maintenance_work_mem=128MB
      -c max_wal_size=2GB
      -c min_wal_size=128MB
      -c random_page_cost=1.1
      -c effective_io_concurrency=200
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U CHANGE_ME_admin -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5
    stop_grace_period: 60s

3 — Start it

docker compose up -d
docker exec -it postgres psql -U CHANGE_ME_admin -d appdb -c "select version();"

4 — First-run setup

1. Create one role and one database per application. Shared roles mean one app's compromised connection can read another's tables.
2. Turn off the public schema for anything internet-facing: REVOKE CREATE ON SCHEMA public FROM PUBLIC;
3. For each app, restrict its role to its own database: REVOKE ALL ON DATABASE otherdb FROM myapp;
4. Set log_min_duration_statement = 500ms while you tune, then drop it once queries are healthy. Slow-query logging is how you find the index you're missing.

5 — Back it up for real

# logical, human-readable, restores anywhere
docker exec -u postgres postgres pg_dump -Fc -U CHANGE_ME_admin appdb \
  > ~/services/postgres/backups/appdb-$(date +%F).dump

# all databases at once
docker exec -u postgres postgres pg_dumpall -U CHANGE_ME_admin -f - \
  > ~/services/postgres/backups/all-$(date +%F).sql

# restore
docker exec -i postgres pg_restore -U CHANGE_ME_admin -d appdb --clean --if-exists \
  < appdb-2026-09-28.dump