Kubernetes - Postgres - Create Tables

user_7961259 0
Viewed 2484

I can create a PostgresSQL deployment in Kubernetes with volumes with no problems. The question I have is how to create the database tables.

I can easily exec in the pod and create the tables but I want it to be automatically createded.

I don't want to build in into the docker image as a want a generic image.

I have thought about a few options such as Kubernetes Batch Job only running once but not sure what the best approach is ?

Thanks.

kubernetes postgresql
2 Answers

You have multiple way:

The official postgresql docker image state (the 'Initialization scripts' section):

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d

Those scripts are only run if the database is created. eg if you start/restart a pod with a data volume containing an already existing database, those scripts will not be launched.

With kubernetes, you can provide a configmap with the needed file (if file sizes below 1Mb) or provide a volume with your initialization file.

An another option can be the application itself. For instance you may use flywayDB or liquibase embeded in your application (springboot do that transparently).

user_62445600

this may help (here I have added configmap, persistent volume, persistent volume-claim, and Postgres deployment yaml. This yaml will automatically create a table named users in the Postgres database inside the Postgres-container. Thanks

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  Postgres_DB: postgresdb

---
apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
data:
  Postgres_User: postgresadmin
  Postgres_Password: admin123

---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: postgres-pv-volume
  labels:
    type: local
    app: postgres
spec:
  storageClassName: manual
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/mnt/data"

---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-pv-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 5Gi

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres-container
          image: postgres:latest
          imagePullPolicy: "IfNotPresent"
          lifecycle:
            postStart:
              exec:
                command: ["/bin/sh","-c","sleep 20 && PGPASSWORD=$POSTGRES_PASSWORD psql -w -d $POSTGRES_DB -U $POSTGRES_USER -c 'CREATE TABLE IF NOT EXISTS users (userid SERIAL PRIMARY KEY,username TEXT,password TEXT,token TEXT,type TEXT);'"]
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_DB
              valueFrom:
                configMapKeyRef:
                  name: postgres-config
                  key: Postgres_DB
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: Postgres_User
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: Postgres_Password               
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim
user_177298630
Related