CSSTEP 01Keep the database on a private network
In Docker Compose, the app reaches PostgreSQL by service name db on port 5432. No host port publication is required. The following service fragment is added to a Compose project; pg_data and pg_password must also be declared at the top level.
services:
db:
image: postgres:18-alpine
restart: unless-stopped
environment:
POSTGRES_DB: app
POSTGRES_USER: app_owner
POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
secrets:
- pg_password
volumes:
- pg_data:/var/lib/postgresql
healthcheck:
test: ["CMD-SHELL", "pg_isready -U app_owner -d app"]
interval: 10s
timeout: 5s
retries: 5
volumes:
pg_data:
secrets:
pg_password:
file: ./secrets/pg_password.txtSTEP 02Protect credentials and use an application role
Create the secret file outside Git with restrictive permissions and a strong generated password. app_owner is a bootstrap database superuser in this image; create a separate restricted runtime role for the app. Provide the application connection secret securely, with host db. A healthcheck does not create your schema or replace migration planning.
STEP 03A volume is persistence, not a backup
The PostgreSQL 18 image uses a version-specific data layout under /var/lib/postgresql. Do not copy older major-version examples blindly. Never upgrade the image major tag without a planned database upgrade. Deleting the volume deletes the database.
STEP 04Create and inspect a logical backup
Run from the Compose directory. This creates a custom-format dump under your current account and restricts its file mode. Copy backups to a separate protected location and define retention. Dumps can contain sensitive data.
umask 077
mkdir -p backups
docker compose exec -T db pg_dump -U app_owner -d app -Fc > backups/app.dump
docker compose exec -T db pg_restore --list < backups/app.dumpSTEP 05Restore into an isolated test database
A listing is not a restore test. Restore the dump into a separate disposable database with compatible PostgreSQL and extensions, then test application queries. Record duration and compare it with the recovery target. Do not run a destructive restore against the live database.
COPY → YOUR AI
Take the next step to your AI.
A safe starting prompt for this guide. No secrets. Works with ChatGPT, Claude and other assistants.
Sources and technical documentation
PostgreSQL backup documentation ↗Official PostgreSQL container ↗This recipe is a pattern for the stated prerequisites. Verify project compatibility and your actual server configuration before applying it to a live service.