Skip to main content

Postgresql

Deploy Postgres​

Creating configmaps​

note

The files we are creating below can be found combined here. This yaml is purely for reference as are the examples below and may need to be edited to suit your environment.

Let's create a configmap for the PGSQL database to use:

postgres_configmap.yaml
kind: ConfigMap
apiVersion: v1
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  POSTGRES_DB: defaultdb
  POSTGRES_USER: admin
  POSTGRES_PASSWORD: p@ssw0rd
  PGDATA: /var/lib/postgresql/data/pgdata

Create PVCs​

Now lets create a pair of ebs storage device PVCs for the database data and tablespaces.

postgres-pvc.yaml
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-data
  namespace: filenet
  labels:
    app: postgres
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 50Gi
  storageClassName: ebs-gp3-sc
  volumeMode: Filesystem
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-tablespaces
  labels:
    app: postgres
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 5Gi
  storageClassName: ebs-gp3-sc
  volumeMode: Filesystem

Create deployment​

Create a deployment for postgres. As per the IBM recommendations we are setting the following args for postgres to 500:

  • max_prepared_transactions=500
  • max_connections=500
postgres-deploy.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres  # Sets Deployment name
  namespace: filenet
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      securityContext:
        runAsUser: 2000
        runAsGroup: 2000
        fsGroup: 65536
      containers:
        - name: postgres
          args:
            - '-c'
            - max_prepared_transactions=500
            - '-c'
            - max_connections=500
          image: postgres:latest # Sets Image
          imagePullPolicy: "IfNotPresent"
          ports:
            - containerPort: 5432  # Exposes container port
          envFrom:
            - configMapRef:
                name: postgres-config
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgres-data
            - mountPath: /pgsqldata
              name: postgres-tablespaces
      volumes:
        - name: postgresdb
          persistentVolumeClaim:
            claimName: postgres-data
        - name: postgres-tablespaces
          persistentVolumeClaim:
            claimName: postgres-tablespaces

Create the service​

Create the service for postgres

postgres-service.yaml
kind: Service
apiVersion: v1
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  ports:
    - name: pgsql-port
      protocol: TCP
      port: 5432
      targetPort: 5432
  type: NodePort
  selector:
    app: postgres

Apply Yaml files to cluster​

These yaml files are not set to any specific namespace, so make sure you've set your kubectl context accordingly to the namespace you want to deploy them in. As a default, we should have set our namespace context to filenet.

Now apply the above yaml files to the cluster:

kubectl apply -f postgres_configmap.yaml
kubectl apply -f postgres-pvc.yaml
kubectl apply -f postgres-deploy.yaml
kubectl apply -f postgres-service.yaml

Verify the postgres default database we configured above is up. You can get the pod name from kubectl get pods

kubectl exec -it postgres-POD-ID -- psql -h localhost -U admin --password -p 5432 defaultdb

Prepare database table spaces and ceuser​

Retrieve the postgres pod id with this command:

kubectl get pods | grep postgres

postgres-759fd876ff-d5fxd                     1/1     Running   0          6d10h

Connect to the postgres pod and create the tablespace directories for all databases you plan to create.

kubectl exec -it postgres-759fd876ff-d5fxd -- mkdir /pgsqldata/osdb /pgsqldata/gcddb /pgsqldata/icndb
kubectl exec -it postgres-759fd876ff-d5fxd -- chmod 700 /pgsqldata/osdb /pgsqldata/gcddb /pgsqldata/icndb

Connect to defaultdb. Our password will be p@ssw0rd.

kubectl exec -it postgres-759fd876ff-d5fxd -- psql -h localhost -U admin --password -p 5432 defaultdb

Create the ceuser

create role ceuser login password 'p@ssw0rd';

Create the databases​

For this deployment, we will be creating the following databases:

  • gcddb
  • icndb

Initial Object Store database

  • osdb

At this point you should still be connected to defaultdb.

Create the GCD database. When you run the \connect command, it will query you for the password. It will still be p@ssw0rd.

With these commands, we will be creating a new database, setting the owner to ceuser, locking it down from public and then creating the tablespace for that database to use the directories we created above. These tablespaces will live on a separate PVC.

CREATE DATABASE gcddb OWNER ceuser TEMPLATE template0 ENCODING UTF8;
GRANT ALL ON DATABASE gcddb TO ceuser;
REVOKE CONNECT ON DATABASE gcddb FROM public;
\connect gcddb
CREATE TABLESPACE gcddb_tbs OWNER ceuser LOCATION '/pgsqldata/gcddb';
GRANT CREATE ON TABLESPACE gcddb_tbs TO ceuser;

Create the ICN database. When you run the \connect command, it will query you for the password. It will still be p@ssw0rd.

CREATE DATABASE icndb OWNER ceuser TEMPLATE template0 ENCODING UTF8;
GRANT ALL ON DATABASE icndb TO ceuser;
REVOKE CONNECT ON DATABASE gcddb FROM public;
\connect icndb
CREATE TABLESPACE icndb_tbs OWNER ceuser LOCATION '/pgsqldata/icndb';
GRANT CREATE ON TABLESPACE icndb_tbs TO ceuser;

Create the Object Store database. When you run the \connect command, it will query you for the password. It will still be p@ssw0rd.

CREATE DATABASE osdb OWNER ceuser TEMPLATE template0 ENCODING UTF8 ;
GRANT ALL ON DATABASE osdb TO ceuser;
REVOKE CONNECT ON DATABASE osdb FROM public;
\connect osdb
CREATE TABLESPACE osdb_tbs OWNER ceuser LOCATION '/pgsqldata/osdb';
GRANT CREATE ON TABLESPACE osdb_tbs TO ceuser;

IER Databases and Object Stores​

For IER support, we will be creating the following databases:

  • fposdb - File Plan Object Store
  • rosdb - Record Object Store

In this instance we will also be creating two extra table spaces for rosdb for the indexarea and workflows.

Create the tablespace directories​

Retrieve the postgres pod id with this command:

kubectl get pods | grep postgres

postgres-759fd876ff-d5fxd                     1/1     Running   0          6d10h

Connect to the postgres pod and create the tablespace directories for fposdb and rosdb. This will also include the paths for the indexarea and workflows for rosdb.

kubectl exec -it postgres-759fd876ff-d5fxd -- mkdir /pgsqldata/fposdb /pgsqldata/rosdb /pgsqldata/rosdbwf /pgsqldata/rosdbidx 
kubectl exec -it postgres-759fd876ff-d5fxd -- chmod 700 /pgsqldata/fposdb /pgsqldata/rosdb /pgsqldata/rosdbwf /pgsqldata/rosdbidx

Create the IER object store databases​

Connect to defaultdb. Our password will be p@ssw0rd.

kubectl exec -it postgres-759fd876ff-d5fxd -- psql -h localhost -U admin --password -p 5432 defaultdb

Create the databases. Each \connect statement will require the p@ssw0rd password.

CREATE DATABASE fposdb OWNER ceuser TEMPLATE template0 ENCODING UTF8;
GRANT ALL ON DATABASE fposdb TO ceuser;
REVOKE CONNECT ON DATABASE fposdb FROM public;

\connect fposdb
CREATE TABLESPACE fposdb_tbs OWNER ceuser LOCATION '/pgsqldata/fposdb';
GRANT CREATE ON TABLESPACE fposdb_tbs TO ceuser;

CREATE DATABASE rosdb OWNER ceuser TEMPLATE template0 ENCODING UTF8;
GRANT ALL ON DATABASE rosdb TO ceuser;
REVOKE CONNECT ON DATABASE rosdb FROM public;

\connect rosdb
CREATE TABLESPACE rosdb_tbs OWNER ceuser LOCATION '/pgsqldata/rosdb';
GRANT CREATE ON TABLESPACE rosdb_tbs TO ceuser;

We will also now create the tablespaces in rosdb for the index area and workflows

CREATE TABLESPACE rosdbwf_tbs OWNER ceuser LOCATION '/pgsqldata/rosdbwf';
CREATE TABLESPACE rosdbidx_tbs OWNER ceuser LOCATION '/pgsqldata/rosdbidx';
GRANT CREATE ON TABLESPACE rosdbwf_tbs TO ceuser;
GRANT CREATE ON TABLESPACE rosdbidx_tbs TO ceuser;

Appendix​

Adding Other Object Store Databases​

note

This presumes you already have completed the above steps

Determine how many databases we need to create. In our example below we will create the following:

Extra object stores

  • os2db
  • os3db

Creating the tablespace directories in postgres pod​

Determine the name of your postgres pod by first making sure we're in the right namespace:

kubectl config set-context --current --namespace=filenet

And then pulling a list of pods

kubectl get pods | grep postgres
postgres-759fd876ff-d5fxd                     1/1     Running   0          7d22h

Now create the tablespace folders on the pod. These will be living in the PVC storage:

kubectl exec -it postgres-759fd876ff-d5fxd -- mkdir /pgsqldata/os2db /pgsqldata/os3db
kubectl exec -it postgres-759fd876ff-d5fxd -- chmod 700 /pgsqldata/os2db /pgsqldata/os3db

Create the databases​

Connect to defaultdb. Our password will be p@ssw0rd.

kubectl exec -it postgres-759fd876ff-d5fxd -- psql -h localhost -U admin --password -p 5432 defaultdb

Now create the databases

Create each database listed above. When you run the \connect command, it will query you for the password. It will still be p@ssw0rd.

With these commands, we will be creating each database, setting the owner to ceuser, locking it down from public and then creating the tablespace for that database to use the directories we created above. These tablespaces will live on a separate PVC.

CREATE DATABASE os2db OWNER ceuser TEMPLATE template0 ENCODING UTF8;
GRANT ALL ON DATABASE os2db TO ceuser;
REVOKE CONNECT ON DATABASE os2db FROM public;

\connect os2db
CREATE TABLESPACE os2db_tbs OWNER ceuser LOCATION '/pgsqldata/os2db';
GRANT CREATE ON TABLESPACE os2db_tbs TO ceuser;

CREATE DATABASE os3db OWNER ceuser TEMPLATE template0 ENCODING UTF8;
GRANT ALL ON DATABASE os3db TO ceuser;
REVOKE CONNECT ON DATABASE os3db FROM public;

\connect os3db
CREATE TABLESPACE os3db_tbs OWNER ceuser LOCATION '/pgsqldata/os3db';
GRANT CREATE ON TABLESPACE os3db_tbs TO ceuser;