Skip to content

PostgreSQL

Lifecycle: Current. Last verified: 2026-07-17.

Service record

Field Value
Purpose Shared relational data for current applications
Owner IT Operations; application data owners unassigned
Criticality / recovery priority High / P3
Host/runtime CT 101, rootless Podman as podsvc
Image docker.io/library/postgres:16-alpine
Unit postgres.service
Network kh3-backend; not published on the CT host
Data /opt/podman/volumes/postgres/data
Env /opt/podman/env/postgres.env, secret-bearing
Databases forgejo, vaultwarden verified by restore/validation design
Container owner PostgreSQL UID/GID 70:70, mapped with podman unshare

Dependencies

Forgejo, Vaultwarden, and Adminer depend on PostgreSQL. PostgreSQL depends on CT 101, rootless user systemd, storage, and the Quadlet network.

Operate

ssh pvessh 'pct exec 101 -- runuser -l podsvc -c \
  "podman exec postgres pg_isready -U postgres"'
ssh pvessh 'pct exec 101 -- runuser -l podsvc -c \
  "podman logs --tail=200 postgres"'
ssh pvessh 'pct exec 101 -- runuser -l podsvc -c \
  "XDG_RUNTIME_DIR=/run/user/2000 systemctl --user status postgres.service --no-pager"'

Expected: accepting connections, healthy container, active unit.

Upgrade

  1. Identify current server and data-major version.
  2. Review PostgreSQL image release and extension compatibility.
  3. Create and verify logical dumps plus a restricted volume backup.
  4. Test restore in an isolated target.
  5. For a major upgrade, use pg_upgrade or logical migration; do not point a new major image at an old data directory blindly.
  6. Validate dependent applications and retain the prior recovery point.

Backup and restore

Back up globals and each application database with pg_dumpall --globals-only and custom-format pg_dump -Fc. Validate with pg_restore --list. Restore roles, create databases with correct owners, restore dumps, and verify grants. Do not use a live filesystem copy as the only backup.

The repository's current Podman restore script imports the historical gitea.dump into database forgejo; treat FORCE_RESTORE=1 as destructive.

Security and monitoring

  • Keep PostgreSQL off host-published ports.
  • Use a distinct role/database per application.
  • Keep passwords only in restricted env files.
  • Monitor readiness, health, disk space, backup age, failed connections, and application checks.

Troubleshooting

Symptom Diagnosis Remedy
Not ready Logs, disk, unit, ownership Correct cause; do not delete WAL/data files
App auth failure Role/database/env target Restore matching credentials/grants without printing values
Permission denied on data podman unshare ownership Map expected container UID/GID; do not use broad modes
Restore fails pg_restore --list, version, owner/grant errors Restore into isolated compatible version and correct roles

Official references