K
Q

User and database are not initialised though environment variables while deploying postgres in kubernetes

4/15/2020

When I am trying to deploy postgres over kubernetes, I have populated all the required environment variables such as POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DATABASE, etc. through the creation of configmap and secret files. Container is successfully deployed and I can open into its terminal through the 'kubectl exec' command. When I run the 'env' command, it is seen that all the environment variables supplied during deployment are successfully set accordingly. But when I try to run :

psql -U testuser -d testdb

It gives two types of errors:

psql: could not connect to server: FATAL: role 'testuser' does not exist

psql: could not connect to server: FATAL: database 'testdb' does not exist

After doing a lot of research, I found that even after setting the environment variables, the user and database do not get initialised. Therefore, I created an init.sql file and added it to docker-entrypoint-initdb.d to build a custom image, push it to docker hub and deploy postgres through that image in kubernetes.

init.sql file content:

CREATE USER testuser WITH SUPERUSER PASSWORD test;

CREATE DATABASE testdb;

GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;

Dockerfile content:

FROM postgres:latest

ADD init.sql /docker-entrypoint-initdb.d/

EXPOSE 5432

Still, I get the same error. user and database are not getting initialised... please help me out with this

UPDATE:

Logs:

PostgreSQL Database directory appears to contain a database; Skipping initialization

2020-04-15 09:50:06.855 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-04-15 09:50:06.855 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-04-15 09:50:06.855 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-04-15 09:50:07.000 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-04-15 09:50:07.204 UTC [26] LOG:  database system was interrupted; last known up at 2020-04-15 09:50:02 UTC
2020-04-15 09:50:07.781 UTC [26] LOG:  database system was not properly shut down; automatic recovery in progress
2020-04-15 09:50:07.815 UTC [26] LOG:  invalid record length at 0/16453B0: wanted 24, got 0
2020-04-15 09:50:07.815 UTC [26] LOG:  redo is not required
2020-04-15 09:50:08.034 UTC [1] LOG:  database system is ready to accept connections

Here, it is already mentioned 'Skipping initialisation'

-- selina
database
database-connection
docker
kubernetes
postgresql

Similar Questions

Swagger UI try it out internally in kubernetes
How can I annotate two different Cloud Armor BackendConfigs to of a service
How Kubernetes balances the load to the eks worker node
What is the role of an external Load Balancer if we are using nginx ingress controller?

1 Answer

4/15/2020

can you try with this shell script ? file name : init-user-db.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER docker;
    CREATE DATABASE docker;
    GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

Docker file something like this

FROM postgres:latest

ADD init-user-db.sh /docker-entrypoint-initdb.d/init-user-db.sh

EXPOSE 5432

EDIT : 1

apiVersion: apps/v1
    kind: StatefulSet
    metadata:
    spec:
      podManagementPolicy: OrderedReady
      replicas: 1
      revisionHistoryLimit: 10
      selector:
        matchLabels:
          app: postgres
      serviceName: postgres
      template:
        metadata:
          creationTimestamp: null
          labels:
            app: postgres
        spec:
          containers:
          - env:
            - name: POSTGRES_USER
              value: root
            - name: POSTGRES_PASSWORD
              value: <Password>
            - name: POSTGRES_DB
              value: <DB name>
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
            image: postgres:9.5
            imagePullPolicy: IfNotPresent
            name: postgres
            ports:
            - containerPort: 5432
              protocol: TCP
            resources: {}
            terminationMessagePath: /dev/termination-log
            terminationMessagePolicy: File
            volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgres-data
              subPath: pgdata
          dnsPolicy: ClusterFirst
          restartPolicy: Always
          schedulerName: default-scheduler
          securityContext: {}
          terminationGracePeriodSeconds: 60
      updateStrategy:
        rollingUpdate:
          partition: 0
        type: RollingUpdate
      volumeClaimTemplates:
      - metadata:
          creationTimestamp: null
          name: postgres-data
        spec:
          accessModes:
          - ReadWriteOnce
          resources:
            requests:
              storage: 3Gi
          volumeMode: Filesystem
        status:
          phase: Pending
-- Harsh Manvar
Source: StackOverflow