PostgreSQL Docker Compose: Persistence, Backups, and Safe Major Upgrades

services:
  db:
    image: postgres:18
    restart: unless-stopped
    environment:
      POSTGRES_DB: app
      POSTGRES_USER: app
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password
    volumes:
      - pgdata:/var/lib/postgresql/data
    networks:
      - backend
    secrets:
      - postgres_password
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app -d app"]
      interval: 10s
      timeout: 5s
      retries: 5

  app:
    build: .
    depends_on:
      db:
        condition: service_healthy
    networks:
      - backend

secrets:
  postgres_password:
    file: ./secrets/postgres_password

volumes:
  pgdata:

networks:
  backend:

pg_dump versus physical/continuous recovery boundaries

Use logical dumps when portability and loading into a newer major or architecture matter; use file-system backups or continuous archiving when the recovery design requires physical recovery or a defined recovery point.

A pg_dump-style logical dump represents database contents in a form that can be reloaded across newer PostgreSQL versions and architectures, making it a natural candidate for a clean-major upgrade when application and extension compatibility is confirmed.

File-system copies are version-specific. They may suit a physical recovery design, but they are not portable directories that can move freely between major versions. Choose the method against the failure you must recover from and document the matching procedure. (PostgreSQL backup and restore, PostgreSQL dump and reload)

Isolated restore and validation of rows, extensions, owners and writes

Restore into an isolated PostgreSQL instance, then validate representative rows, required extensions, object owners, and an actual write before trusting the result.

Use a separate Compose project or volume; never point validation at the production data directory. Restore with the method that created the backup, then inspect both structure and application-critical data:

1. Confirm the expected databases and schemas exist.
2. Check representative rows and important relationships.
3. Verify required extensions are installed and usable.
4. Confirm owners and roles match the intended access model.
5. Perform a controlled write and read it back.
6. Record errors, duration, and manual steps.

The backup tool does not guarantee application correctness. The restore procedure proves whether the result is useful for this deployment. (PostgreSQL dump and reload)

Safe major upgrades: never attach an old directory to a new major

Never point a new PostgreSQL major at an old major’s data directory; treat the directory as version-specific. Preserve the original instance, create a clean target, and move data through a documented compatible path, such as a logical dump and reload when it fits the recovery design.

Use this workflow:

1. Pin and preserve the current source image and volume.
2. Create a separate target service with the new major.
3. Prepare required roles, databases, and extensions.
4. Load a logical dump or follow the selected physical method.
5. Validate rows, ownership, extensions, and writes.
6. Switch application connectivity only after validation.

Rollback means directing the application back to the preserved source instance, not downgrading a new-major directory in place. Coordinate writes during cutover; two independent instances accepting changes create a reconciliation problem that Compose cannot solve. (PostgreSQL dump and reload, PostgreSQL backup methods)

Operations, monitoring and rollback checklist

Operate the database as a recovery system, not merely a running container. Observe health, backup freshness, storage headroom, restore evidence, and application errors. Keep rollback bounded by the preserved old instance and define recovery objectives before changing the major version.

• PostgreSQL major version is explicit and change-controlled.
• The active data directory uses a named volume.
• Backup output is outside the active volume.
• Secret values use a controlled secret source.
• The database is private unless external access is intentional.
• Health status and application connection errors are observable.
• Backup freshness and storage capacity have clear thresholds.
• A restore validates rows, extensions, owners, and writes.
• The old instance remains available during cutover.
• Recovery objectives and rollback steps are documented.

For related OpenAlt guidance, browse the database and spreadsheet category. Use the backup storage calculator for retention planning, the RTO/RPO impact calculator for recovery tradeoffs, and the Grafana Docker Compose guide for operational visibility.

FAQ

Should the database publish port 5432?

Usually no for an internal application. Keep PostgreSQL on the private Compose network and let the application use the database service name. Publish a port only when an approved external client requires it, and treat exposure as an intentional access decision. (Docker PostgreSQL networking and connectivity)

Does a named volume make PostgreSQL safe?

No. A named volume preserves the active data location across routine container replacement, but it remains one working copy. Keep separate backup output, define recovery objectives, and validate an isolated restore.

Can a logical dump support a major upgrade?

Yes, when the target version and application dependencies are compatible with the reload plan. Logical dumps can be reloaded across newer versions and architectures, while file copies remain version-specific. Validate extensions, owners, representative rows, and writes on the clean target. (PostgreSQL dump and reload)

What is the safest rollback for a major upgrade?

Preserve the old instance and data separately, validate the new target before switching application traffic, and return to the old instance if necessary. Do not make a new-major process consume an old-major directory; use a documented backup or reload path instead.