Database disappears after pod restarts

[smercerdrest]

Bug report

Describe the bug

Database disappears after pod restarts related to the issue with docker mounting here

https://datageek.blog/en/2020/06/17/disappearing-data-in-postgresql/

This always creates two mount points

postgres@supabase-database-0:/$ lsblk NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT nvme0n1 259:0 0 100G 0 disk ├─nvme0n1p1 259:1 0 100G 0 part /var/lib/postgresql/data └─nvme0n1p128 259:2 0 1M 0 part nvme1n1 259:3 0 10G 0 disk nvme2n1 259:4 0 8G 0 disk /var/lib/postgresql

when a container or pod is restarted the database is not persisted as it is added to the container mount and not the host.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

When setting the PGDATA env variable to the recommended directory for postgres PGDATA=/var/lib/postgresql/data/pgdata (or any directory not /var/lib/postgresql/data)get the following error

2022-12-13 12:44:20.599 GMT [1] LOG: skipping missing configuration file "/var/lib/postgresql/data/postgresql.auto.conf" 2022-12-13 12:44:20.599 UTC [1] FATAL: "/var/lib/postgresql/data" is not a valid data directory 2022-12-13 12:44:20.599 UTC [1] DETAIL: File "/var/lib/postgresql/data/PG_VERSION" is missing.

example values from stateful set

        - name: supabase-database
          command: [ "/bin/bash", "-c", "--" ]
          args: [ "while true; do sleep 30; done;" ]
          image: docker.io/supabase/postgres:15.1.0.1
          ports:
            - name: tcp-postgresql
              containerPort: 5432
              protocol: TCP
          env:
            - name: BITNAMI_DEBUG
              value: 'false'
            - name: POSTGRESQL_PORT_NUMBER
              value: '5432'
            - name: POSTGRESQL_VOLUME_DIR
              value: /var/lib/postgresql/data
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: supabase-database
                  key: postgresql-password
            - name: POSTGRESQL_WAL_LEVEL
              value: LOGICAL
            - name: POSTGRESQL_ENABLE_LDAP
              value: 'no'
            - name: POSTGRESQL_ENABLE_TLS
              value: 'no'
            - name: POSTGRESQL_LOG_HOSTNAME
              value: 'false'
            - name: POSTGRESQL_LOG_CONNECTIONS
              value: 'false'
            - name: POSTGRESQL_LOG_DISCONNECTIONS
              value: 'false'
            - name: POSTGRESQL_PGAUDIT_LOG_CATALOG
              value: 'off'
            - name: POSTGRESQL_CLIENT_MIN_MESSAGES
              value: error
            - name: POSTGRESQL_SHARED_PRELOAD_LIBRARIES
              value: pgaudit
          resources:```  

## Expected behavior

When swapping out the supabase/postgres docker image for the binnami/postgres image this resolves the issue completely so I think there maybe something in the supabase/postgres image that is defaulting to look for a database in  `/var/lib/postgresql/data` no matter what is passed into PGDATA


## System information

- OS: EKS 1.21


## Additional context

Add any other context about the problem here.

[shentschel]

I had the same problem with
supabase/postgres:14.1.0.21. In my case I had to add a configMap to use a custom postgresql.conf file to change the behavior:

        volumeMounts:
            - name: config-volume
              mountPath: /etc/postgresql/postgresql.conf
              subPath: postgresql.conf
      volumes:
        - name: config-volume
          configMap:
            name: postgres-config
            items:
              - key: postgresql.conf
                path: postgresql.conf

The original file has a data_directory entry which seems to be used instead of PGDATA.
Maybe this helps to solve the problem.