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:
-
Download the necessary YAML file from the repository.
-
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¶
-
Configure the
mdsp-psql-all
database:Log in to the primary
mdsp-psql-all
database pod and execute the following SQL commands using thepsql
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;
-
Configure the
mdsp-psql-iot
database:-
Log in to the primary
mdsp-psql-iot
database pod and execute the following commands using thepsql
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. -
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
-
Change the password of the
_crunchypgbouncer
user, follow the steps:- Change the password stored in the
mdsp-psql-iot-pgbouncer
secret. - Restart the
pgbouncer
pod. - Change the password of the
_crunchypgbouncer
user in PostgreSQL:
alter user _crunchypgbouncer with password 'xxx';
- Change the password stored in the
-
Post Installation Validation¶
-
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)
-
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
-
Manual installation (deprecated method).
Use the following
kubectl apply
command to create a PostgreSQL cluster. Instead of using the deprecatedpgo 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 |