Skip to content

Installing PostgreSQL

This section outlines the setup and deployment of a PostgreSQL cluster within an existing Rancher-based Kubernetes environment. The goal is to replace HBase with PostgreSQL for IoT workloads and upgrade the existing PostgreSQL for general workloads.

Hardware Design

Note

Three additional worker nodes are added to the existing Rancher-Box Kubernetes Cluster. The capacity of a work node is 3x. Each worker node is provisioned with 16 CPUs and 32 GB of memory.
- 1 TiB for each Persistent Volume (PV) mdsp-psql-iot (replaces HBase).
- 225 GiB for mdsp-psql-all (replaces the existing PostgreSQL configuration).

The Postgres cluster is scheduled with 2TB of storage, with the following requirements:

Name Status Volume Capacity Access Modes Storage Class Age
mdsp-psql-all-db-dm9k-pgdata Bound pvc-c1db7ece-1967-4498-8983-9fc6d12ecf62 225Gi RWO longhorn-postgresql 38h
mdsp-psql-all-db-dm9k-pgwal Bound pvc-25e98a8a-7bc2-4b33-b90f-e90fa889ff1f 150Gi RWO longhorn-postgresql 38h
mdsp-psql-all-db-mnf9-pgdata Bound pvc-3463741e-f85a-49c3-94b7-5d6b1ecace7d 225Gi RWO longhorn-postgresql 38h
mdsp-psql-all-db-mnf9-pgwal Bound pvc-cfa25bce-3820-4c82-bf29-c277a7ab024d 150Gi RWO longhorn-postgresql 38h
mdsp-psql-all-db-q267-pgdata Bound pvc-bf2e046e-084f-4337-b587-63bfd5d0ad6b 225Gi RWO longhorn-postgresql 38h
mdsp-psql-all-db-q267-pgwal Bound pvc-326f6fa5-03c3-4052-a70f-719b3d943470 150Gi RWO longhorn-postgresql 38h
mdsp-psql-all-pgadmin Bound pvc-2baa4c82-525a-4ae5-a341-047b46a6479e 1Gi RWO longhorn-postgresql 38h
mdsp-psql-all-repo1 Bound pvc-1cf65c93-a5a1-444b-bbbd-c0113dc47386 225Gi RWO longhorn-postgresql 38h
mdsp-psql-iot-db-6nt5-pgdata Bound pvc-41b0e693-fc05-42c2-a28d-a4c3b4a0be3e 1Ti RWO longhorn-postgresql 39h
mdsp-psql-iot-db-6nt5-pgwal Bound pvc-d1732d52-79c2-489f-85f7-2745a7a1b3e1 100Gi RWO longhorn-postgresql 39h
mdsp-psql-iot-db-pgk5-pgdata Bound pvc-a60636fd-0194-4e68-a2a8-fea06e8c1ebf 1Ti RWO longhorn-postgresql 39h
mdsp-psql-iot-db-pgk5-pgwal Bound pvc-0894c67f-9ab4-45ab-ae85-b30b760a1e10 100Gi RWO longhorn-postgresql 39h
mdsp-psql-iot-db-z6v9-pgdata Bound pvc-d7997069-5aa9-476f-b193-6ac76e175c73 1Ti RWO longhorn-postgresql 39h
mdsp-psql-iot-db-z6v9-pgwal Bound pvc-0f38b96d-2e0f-4f28-b4ac-50841b9809ff 100Gi RWO longhorn-postgresql 39h
mdsp-psql-iot-pgadmin Bound pvc-3e6e9163-69f6-408e-b831-7e8a74275d09 1Gi RWO longhorn-postgresql 39h
mdsp-psql-iot-repo1 Bound pvc-6730d5a8-eef8-4063-b9ec-c9b60a6c983a 1Ti RWO longhorn-postgresql 39h

Note

The Write-Ahead Log (WAL) size is configured to 150 Gi on the OSS box, since 100 Gi is insufficient for the Rancher box.

Software Configuration

The PostgreSQL Operator (PGO) version 5.3.0 is compatible with PostgreSQL and automates cluster management tasks like deployment. For more information, refer to the Postgres Operator 5.3.0.

Prerequisites

Postgres Operator (PGO) from Crunchy Data is tested and supported on the Kubernetes versions 1.22 to 1.25.

Installing PostgreSQL with ArgoCD

Automated Deployment: An Argo CD configuration is prepared for automated installation using a predefined repository: Project internal repository.

Installing PostgreSQL manually (Optional)

Note

Manual installation is optional as Argo CD is already configured for deployment.

To install PostgreSQL manually, follow the steps:

  1. Download the necessary YAML file from the repository.

  2. Install the key components kubectl by using the following command line:

    • Custom Resource Definitions (CRD):

      kubectl apply --server-side -k install/crd
      
    • NameSpace

      kubectl create ns pgo  # in case of 1st installation
      
    • Operator

      kubectl apply -f pgo-operator-5.3.0.yaml
      
    • Pgocluster all

      kubectl apply -f pgo-cluster-all-5.3.0.yaml
      
    • Pgocluster iot

      kubectl apply -f pgo-cluster-iot-5.3.0.yaml
      

Post-Configuration

  1. Configure the mdsp-psql-all database:

    Log in to the primary mdsp-psql-all database pod and execute the following SQL commands using the psql CLI:

     alter user postgres with password 'Define your password here';   # update postgres role named postgres for mdsp-psql-all cluster
     select * from pg_hba_file_rules;
     select setting from pg_settings where name like '%hba%';
     copy hba from '/pgdata/pg14/pg_hba.conf';
     select * from hba where lines !~ '^#' and lines !~ '^$';
     insert into hba (lines) values ('host all "postgres" 0.0.0.0/0 md5');
     insert into hba (lines) values ('hostssl all all all md5');
     copy hba to '/pgdata/pg14/pg_hba.conf';
     select pg_read_file('pg_hba.conf');
     ---
     alter user postgres with password '***';
     ### pgadmin Login
     ALTER ROLE "mdsp-psql-all"   
     LOGIN
     SUPERUSER;
    
  2. Configure the mdsp-psql-iot database:

    1. Log in to the primary mdsp-psql-iot database pod and execute the following commands using the psql CLI:

          ALTER ROLE _crunchypgbouncer
          LOGIN
          SUPERUSER
          CREATEDB
          CREATEROLE
          INHERIT
          REPLICATION;
            create database _crunchypgbouncer;
            ### pgadmin Login
            ALTER ROLE "mdsp-psql-iot"   
                LOGIN
                SUPERUSER;
      

      Note: The user _crunchypgbouncer will be automatically created once the cluster setup is completed successfully.

    2. Retrieve the default password of _crunchypgbouncer using the following command:

      -bash-4.2$ kubectl edit secret mdsp-psql-iot-pgbouncer
      apiVersion: v1
      data:
        pgbouncer-password: xxx
      
    3. Change the password of the _crunchypgbouncer user, follow the steps:

      1. Change the password stored in the mdsp-psql-iot-pgbouncer secret.
      2. Restart the pgbouncer pod.
      3. Change the password of the _crunchypgbouncer user in PostgreSQL:
      alter user _crunchypgbouncer with password 'xxx';
      

Post Installation Validation

  1. Check the PostgreSQL parameters using the following command:

    postgres=# show max_connections;
    max_connections
    -----------------
    2500
    (1 row)
    
    postgres=# SELECT name,setting,unit,current_setting(name) FROM pg_settings WHERE name='shared_buffers';   # 2GB for iot cluster
    name      | setting | unit | current_setting
    ----------------+---------+------+-----------------
    shared_buffers | 524288  | 8kB  | 4GB
    (1 row)
    
  2. To edit the pgo-cluster-all-5.3.0.yaml configuration file, use the following command: vim pgo-cluster-all-5.3.0.yaml

      apiVersion: postgres-operator.crunchydata.com/v1beta1
      kind: PostgresCluster
      metadata:
        name: mdsp-psql-all
        namespace: pgo
        labels:
        crunchy-pgha-scope: mdsp-psql-all
        deployment-name: mdsp-psql-all
        name: mdsp-psql-all
        namespace: pgo
        pg-cluster: mdsp-psql-all
        pgo-version: 5.3.0
        pgouser: admin
        annotations:
        current-primary: postgres-operator
      spec:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.6-2
        postgresVersion: 14
        instances:
        - name: db
          replicas: 3
          minAvailable: 1
          dataVolumeClaimSpec:
          accessModes:
          - "ReadWriteOnce"
          resources:
            requests:
            storage: 225Gi
          storageClassName: longhorn-postgresql
          walVolumeClaimSpec:
          accessModes:
          - "ReadWriteOnce"
          resources:
            requests:
            storage: 150Gi
          storageClassName: longhorn-postgresql
          tolerations:
          - effect: NoSchedule
          key: domain
          operator: Equal
          value: postgresql
          affinity:
          nodeAffinity:
            requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
            - matchExpressions:
              - key: postgresql
              operator: In
              values:
              - "true"
          podAntiAffinity:
            requiredDuringSchedulingIgnoredDuringExecution:
            - topologyKey: kubernetes.io/hostname
            labelSelector:
              matchLabels:
              postgres-operator.crunchydata.com/cluster: mdsp-psql-all
              postgres-operator.crunchydata.com/instance-set: db
        proxy:
        pgBouncer:
          replicas: 1
          minAvailable: 1
          config:
          global:
            default_pool_size: "100"
            max_client_conn: "1000"
            pool_mode: session
          service:
          metadata:
            annotations:
            my-annotation: value1
            labels:
            my-label: value2
          type: NodePort
          nodePort: 32003
          tolerations:
          - effect: NoSchedule
          key: domain
          operator: Equal
          value: postgresql
          affinity:
          nodeAffinity:
            requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
            - matchExpressions:
              - key: postgresql
              operator: In
              values:
              - "true"
        patroni:
        dynamicConfiguration:
          synchronous_mode: true
          postgresql:
          parameters:
            synchronous_commit: "on"
            shared_buffers: 4GB
            max_connections: 2500
            default_pool_size: 100
        backups:
        pgbackrest:
          image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-2
          repos:
          - name: repo1
          volume:
            volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
              storage: 225Gi
            storageClassName: longhorn-postgresql
          repoHost:
          tolerations:
          - effect: NoSchedule
            key: domain
            operator: Equal
            value: postgresql
          jobs:
          tolerations:
          - effect: NoSchedule
            key: domain
            operator: Equal
            value: postgresql
        userInterface:
        pgAdmin:
          service:
          metadata:
            annotations:
            my-annotation: value1
            labels:
            my-label: value2
          type: NodePort
          nodePort: 32001
          image: registry.developers.crunchydata.com/crunchydata/crunchy-pgadmin4:ubi8-4.30-4
          dataVolumeClaimSpec:
          accessModes:
          - "ReadWriteOnce"
          resources:
            requests:
            storage: 1Gi
          storageClassName: longhorn-postgresql
          tolerations:
          - effect: NoSchedule
          key: domain
          operator: Equal
          value: postgresql
        service:
        metadata:
          annotations:
          my-annotation: value1
          labels:
          my-label: value2
        type: NodePort
        nodePort: 32000
    
  3. Manual installation (deprecated method).

    Use the following kubectl apply command to create a PostgreSQL cluster. Instead of using the deprecated pgo create cluster command.

    pgo create cluster hippo  -n pgo  --replica-count=2 \
                                      --node-affinity-type=required \
                                      --sync-replication \
                                      --node-label=postgresql=true \
                                      --toleration=domain=postgresql:NoSchedule
    

Note

The pgo client is not available starting from version 5.0.0. Therefore, use kubectl apply command.

PostgreSQL Cluster Validation

Use the following command to validate the PostgreSQL cluster deployment:

$ kubectl get pods -l postgres-operator.crunchydata.com/cluster=mdsp-psql-all \
>     -L postgres-operator.crunchydata.com/instance \
>     -L postgres-operator.crunchydata.com/role
NAME                                       READY   STATUS      RESTARTS   AGE   INSTANCE                ROLE
mdsp-psql-all-backup-z8l8-d6l8w            0/1     Completed   0          39h
mdsp-psql-all-db-dm9k-0                    4/4     Running     0          23h   mdsp-psql-all-db-dm9k   master
mdsp-psql-all-db-mnf9-0                    4/4     Running     0          23h   mdsp-psql-all-db-mnf9   replica
mdsp-psql-all-db-q267-0                    4/4     Running     0          23h   mdsp-psql-all-db-q267   replica
mdsp-psql-all-pgadmin-0                    1/1     Running     0          39h                           pgadmin
mdsp-psql-all-pgbouncer-64c6557cd8-725wl   2/2     Running     0          39h                           pgbouncer
mdsp-psql-all-repo-host-0                  2/2     Running     0          39h

Note

If the file was not found, either it does not exist or you do not have the necessary access permissions.

Rancher box PostgreSQL configuration comparison are as follows:

Old postgresql for all New postgresql for all New postgresql for IOT(replace hbase) Custom postgresql for IOT(replace hbase) Custom postgresql for all(replace old posgresql)
pg_sql_host/lpc_pg_sql_host Not ready on rancher box poc_pg_sql_host poc_pg_sql_host lpc_pg_sql_host
pg_sql_password/lpc_pg_sql_password Not ready on rancher box poc_pg_sql_password poc_pg_sql_password lpc_pg_sql_password
pg_sql_password_mb Not ready on rancher box poc_pg_sql_port poc_pg_sql_port lpc_pg_sql_port
pg_sql_port/lpc_pg_sql_port Not ready on rancher box poc_pg_sql_user poc_pg_sql_user lpc_pg_sql_user
pg_sql_user/lpc_pg_sql_user Not ready on rancher box
pg_sql_user_mb Not ready on rancher box

Last update: January 31, 2025