PostgreSQL 18: OLD_DATABASES detection fails to find data in /var/lib/postgresql/data/18/docker/ causing silent data loss on container recreation

[athom]

Summary

The docker_setup_env() function in the PostgreSQL 18 entrypoint script fails to detect existing database data located
at /var/lib/postgresql/data/18/docker/ during container recreation, leading to silent data loss through creation of
a new database in an anonymous volume.

Environment

• Image: postgres:18.1-alpine
• PGDATA: /var/lib/postgresql/18/docker (default for PostgreSQL 18)
• Volume Configuration:

  volumes:
    - ./data/postgres:/var/lib/postgresql/data
  

Root Cause

The glob pattern used to detect old databases is incomplete:

Current detection logic (docker-entrypoint.sh, lines 250-254):

  for d in /var/lib/postgresql /var/lib/postgresql/data /var/lib/postgresql/*/docker; do
      if [ -s "$d/PG_VERSION" ]; then
          OLD_DATABASES+=( "$d" )
      fi
  done

Problem: The pattern /var/lib/postgresql/*/docker matches only two-level paths:

• ✅ Matches: /var/lib/postgresql/18/docker
• ❌ Misses: /var/lib/postgresql/data/18/docker (three-level path)

Reproduction Steps

Initial Setup (First Container)

  # Create docker-compose.yml
  cat > docker-compose.yml <<EOF
  services:
    postgres:
      image: postgres:18.1-alpine
      volumes:
        - ./data/postgres:/var/lib/postgresql/data
      environment:
        POSTGRES_USER: testuser
        POSTGRES_PASSWORD: testpass
        POSTGRES_DB: testdb
  EOF

  # Start container
  docker compose up -d

  # Verify data location
  docker exec postgres ls -la /var/lib/postgresql/data/18/docker/
  # Output shows PG_VERSION exists

  # Insert test data
  docker exec postgres psql -U testuser -d testdb -c "CREATE TABLE test (id INT); INSERT INTO test VALUES (1);"
  docker exec postgres psql -U testuser -d testdb -c "SELECT * FROM test;"
  # Output: 1 row

Container Recreation (Triggers Bug)

# Recreate container (e.g., due to config change)
docker compose down
docker compose up -d

# Check data - LOST!
docker exec postgres psql -U testuser -d testdb -c "SELECT * FROM test;"
# ERROR: relation "test" does not exist

Why Data Loss Occurs

1. First container: PostgreSQL initializes data at /var/lib/postgresql/data/18/docker/ (bind mount)
2. Container recreation: New anonymous volume created for /var/lib/postgresql/
3. Entrypoint check:

# PGDATA = /var/lib/postgresql/18/docker (in anonymous volume)
[ -s "$PGDATA/PG_VERSION" ]  # FALSE (anonymous volume is empty)

# Check old database locations
for d in /var/lib/postgresql /var/lib/postgresql/data /var/lib/postgresql/*/docker; do
    # /var/lib/postgresql/18/docker - matches, but is new (empty)
    # /var/lib/postgresql/data/18/docker - NOT MATCHED by glob pattern!
done

4. Result: OLD_DATABASES array remains empty → No error raised → initdb runs in anonymous volume → New database created
   → Old data silently abandoned

Verification

Test glob pattern behavior:

In running container

  docker exec postgres sh -c 'for d in /var/lib/postgresql /var/lib/postgresql/data /var/lib/postgresql/*/docker; do if [
   -s "$d/PG_VERSION" ]; then echo "Found: $d"; fi; done'
  # Output: Found: /var/lib/postgresql/18/docker
  # MISSING: /var/lib/postgresql/data/18/docker

With fixed pattern:

  docker exec postgres sh -c 'for d in /var/lib/postgresql /var/lib/postgresql/data /var/lib/postgresql/*/docker
  /var/lib/postgresql/data/*/docker; do if [ -s "$d/PG_VERSION" ]; then echo "Found: $d"; fi; done'
  # Output: Found: /var/lib/postgresql/18/docker
  #         Found: /var/lib/postgresql/data/18/docker ✓

Impact

• Severity: Critical - Silent data loss
• Scope: Any deployment where:
  • Volume mounted at /var/lib/postgresql/data (common legacy pattern)
  • Container recreation occurs (e.g., docker-compose config changes, docker run --force-recreate)
• No warnings: Users receive no error messages - database appears to work but data is lost

Proposed Fix

Update glob pattern in docker_setup_env() to include three-level paths:

  for d in /var/lib/postgresql \
           /var/lib/postgresql/data \
           /var/lib/postgresql/*/docker \
           /var/lib/postgresql/data/*/docker; do  # <-- ADD THIS LINE
      if [ -s "$d/PG_VERSION" ]; then
          OLD_DATABASES+=( "$d" )
      fi
  done

This ensures detection of databases in:

• /var/lib/postgresql/18/docker (new default)
• /var/lib/postgresql/data/18/docker (legacy bind mount + PostgreSQL 18 subdirectory)
• /var/lib/postgresql/17/docker (old version upgrade scenarios)

Workaround

Option 1: Mount parent directory (PostgreSQL 18 recommendation)
volumes:
- ./data/postgres:/var/lib/postgresql # <-- No /data suffix

Option 2: Set explicit PGDATA in bind mount
environment:
PGDATA: /var/lib/postgresql/data/pgdata
volumes:
- ./data/postgres:/var/lib/postgresql/data

Option 3: Manual recovery after recreation

  # Remove anonymous volume
  docker compose down
  docker volume rm $(docker volume ls -q -f name=postgres)
  # Restart - will use bind mount data
  docker compose up -d

Related Issues

• Change PGDATA in 18+ to /var/lib/postgresql/MAJOR/docker #1259 - PostgreSQL 18 directory structure changes
• Postgres 18: VOLUME/PGDATA path change breaks existing mounts — docs/notes request #1370 - VOLUME/PGDATA path change breaks existing mounts
• Remove intentionally-breaking "data" symlink and add better detection #1372 - Add better detection for unused mounts (only detects empty mounts, not this case)
• PostgreSQL 18: Init scripts in /docker-entrypoint-initdb.d/ never execute due to automatic version subdirectory creation #1373 - Init scripts not executing due to subdirectory creation

Additional Context

This bug was discovered during a production deployment where a configuration change (adding extra_hosts to Caddy
service) triggered container recreation via docker compose up -d. The PostgreSQL container was recreated despite no
changes to its own configuration, resulting in data loss for a production database. The bind mount contained 9.9MB of
user data in /var/lib/postgresql/data/18/docker/, but the entrypoint script failed to detect it, creating a fresh
database in an anonymous volume instead.

[tianon]

(sorry, GitHub is glitching 😬)

I'm missing something - where did /var/lib/postgresql/data/18/docker come from? That's not a path we've ever used nor a path we've ever recommended using. 😅

[Fomys]

If it is like me, from https://docs.goauthentik.io/install-config/install/docker-compose/
They recommend the volumes:

volumes:
    - database:/var/lib/postgresql/data

I personally have this in my configuration, and face the same issue:

services:
  postgresql:
    image: docker.io/library/postgres:18-alpine
    networks:
      backend:
    volumes:
      - database:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD_FILE: /run/secrets/authentik.dbpassword
      POSTGRES_USER_FILE: /run/secrets/authentik.dbuser
      POSTGRES_DB_FILE: /run/secrets/authentik.db
    secrets:
      - authentik.dbpassword
      - authentik.dbuser
      - authentik.db

I already restarted the previous version of the container worked like a charm, I restarted / updated it multiple times, until today.

I will try to "fix" by exporting from an old container / importing in the new container.

(thanks @tianon, I did not notice the difference!)

[tianon]

Their file (https://docs.goauthentik.io/docker-compose.yml) specifies PG 16 with that volume - they don't use 18. If you want to upgrade that to 18, you'll need to swap the volume to /var/lib/postgresql.

[Birdie0]

I've been running a PostgreSQL 18 database in Docker for a while, periodically updating it, and stumbled upon this same issue. Everything was running smoothly until the database suddenly stopped responding. I restarted the service, but then all my databases were gone. During a volume inspection, I found that the database files were still present in the /var/lib/postgresql/data/18/docker directory. Changing the volume mapping to db_data:/var/lib/postgresql resolved the issue.

For context on why the old path was used: I've been using the same Compose file since PostgreSQL 16 and wasn't aware that the default path had changed and it was working file so I never questioned it.

Original compose file:

services:
  db:
    image: postgres:18-alpine
    restart: unless-stopped
    pull_policy: always
    volumes:
      - db_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: postgres
volumes:
  db_data:

[autorejecttop]

Can confirm, happened when deploying a StatefulSet with k3d cluster using:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  namespace: exercises
  name: ping-pong-postgres-statefulset
spec:
  serviceName: ping-pong-postgres-service
  replicas: 1
  selector:
    matchLabels:
      app: ping-pong-postgres-app
  template:
    metadata:
      labels:
        app: ping-pong-postgres-app
    spec:
      containers:
        - name: ping-pong-postgres-app
          image: postgres:18.3-alpine3.23
          volumeMounts:
            - name: ping-pong-postgres-volume
              mountPath: /var/lib/postgresql/data
          ports:
            - name: db
              containerPort: 5432
          env:
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: ping-pong-postgres-secret
                  key: postgres_password
  volumeClaimTemplates:
    - metadata:
        namespace: exercises
        name: ping-pong-postgres-volume
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: local-path
        resources:
          requests:
            storage: 100Mi

Managed to find the problem after 6 hours searching by luck by trying the postgres:18.3-trixie image where the log about volume mount to use is /var/lib/postgresql and not /var/lib/postgresql/data. There was no log about it on image postgres:18.3-alpine3.23, it was a nightmare.

[banagale]

I was affected by this change.

I upgraded from postgres:16 to postgres:18-alpine in March. The upgrade went smoothly.

I did not realize the volume mount at /var/lib/postgresql/data was now pointing at nothing. Postgres was silently writing to /var/lib/postgresql/18/docker on the container's ephemeral filesystem.

Everything worked perfectly for three weeks. Then while doing a routine deploy I recreated the container and all data was gone.

The failure mode here is particularly bad because there is no error, no warning, no indication anything is wrong. The database starts, accepts connections, runs queries, passes health checks. You only discover the volume isn't being used when the container dies.

At minimum, the entrypoint script should detect when PGDATA resolves to a path outside any mounted volume and log a warning. A silent data loss time bomb is the worst possible outcome of a default change.

[tianon]

The entrypoint script does handle that case though, and if /var/lib/postgresql/data had been a volume but was unused, that would cause not just a warning, but a startup error: 🤔

postgres/docker-entrypoint.sh

Lines 255 to 257 in 6edb0a8

	if [ "${#OLD_DATABASES[@]}" -eq 0 ] && [ "$PG_MAJOR" -ge 18 ] && mountpoint -q /var/lib/postgresql/data; then
	OLD_DATABASES+=( '/var/lib/postgresql/data (unused mount/volume)' )
	fi

$ docker run -it --rm --pull=always -v /var/lib/postgresql/data -e POSTGRES_PASSWORD=example postgres:18
18: Pulling from library/postgres
Digest: sha256:52e6ffd11fddd081ae63880b635b2a61c14008c17fc98cdc7ce5472265516dd0
Status: Image is up to date for postgres:18
Error: in 18+, these Docker images are configured to store database data in a
       format which is compatible with "pg_ctlcluster" (specifically, using
       major-version-specific directory names).  This better reflects how
       PostgreSQL itself works, and how upgrades are to be performed.

       See also https://github.com/docker-library/postgres/pull/1259

       Counter to that, there appears to be PostgreSQL data in:
         /var/lib/postgresql/data (unused mount/volume)

       This is usually the result of upgrading the Docker image without
       upgrading the underlying database using "pg_upgrade" (which requires both
       versions).

       The suggested container configuration for 18+ is to place a single mount
       at /var/lib/postgresql which will then place PostgreSQL data in a
       subdirectory, allowing usage of "pg_upgrade --link" without mount point
       boundary issues.

       See https://github.com/docker-library/postgres/issues/37 for a (long)
       discussion around this process, and suggestions for how to do so.

[autorejecttop]

I am guessing it's a problem for Alpine images, probably about bash and sh incompatibilities

[tianon]

Oh that's wild, yeah, the error doesn't show up for Alpine - I'll take a look. 🙇

Edit: to be clear, we do use bash in both cases, but mountpoint is not a builtin, so that's probably working differently in Alpine

Edit 2: woof:

$ docker run --rm -v /var/lib/postgresql/data postgres:18 mountpoint /var/lib/postgresql/data
/var/lib/postgresql/data is a mountpoint
$ docker run --rm -v /var/lib/postgresql/data postgres:18-alpine mountpoint /var/lib/postgresql/data
/var/lib/postgresql/data is not a mountpoint

Edit 3: ah, the implementation of mountpoint in coreutils checks /proc/self/mountinfo, but the implementation of mountpoint in BusyBox just does stat arg + stat arg/.. and compares their dev and ino numbers 😭 https://github.com/tianon/mirror-busybox/blob/be7d1b7b1701d225379bc1665487ed0871b592a5/util-linux/mountpoint.c#L78 (the implication being that it misses bind-mounts entirely, which is what container mounts end up being)

PR up: #1409