SQL script isn't running Kubernetes, but runs fine using just Docker

Question

Have a pretty simple test.sql:

SELECT 'CREATE DATABASE test_dev'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'test_dev')\gexec

\c test_dev

CREATE TABLE IF NOT EXISTS test_table (
  username varchar(255)
);

INSERT INTO test_table(username)
VALUES ('test name');

Doing the following does what I expected it to do:

Dockerfile.dev

FROM postgres:11-alpine
EXPOSE 5432
COPY ./db/*.sql /docker-entrypoint-initdb.d/

docker build -t testproj/postgres -f db/Dockerfile.dev .

docker run -p 5432:5432 testproj/postgres

This creates the database, switches to it, creates a table, and inserts the values.

Now I'm trying to do the same in Kubernetes with Skaffold, but nothing really seems to happen: no error messages, but nothing changed in postgres

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: init-script
spec:
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 500Mi
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-storage
spec:
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 10Gi

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      component: postgres
  template:
    metadata:
      labels:
        component: postgres
    spec:
      volumes:
        - name: postgres-storage
          persistentVolumeClaim:
            claimName: postgres-storage
        - name: init-script
          persistentVolumeClaim:
            claimName: init-script
      containers:
        - name: postgres
          image: postgres
          ports:
            - containerPort: 5432
          volumeMounts:
            - name: postgres-storage
              mountPath: /var/lib/postgresql/data
              subPath: postgres
            - name: init-script
              mountPath: /docker-entrypoint-initdb.d
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-cluster-ip-service
spec:
  type: ClusterIP
  selector:
    component: postgres
  ports:
    - port: 5432
      targetPort: 5432

What I am doing wrong here?

Basically tried to follow the answers here, but isn't panning out. Sounded like I needed to move the .sql to a persistent volume.

https://stackoverflow.com/a/53069399/3123109

score 2 · Answer 1 · answered Nov 21 '19 at 04:45

2

You don’t want to mount a volume over the entry point folder. You are basically masking the script in your image with an empty folder. Also you aren’t using your modified image, so it wouldn’t have your script in the first place.

answered Nov 21 '19 at 04:45

coderanger

52,400
4
52
75

score 1 · Answer 2 · answered Nov 21 '19 at 10:49

I'm not 100% sure your image will work on Kubernetes.

I would recommend using something tested like PostgreSQL chart by bitnami, also it might be helpful to read Using Kubernetes to Deploy PostgreSQL.

If you want to use your own image inside the Kubernetes you need to push the image to your private Docker registry or repository. This is explained here Pull an Image from a Private Registry.

As for the test.sql you can store it into ConfigMap:

apiVersion: v1
kind: ConfigMap
metadata:
  name: test-sql
data:
  test.sql: |
    SELECT 'CREATE DATABASE test_dev'
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'test_dev')\gexec
    \c test_dev
    CREATE TABLE IF NOT EXISTS test_table (
      username varchar(255)
    );
    INSERT INTO test_table(username)
    VALUES ('test name');

Which you can later mount as init.sql or execute after pod was created.

score 0 · Accepted Answer · answered Dec 06 '19 at 00:01

coderanger pointed out a glaring mistake that got me going in the right direction: I wasn't referring to the modified image.

I update accordingly:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      component: postgres
  template:
    metadata:
      labels:
        component: postgres
    spec:
      containers:
        - name: postgres
          image: testproject/postgres
          ports:
            - containerPort: 5432
          volumeMounts:
            - name: postgres-storage
              mountPath: /var/lib/postgresql/data
              subPath: postgres
      volumes:
        - name: postgres-storage
          persistentVolumeClaim:
            claimName: postgres-storage
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-cluster-ip-service
spec:
  type: ClusterIP
  selector:
    component: postgres
  ports:
    - port: 5432
      targetPort: 5432

Then it started loading the data accordingly.

SQL script isn't running Kubernetes, but runs fine using just Docker

3 Answers3

Linked