Kubernetes PostgreSQL: How do I store config files elsewhere than the data directory?

Question

Currently I'm trying to create a PostgreSQL Deployment for replication, using the postgres:latest image.

The config files are located by default within the data directory /var/lib/postgresql/data. For replication to work I need the data directory to be empty, but that means I have to keep the config files elsewhere.

Referring to the PostgreSQL Documentation:

If you wish to keep the configuration files elsewhere than the data directory, the postgres -D command-line option or PGDATA environment variable must point to the directory containing the configuration files, and the data_directory parameter must be set in postgresql.conf (or on the command line) to show where the data directory is actually located. Notice that data_directory overrides -D and PGDATA for the location of the data directory, but not for the location of the configuration files.

In a physical machine setup, we can manually move the files and set the location of data-directory in the postgresql.conf file. However in Kubernetes it is not so straight-forward.

I tried to use volumeMount with subPath to mount the config files in another location, then use command to change the new location of postgresql.conf.

Sample .yaml file:

apiVersion: v1
kind: ConfigMap
metadata:
  name: pg-replica
  labels:
    app: postgres
    name: pg-replica
data:
  POSTGRES_DB: postgres
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: mypassword
  pg_hba.conf: |
    # Contents
  postgresql.conf: |
    data_directory = /var/lib/postgresql/data/data-directory
  recovery.conf: |
    # Contents
---
apiVersion: v1
kind: Service
metadata:
  name: pg-replica
  labels:
    app: postgres
    name: pg-replica
spec:
  type: NodePort
  ports:
  - nodePort: 31000
    port: 5432
  selector:
    app: postgres
    name: pg-replica
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pg-replica
spec:
  selector:
    matchLabels:
      app: postgres
      name: pg-replica
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
        name: pg-replica
    spec:
      containers:
        - name: pg-replica
          image: postgres:latest
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: pg-replica
          volumeMounts:
            - name: pg-replica
              mountPath: /var/lib/postgresql/data
            - name: replica-config
              mountPath: /var/lib/postgresql/postgresql.conf
              subPath: postgresql.conf
            - name: replica-config
              mountPath: /var/lib/postgresql/pg_hba.conf
              subPath: pg_hba.conf
            - name: replica-config
              mountPath: /var/lib/postgresql/recovery.conf
              subPath: recovery.conf
          command:
            - "/bin/bash"
            - "postgres -c config_file=/var/lib/postgresql/postgresql.conf"
      volumes:
        - name: pg-replica
          persistentVolumeClaim:
            claimName: pv-replica-claim
        - name: replica-config
          configMap:
            name: pg-replica

The returned message was as following:

/bin/bash: postgres -c config_file=/var/lib/postgresql/postgresql.conf: No such file or directory

What is wrong with this configuration? And what steps am I missing to make it work?

Edit: When using the volumeMount field, the directory is overwritten (all other files were removed) despite I specified the exact file to mount on with subPath. What could be the cause for this?

Answer 1

I realized there were a few mistakes here after posting this question...

1. I used PostgreSQL 11 for replication prior so I assumed they worked the same way (which of course is wrong, there are some changes). The recovery.conf is omitted from PostgreSQL 12 and it gave this error message FATAL: XX000: using recovery command file "recovery.conf" is not supported when I had it. so I had to remove it from my ConfigMap.

2. I confused about Docker's Entrypoint & Command to Kubernetes' Command & Args. After being corrected by my senior that Kubernetes Command will override the Docker Entrypoint, I'm going to need and use only Args afterwards.

The following are the changes I made to my ConfigMap and Deployment.

apiVersion: v1
kind: ConfigMap
metadata:
  name: pg-replica
  labels:
    app: postgres
    name: pg-replica
data:
  POSTGRES_DB: postgres
  POSTGRES_USER: postgres
  POSTGRES_PASSWORD: mypassword
  pg_hba.conf: |
    # Contents
  postgresql.conf: |
    data_directory = '/var/lib/postgresql/data'
    # the contents from recovery.conf are integrated into postgresql.conf
    primary_conninfo = # host address and authentication credentials
    promote_trigger_file = # trigger file path
  extra.sh: |
    #!/bin/sh
    postgres -D /var/lib/postgresql
---
apiVersion: v1
kind: Service
metadata:
  name: pg-replica
  labels:
    app: postgres
    name: pg-replica
spec:
  type: NodePort
  ports:
  - nodePort: 31000
    port: 5432
  selector:
    app: postgres
    name: pg-replica
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pg-replica
spec:
  selector:
    matchLabels:
      app: postgres
      name: pg-replica
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
        name: pg-replica
    spec:
      containers:
        - name: pg-replica
          image: postgres:latest
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: pg-replica
          volumeMounts:
            - name: pg-replica
              mountPath: /var/lib/postgresql/data
            - name: replica-config
              mountPath: /var/lib/postgresql/postgresql.conf
              subPath: postgresql.conf
            - name: replica-config
              mountPath: /var/lib/postgresql/pg_hba.conf
              subPath: pg_hba.conf
            - name: replica-config
              mountPath: /docker-entrypoint-initdb.d/extra.sh
              subPath: extra.sh
          args:
            - "-c"
            - "config_file=/var/lib/postgresql/postgresql.conf"
            - "-c"
            - "hba_file=/var/lib/postgresql/pg_hba.conf"
      volumes:
        - name: pg-replica
          persistentVolumeClaim:
            claimName: pv-replica-claim
        - name: replica-config
          configMap:
            name: pg-replica

The arguments in Args will set the location of the .conf files to where I specified.

For further steps in Replication:

1. After the pod is up, I manually ran the pod's shell with kubectl exec.

2. I removed all the files from the data-directory for step 3 (to copy files from master pod).

rm -rf /var/lib/postgresql/data/*

3. Use pg_basebackup to backup data from master node.

pg_basebackup -h <host IP> --port=<port number used> -D  /var/lib/postgresql/data -P -U replica -R -X stream

And that's it. Now I managed to have my pg-replica pod replicating my master pod.

Answer 2

As mentioned in comments, I really encorage you to use the Postgres Helm chart to setup yout environment.

The way you solved the issue could work, but if the pod died for some reason, all work you have done will be lost and you'll need to reconfigure everything again.

Here you can found all information about how to create a postgres deployment with high availability and replication.

To install HELM you can follow this guide.