---
name: database-in-docker
description: "Run databases and object storage (MinIO) in Docker containers — setup, user permissions, environment variables, backup/restore, full-stack deployment patterns, and troubleshooting."
tags: [docker, database, kingbase, postgresql, mysql, minio, object-storage, full-stack, prisma, devops, containers]
triggers:
  - database docker container
  - run database in docker
  - restore database docker
  - kingbase docker
  - postgresql docker
  - mysql docker container
  - database backup restore docker
  - minio docker
  - object storage docker
  - full stack deploy docker
  - prisma docker
---

# Database in Docker

Run relational databases as Docker containers. Covers user/permission issues, environment variable discovery, backup, and restore.

## General Workflow

### 1. Pull and inspect the image

```bash
docker pull IMAGE:TAG
docker inspect IMAGE:TAG --format '{{json .Config.Entrypoint}}'
docker inspect IMAGE:TAG --format '{{json .Config.Env}}'
```

Always inspect the entrypoint script and env vars — different images use different variable names for user/password/database.

### 2. Start the container

Common flags:
```bash
docker run -d \
  --name db-container \
  --user <db_user> \          # run as the database's expected user, NOT root
  -p 54321:54321 \
  -e DB_USER=myuser \
  -e DB_PASSWORD=mypass \
  -v /host/data:/container/data \
  IMAGE:TAG
```

### 3. Verify startup

```bash
docker ps | grep db-container
docker logs db-container 2>&1 | tail -20
```

### 4. Create databases and users

```bash
docker exec db-container bash -c 'source /etc/profile && TOOL -U admin_user -d default_db -c "CREATE DATABASE mydb;"'
```

## Critical: User Permission Issues

### Root cannot run database engines

Most databases (PostgreSQL, KingbaseES, MySQL) **refuse to run as root** for security:
```
initdb: error: cannot be run as root
```

**Do NOT use `--user root`**. Use the image's intended user (often `postgres`, `kingbase`, `mysql`).

**`--privileged` flag:** Useful when running as the image's intended user (e.g., `kingbase`) and the entrypoint script uses `sudo` heavily. Helps avoid PAM permission errors without needing root:
```bash
docker run -d --name kingbasev8 --user kingbase --privileged -p 54321:54321 ...
```
Do NOT combine `--privileged` with `--user root` — same initdb issue.

### sudo PAM errors in container logs

If the entrypoint script uses `sudo` and you see:
```
sudo: pam_open_session: Permission denied
sudo: policy plugin failed session initialization
```

This is usually **non-critical** — it affects cron/logrotate setup, not the database itself. Check if the database actually started:
```
waiting for server to start.... done
server started   ← this means it's fine
```

### `--privileged` flag

Only use if the container truly needs elevated capabilities. For databases, it's rarely needed and can cause the root-can't-run-db issue if combined with `--user root`.

## Environment Variable Discovery

Different images use different env var names. **Always inspect the entrypoint script**:

```bash
docker exec db-container cat /home/USER/docker-entrypoint.sh
```

Common patterns:
| Image | User var | Password var | Database var |
|-------|----------|-------------|-------------|
| PostgreSQL | `POSTGRES_USER` | `POSTGRES_PASSWORD` | `POSTGRES_DB` |
| KingbaseES | `DB_USER` | `DB_PASSWORD` or `PASSWORD` | (created manually) |
| MySQL | `MYSQL_USER` | `MYSQL_PASSWORD` | `MYSQL_DATABASE` |

If the image doesn't document env vars, grep the entrypoint:
```bash
docker exec db-container grep -E 'DB_USER|PASSWORD|DB_NAME' /path/to/entrypoint.sh
```

## Backup & Restore

### Identify the dump format

```bash
file backup.dmp
head -c 500 backup.dmp | cat -v
```

- **SQL text** → restore with `psql` / `ksql` / `mysql`
- **Custom binary** → restore with platform tool (`pg_restore`, `sys_restore`, `mysqlrestore`)

### Copy file into container

```bash
docker cp backup.dmp db-container:/tmp/backup.dmp
```

### Restore with --no-owner

When restoring to a container with a different user, always use `--no-owner` to avoid "role does not exist" errors:

```bash
# PostgreSQL
docker exec db-container pg_restore -U user -d dbname --no-owner /tmp/backup.dmp

# KingbaseES
docker exec db-container bash -c 'source /etc/profile && /path/to/sys_restore -U user -d dbname --no-owner /tmp/backup.dmp'
```

### Handle "already exists" errors

If restore fails with "relation/function already exists":
1. Terminate active connections
2. Drop and recreate the database
3. Restore again

```bash
# Terminate connections
docker exec db-container psql -U user -d default_db -c \
  "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'target_db' AND pid <> pg_backend_pid();"

# Drop and recreate
docker exec db-container psql -U user -d default_db -c "DROP DATABASE target_db;"
docker exec db-container psql -U user -d default_db -c "CREATE DATABASE target_db;"

# Restore
docker exec db-container pg_restore -U user -d target_db --no-owner /tmp/backup.dmp
```

### Verify restore

```bash
# List schemas
docker exec db-container psql -U user -d dbname -c "\dn"

# Count tables per schema
docker exec db-container psql -U user -d dbname -c \
  "SELECT schemaname, count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema') GROUP BY schemaname ORDER BY schemaname;"
```

## Connecting to the Database

From host:
```bash
psql -h 127.0.0.1 -p 54321 -U username -d dbname
```

From another container:
```bash
docker run --rm -it --network host IMAGE psql -h 127.0.0.1 -p 54321 -U user -d db
```

## Pitfalls

### P1: "role does not exist" during restore
The dump references a role (e.g., `system`) that doesn't exist in the target. Use `--no-owner` or create the role first.

### P2: "database is being accessed by other users"
Can't drop a database with active connections. Terminate them first (see above).

### P3: Container starts but database not ready
Some databases take 10-30 seconds to initialize. Wait and check logs:
```bash
sleep 15 && docker logs db-container 2>&1 | tail -10
```

### P4: Default password in image
Many database images have a hardcoded default password (often base64-encoded in the entrypoint). Always set your own via env vars.

### P5: Encoding mismatch
If restoring data with special characters (Chinese, etc.), ensure the target database encoding matches the source (e.g., `UTF-8`, `GBK`).

### P6: Docker Hub private repo
If `docker pull` returns "repository does not exist or may require 'docker login'" but you're sure the image exists, it's likely **private**. Either:
- Make it public on Docker Hub (repo Settings → Visibility → Public)
- Or login first: `sudo docker login` then pull

### P7: NAT traversal for external access
When the app runs on an internal network and needs internet access:
- **Router port forwarding** is the most reliable: forward external port to internal IP:port
- **Cloudflare quick tunnels** (recommended, no registration): `cloudflared tunnel --url http://localhost:3000` → temporary `*.trycloudflare.com` HTTPS URL
- **ngrok** requires account + auth token; **localtunnel/serveo** often fail on restricted networks
- If the machine has a public IPv4 (check `curl -4 ifconfig.me`), port forwarding is simplest
- Always test external access: `curl -4 -s -o /dev/null -w "%{http_code}" http://PUBLIC_IP:PORT`

See `references/cloudflare-tunnels.md` for cloudflared setup and usage.

## Object Storage: MinIO

Many apps need S3-compatible object storage alongside the database. MinIO is the standard choice for self-hosted deployments.

### Deploy MinIO in Docker

```bash
sudo docker run -d \
  --name minio \
  -e MINIO_ROOT_USER=minioadmin \
  -e MINIO_ROOT_PASSWORD=minioadmin \
  -p 9000:9000 \
  -p 9001:9001 \
  -v minio_data:/data \
  --restart unless-stopped \
  minio/minio server /data --console-address ":9001"
```

- **Port 9000** — S3 API endpoint
- **Port 9001** — Web console (browser UI)
- **Volume** — `minio_data` for persistent storage

### Create a bucket

Via CLI (install `mc` first) or via the console at `http://localhost:9001`.

### Connection from app

```env
MINIO_ENDPOINT="http://localhost:9000"
MINIO_ACCESS_KEY="minioadmin"
MINIO_SECRET_KEY="minioadmin"
MINIO_BUCKET="my-bucket"
MINIO_USE_SSL="false"
```

## Full-Stack Deployment Pattern

For apps with database + object storage + app server (e.g., Next.js + Prisma + PostgreSQL + MinIO):

1. **Docker services:** PostgreSQL, MinIO (and any other infra)
2. **Local app:** `npm install` → `npx prisma db push` → `npm run dev`
3. **`.env` file:** Point `DATABASE_URL` and `MINIO_ENDPOINT` to `localhost` (not Docker service names, since app runs on host)

Key: Docker containers use `--name` for inter-container DNS, but host-to-container uses `localhost` + published ports.

## Docker Daemon Proxy Configuration

When Docker Hub is inaccessible (common in China), configure the Docker daemon to use a local proxy:

```bash
sudo mkdir -p /etc/systemd/system/docker.service.d
sudo tee /etc/systemd/system/docker.service.d/proxy.conf <<'EOF'
[Service]
Environment="HTTP_PROXY=http://127.0.0.1:7890"
Environment="HTTPS_PROXY=http://127.0.0.1:7890"
Environment="NO_PROXY=localhost,127.0.0.1,10.0.0.0/8,172.16.0.0/12,192.168.0.0/16"
EOF
sudo systemctl daemon-reload && sudo systemctl restart docker
```

Test proxy connectivity before configuring:
```bash
curl -x http://127.0.0.1:7890 -s --connect-timeout 5 https://registry-1.docker.io/v2/
```

**Clash/mihomo:** Use the mixed port (7890) rather than the HTTP port (7891) — the mixed port handles both HTTP and HTTPS better.

**Mirror registries** are an alternative but often unreliable. Proxy is more reliable.

## GitHub Authentication for Cloning

When `gh` CLI isn't available or token lacks `read:org` scope, use HTTPS + stored credentials:

```bash
git config --global credential.helper store
# Token stored in ~/.git-credentials after first use
git clone https://USER:TOKEN@github.com/USER/REPO.git
```

## See Also

- `references/kingbasev8.md` — KingbaseES V8 specific notes
- `references/cloudflare-tunnels.md` — Expose local services to internet via Cloudflare tunnels
- `references/nextjs-fullstack-deploy.md` — Next.js + Prisma + Docker deployment and code modification workflow
- `references/jellyfin-media-server.md` — Jellyfin media/TV streaming server deployment for smart TVs