Postgres data folder empty on kubernetes

[MK-2001]

Describe the situation

We are using postgres 11.7. We mount the data volume on /var/lib/postgresql via an cider pvc.
When we use the path /var/lib/postgresql/data the mount will be done with the permissions root:postgres. In this case postgres is not able to takeover the ownership for the folder .../data and aborts the startup with the message "Opertation not permitted".

When we mount the dir /var/lib/postgresql we can see the folders /var/lib/postgesql/data and during postgres is running there are all needed files. But after we stopped the container the folder .../data is empty.

What is expected

After shutdown the container there should be all data still available within the folder /var/lib/postgresql/data.

Alternative solutions:

The startup script may be not need the User-Ownership of the folder /var/lib/postgresql/data then it does not interrupt the startup.

Questions:

How to handle postgres with a docker image on kubernetes?
Is the .../data directory an hardlink directy?
Why there are no failes anymore in the directory after shutdown?

Similiar issues:

#103 / #560

• But in kubernetes it is not possible to mount an volume not as root user. Just the group is changeable.
• it seem to be an locking issue between postgres and k8s

[MK-2001]

Currently found an Workaround:
#116 (comment)

Is is only possible to mount the directory /var/lib/postgresql/data directly and use PGDATA env.-variable to use a different subdirectory like /var/lib/postgresql/data/pgdata.

It was not possible to move the directory outsite of /var/lib/postgresql/data like /usr/postgresql/data or other locations.

[yosifkit]

When we mount the dir /var/lib/postgresql we can see the folders /var/lib/postgesql/data and during postgres is running there are all needed files. But after we stopped the container the folder .../data is empty.

The answer is here: #103 (comment) (quote below):

Indeed. Because "/var/lib/postgresql/data" is marked as a VOLUME in the
Dockerfile, Docker will always create a new volume there unless it's
explicitly mounted over top of, even if "/var/lib/postgresql" is a volume
above it.

---

How to handle postgres with a docker image on kubernetes?

While the postgres image was improved to support running as an "arbitrary user" (#253), it doesn't really like to.

The main caveat to note is that postgres doesn't care what UID it runs as (as long as the owner of /var/lib/postgresql/data matches), but initdb does care (and needs the user to exist in /etc/passwd):

So, the database folder must be owned by the user running postgres. The easiest way to do that is to define PGDATA to a subdirectory of a directory writable by the running user (so, a subdirectory of your kubernetes-defined volume). This way the postgres user can create and chmod the directory for itself (since it can't change permissions of the "cider pvc").

    volumeMounts:
    - name: postgres-data
      mountPath: /var/lib/postgresql/data
    env:
    - name: PGDATA
      value: /var/lib/postgresql/data/pgdata

It was not possible to move the directory outsite of /var/lib/postgresql/data like /usr/postgresql/data or other locations.

Were those defined the same, i.e. PGDATA was a subdirectory of the volume?

---

Is the .../data directory an hardlink directy?
Why there are no failes anymore in the directory after shutdown?

It is a VOLUME. See quote above (#103 (comment))

[walec51]

this is such a crazy issue I thought it was a problem on Kubernetes CSI / Filesystem level:

digitalocean/csi-digitalocean#289

postgreses Dockerfile enforces you to mount something to /var/lib/postgresql/data however if you try to mount a persistent volume claim it will fail suggest to you in the logs to mount that volume to /var/lib/postgresql

but if you mount it to /var/lib/postgresql/ you end up with a persistent volume in which the data directory will be wiped by your docker pod / container on each restart

lost two days on tinkering due to this

my workaround was to change the PGDATA directory to a totally different one:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres-reservation-db
  namespace: staging
spec:
  serviceName: postgres-reservation-db
  replicas: 1
  selector:
    matchLabels:
      app: postgres-reservation-db
  template:
    metadata:
      labels:
        app: postgres-reservation-db
    spec:
      containers:
        - name: postgres
          image: postgres:12.2
          env:
          - name: PGDATA
            value: /work/data
          - name: POSTGRES_PASSWORD
            value: someTestPassword
          ports:
          - containerPort: 5432
          resources:
            requests:
              cpu: "200m"
              memory: "128Mi"
            limits:
              cpu: "500m"
              memory: "256Mi"
          volumeMounts:
          - name: data
            mountPath: /work
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      accessModes: [ "ReadWriteOnce" ]
      resources:
        requests:
          storage: 20Gi

please at least improve the documentation and to NOT suggest to mount in /var/lib/postgresql

[MK-2001]

May be the docker image should not contain the default VOLUME on /var/lib/postgresql/data . Specially in case that you can change the volume to any other location with PGDATA.

Without the volume should be the problem solved, too.

[yosifkit]

Maybe the docker image should not contain the default VOLUME

That is #404.

---

We don't recommend using /var/lib/postgresqlin the documentation or the entrypoint script; I am unsure where the recommendation to use it would be coming from. All volume recommendations in the docs use /var/lib/postgresql/data as the volume destination since that is the VOLUME defined in the image (and the default value of PGDATA).

We also point out that on some volumes, you need to use a subdirectory of the mount for the postgres data.

if the data volume you're using is a filesystem mountpoint (like with GCE persistent disks), Postgres initdb recommends a subdirectory (for example /var/lib/postgresql/data/pgdata ) be created to contain the data.

- docker hub docs

[carloreggiani]

Thank you for describe the source of my headache trying to deploy Red Hat CodeReady Workspaces on my OpenShift 4.3 lab with a NFS storage class!

Using the Operator seems not possible to edit the deployment config yaml to change the path...

[yosifkit]

Closing old issue; it seems to be sufficiently resolved.