Install PostgreSQL on OpenShift

Postgres

To deploy Postgres in OpenShift clusters, you need to use containers. For Postgres, there are several different images. The ‘Postgres’ image is the right one.

Dockerfile

The custom Dockerfile extends the Postgres image and sets access rights, especially to make sure that the container runs as non-root.

FROM postgres:12 
RUN mkdir temp
RUN groupadd non-root-postgres-group
RUN useradd non-root-postgres-user --group non-root-postgres-group
RUN chown -R non-root-postgres-user:non-root-postgres-group /temp
RUN chmod 777 /temp
USER non-root-postgres

Deployment yaml

To deploy the image to OpenShift, you obviously need additional yaml configuration.

containers:
- env
  - name: POSTGRES_DB
    value: postgres
  - name: POSTGRES_USER
    value: postgres
  - name: POSTGRES_PASSWORD
    value: postgres
  - name: PGDATA
    value: /temp/data
  image: image-registry.openshift-image-registry.svc:5000/postgres/non-root-postgres:latest
  imagePullPolicy: Always
  name: postgres
  ports:
  - containerPort: 5432
    protocol: TCP
  resources:
    limits:
      cpu: 60m
      memory: 512Mi
    requests:
      cpu: 30m
      memory: 128Mi
  restartPolicy: Always

Deploy the container, build the non-root image, and push it to a registry using these commands.

$ oc new-project postgres
$ oc apply -f ./postgres.yaml
$ oc expose svc/postgres

To access the database from other containers running in the same cluster use the name ‘postgres:postgres:5432` and the same configuration as above. To access the database from external processes use the OpenShift route.

Troubleshooting

In case you are unable to connect to the remote Openshift Cluster with three pods. An nginx pod that serves a web app, a .NET pod that serves a .NET web API, and a Postgres database pod.

You can connect the nginx pod to the .NET pod and have no problem making API requests. However, if you cannot get communication from the .NET pod to the Postgres pod in the Openshift cluster, then you need to troubleshoot. If you can curl the Postgres pod from the .NET pod's terminal in the Openshift web console and connect the Postgres pod itself (not the database) using the Postgres pod's service name, so DNS resolution of the Postgres pod is working.
Using Openshift's port forwarding to forward traffic from the local machine's localhost:5432 to my Postgres Pod's port 5432, we can connect to the Postgres database while running the .NET API locally using the connection string Host=localhost;Port=5432;Database=postgres;Username=suyash;Password=abcd1234

Run the following commands on the running Postgres pod to show the running config:

postgres -C listen_addresses
*

postgres -C hba_file
/var/lib/postgresql/data/mydata/pg_hba.conf

cat /var/lib/postgresql/data/mydata/pg_hba.conf

`sh
TYPE DATABASE USER ADDRESS METHOD

"local" is for Unix domain socket connections only
local all all trust
IPv4 local connections:
host all all 127.0.0.1/32 trust
IPv6 local connections:
host all all ::1/128 trust
Allow replication connections from localhost, by a user with the
replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
warning trust is enabled for all connections
see https://www.postgresql.org/docs/12/auth-trust.html
host all all all trust
`

postgres -C port
5432

The service definition's port name for my Postgres service is 'http' and not 'tcp'.
The name field is the protocol so it does matter what you put here.
The updated service yml for Postgres deployment is

yaml
apiVersion: v1
kind: Service
metadata:
labels:
app: postgres
service: postgres
name: postgres
spec:
ports:
- name: tcp
port: 5432
selector:
app: postgres


Ref: https://docs.openshift.com/container-platform/3.11/using_images/db_images/postgresql.html