How to deploy postgres with a new schema on kubernetes?

Question

I have used this link to deploy Postgres on Kubernetes.

below is the configmap :

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  POSTGRES_DB: postgresdb
  POSTGRES_USER: postgresadmin
  POSTGRES_PASSWORD: admin123

and deployment :

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:10.4
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5432
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim

I hope, a simple configuration at configmap or deployment yaml will help to create a new schema on postgres db.

Where do you have your schema file on local or a repository? — hariK
– hariK, Commented May 19, 2020 at 7:17
actually, I wanted to just create a schema CREATE SCHEMA IF NOT EXISTS key; and store in configmap. — Siddhanta Rath
– Siddhanta Rath, Commented May 19, 2020 at 7:21

hariK · Accepted Answer · 2020-05-19 07:54:22Z

5

You can use Kubernetes postStart lifecycle hook to create your schema on given Postgres DB. Just update your deployment file with the lifecycle block.

apiVersion: v1
kind: Pod
metadata:
  creationTimestamp: null
  labels:
    run: postgres
  name: postgres
spec:
  containers:
  - image: postgres:10.4
    name: postgres
    resources: {}
    lifecycle:
      postStart:
        exec:
          command: ["/bin/bash","-c","sleep 20 && PGPASSWORD=$POSTGRES_PASSWORD psql $POSTGRES_DB -U $POSTGRES_USER -c \'CREATE SCHEMA IF NOT EXISTS key;\'"]
    envFrom:
    - configMapRef:
        name: postgres-config
    volumeMounts:
    - mountPath: /var/lib/postgresql/data
      name: data
  dnsPolicy: ClusterFirst
  restartPolicy: Never
  volumes:
  - name: data
    emptyDir: {}
status: {}

answered May 19, 2020 at 7:54

hariK

3,10915 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Over a year ago

Trying this makes my Pod fail with the following error -> exited with 1: , message: "/var/run/postgresql:5432 - rejecting connections\n". Any ideas on fixing this?

Daniel Over a year ago

Actually, it was my bad, I tried replacing sleep 20 with pg_isready which doesn't seem to work, any idea why my approach doesn't work?

hdhruna · Accepted Answer · 2020-05-19 08:00:58Z

3

For your use-case, it is better to setup PostgreSQL using Helm charts, for e.g.:

helm install stable/postgresql \
--set global.postgresql.postgresqlDatabase=postgresdb \
--set global.postgresql.postgresqlUsername=postgresadmin \
--set global.postgresql.postgresqlPassword=admin123 \
--set global.postgresql.servicePort=5432 \
--set initdbScripts."init\.sql"="CREATE SCHEMA IF NOT EXISTS key;"

answered May 19, 2020 at 8:00

hdhruna

8647 silver badges15 bronze badges

Comments

Fritz Duchardt · Accepted Answer · 2020-05-19 07:14:32Z

0

The new DB schema can be create on the running Pod:

Find out the name of your Postges Pod: kubectl get po
Log into your Pod: kubectl exec -it [Name-of-Pod] -- sh
Copy your schema dump file to the Pod: kubectl cp [Name-of-File] [Name-of-Pod]:[Name-of-File]
Install schema with psql tool: psql -U username dbname < [Name-of-File]

answered May 19, 2020 at 7:14

Fritz Duchardt

12.1k5 gold badges48 silver badges68 bronze badges

2 Comments

Siddhanta Rath Over a year ago

just wanted to create a schema with config map or deployment file. In production, I won't be having access to do theses thing :(

Fritz Duchardt Over a year ago

In that case, you could create a config map containing your schema dump, mount that into the postgres Pod as a volume and tweak the postgres Pod's commands to first import the schema before starting the main process.

Collectives™ on Stack Overflow

How to deploy postgres with a new schema on kubernetes?

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related