[應用] PostgreSQL

[應用] PostgreSQL === ###### tags: `K8s / app` ###### tags: `Kubernetes`, `k8s`, `app`, `PostgreSQL` [TOC] # SQL, psql commands ## SQL Commands ### `select current_database(), current_user, version();` ## psql Commands ### `-c '<sql-or-psql-command>'` - `-c` 是 `psql` 的選項：**執行後面那一段命令字串，執行完就離開**（非互動模式）。 - 這段命令可以是**SQL**（例如 `SELECT ...`），也可以是 **psql 的內建指令（meta-command）**（例如 `\dt`）。 ### `-c '\dt'` 是什麼意思 > `\dt`：display table，列出目前可見的**資料表（tables）**（不含 views 與 system tables）。 > `\dt+` 會顯示更多資訊（大小、描述）。 - ### gpt-5 (2025/11/07) * `-c` 是 `psql` 的選項：**執行後面那一個指令字串，執行完就離開**（非互動模式）。 * `'\dt'` 是 `psql` 的**內建指令（meta-command）**：**列出目前可見的資料表**（不含 system tables）。所以 `-c '\dt'` 的意思就是：啟動 `psql`、跑一次「列出表格」的指令，跑完就退出。 ### 小補充（常用變體）： * `-c '\dt+'`：列出表格並顯示更多資訊（大小、描述）。 * `-c '\dt public.*'`：只看 `public` schema 的表。 * `-c '\d schema.table'`：查看某張表的欄位與索引。 * 列 view/索引/序列：`\dv` / `\di` / `\ds` 也可以用純 SQL 等價查詢（非 meta-command）： ```bash psql ... -c "SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema');" ``` ### 用 slurm 使用者的密碼連上 postgresql 並印出當前 db 資訊 ```bash= PGPASSWORD=slurm_password psql \ -h postgresql.postgresql.svc.cluster.local \ -p 5432 \ -U slurm \ -d slurm_acct_db \ -c 'SELECT current_database(), current_user, version();' ``` - `-h`: host - `-p`: port - `-U`: user - `-d`: database - `-c`: command（一次性執行後離開） > 小提醒：在 shell 命令列直接夾帶密碼僅適合測試；正式環境可改用 K8s Secret 注入或 `.pgpass`。 --- --- # kubernetes ## PostgreSQL 安裝方式 > - 方案一(驗證過) (選用簡單方案即可) > - 方案二(尚未驗證) ### 方案一：使用 Bitnami PostgreSQL Helm Chart (推薦) Bitnami 提供穩定且易於維護的 PostgreSQL Helm Chart。 - ### 1. 新增 Bitnami Helm Repository ```bash helm repo add bitnami https://charts.bitnami.com/bitnami helm repo update ``` - ### 2. 建立 Namespace ```bash kubectl create namespace postgresql ``` - ### 3. 準備 values 檔案 > 請參考 [[SlinkyProject] values.yaml#L385-L402](https://github.com/SlinkyProject/slurm-operator/blob/main/helm/slurm/values.yaml#L385-L402) > ```yaml > accounting: > ... > # The storage configuration. > storageConfig: > # -- The name of the host where the database is running. > # Ref: https://slurm.schedmd.com/slurmdbd.conf.html#OPT_StorageHost > host: mariadb > # -- The port number to communicate with the database with. > # Ref: https://slurm.schedmd.com/slurmdbd.conf.html#OPT_StoragePort > port: 3306 > # -- The name of the database where records are written into. > # Ref: https://slurm.schedmd.com/slurmdbd.conf.html#OPT_StorageLoc > database: slurm_acct_db > # -- The name of the user used to connect to the database with. > # Ref: https://slurm.schedmd.com/slurmdbd.conf.html#OPT_StorageUser > username: slurm > # -- (secretKeyRef) The password used to connect to the database, from secret reference. > # Ref: https://slurm.schedmd.com/slurmdbd.conf.html#OPT_StoragePass > passwordKeyRef: > name: mariadb-password > key: password > ``` > --- 建立 `postgres-values.yaml`： ```yaml= global: postgresql: auth: username: "slurm" password: "slurm_password" database: "slurm_acct_db" postgresPassword: "admin_password" primary: persistence: enabled: true size: 10Gi storageClass: "" resources: limits: memory: 2Gi cpu: 1000m requests: memory: 512Mi cpu: 250m livenessProbe: enabled: true initialDelaySeconds: 30 periodSeconds: 10 timeoutSeconds: 5 failureThreshold: 6 readinessProbe: enabled: true initialDelaySeconds: 5 periodSeconds: 10 timeoutSeconds: 5 failureThreshold: 6 startupProbe: enabled: true initialDelaySeconds: 30 periodSeconds: 10 timeoutSeconds: 5 failureThreshold: 15 service: type: ClusterIP ports: postgresql: 5432 metrics: enabled: true serviceMonitor: enabled: false ``` #### 4. 安裝 PostgreSQL ```bash helm install postgresql bitnami/postgresql \ --namespace postgresql \ --values postgres-values.yaml ``` - :::spoiler 執行過程 ``` NAME: postgresql LAST DEPLOYED: Wed Nov 5 12:29:09 2025 NAMESPACE: postgresql STATUS: deployed REVISION: 1 TEST SUITE: None NOTES: CHART NAME: postgresql CHART VERSION: 18.0.15 APP VERSION: 18.0.0 ``` ⚠ WARNING: Since August 28th, 2025, only a limited subset of images/charts are available for free. Subscribe to Bitnami Secure Images to receive continued support and security updates. More info at https://bitnami.com and https://github.com/bitnami/containers/issues/83267 **Please be patient while the chart is being deployed** PostgreSQL can be accessed via port 5432 on the following DNS names from within your cluster: postgresql.postgresql.svc.cluster.local - Read/Write connection To get the password for "postgres" run: export POSTGRES_ADMIN_PASSWORD=$(kubectl get secret --namespace postgresql postgresql -o jsonpath="{.data.postgres-password}" | base64 -d) To get the password for "slurm" run: export POSTGRES_PASSWORD=$(kubectl get secret --namespace postgresql postgresql -o jsonpath="{.data.password}" | base64 -d) To connect to your database run the following command: kubectl run postgresql-client --rm --tty -i --restart='Never' --namespace postgresql --image registry-1.docker.io/bitnami/postgresql:latest --env="PGPASSWORD=$POSTGRES_PASSWORD" \ --command -- psql --host postgresql -U slurm -d slurm_acct_db -p 5432 > NOTE: If you access the container using bash, make sure that you execute "/opt/bitnami/scripts/postgresql/entrypoint.sh /bin/bash" in order to avoid the error "psql: local user with ID 1001} does not exist" To connect to your database from outside the cluster execute the following commands: kubectl port-forward --namespace postgresql svc/postgresql 5432:5432 & PGPASSWORD="$POSTGRES_PASSWORD" psql --host 127.0.0.1 -U slurm -d slurm_acct_db -p 5432 WARNING: The configured password will be ignored on new installation in case when previous PostgreSQL release was deleted through the helm command. In that case, old PVC will have an old password, and setting it through helm won't take effect. Deleting persistent volumes (PVs) will solve the issue. WARNING: Rolling tag detected (bitnami/postgresql:latest), please note that it is strongly recommended to avoid using rolling tags in a production environment. +info https://techdocs.broadcom.com/us/en/vmware-tanzu/application-catalog/tanzu-application-catalog/services/tac-doc/apps-tutorials-understand-rolling-tags-containers-index.html WARNING: Rolling tag detected (bitnami/os-shell:latest), please note that it is strongly recommended to avoid using rolling tags in a production environment. +info https://techdocs.broadcom.com/us/en/vmware-tanzu/application-catalog/tanzu-application-catalog/services/tac-doc/apps-tutorials-understand-rolling-tags-containers-index.html WARNING: There are "resources" sections in the chart not set. Using "resourcesPreset" is not recommended for production. For production installations, please set the following values according to your workload needs: - metrics.resources - readReplicas.resources +info https://kubernetes.io/docs/concepts/configuration/manage-resources-containers/ ::: #### 5. 等待 PostgreSQL 就緒 ```bash kubectl wait --for=condition=ready pod -l app.kubernetes.io/name=postgresql \ -n postgresql --timeout=600s ``` #### 6. 驗證安裝 ```bash kubectl get pods -n postgresql kubectl get svc -n postgresql ``` #### 7. 移除 PostgreSQL ``` helm -n postgresql uninstall postgresql ``` #### Troubleshooting - ### ImagePullBackOff Bitnami 宣布自 2025-08-28 起，Docker Hub 的大部分版本化影像不再免費提供，舊版本移往 `bitnamilegacy/*`（停止更新），僅提供少量 “latest” 作為開發用，或走付費的 Secure Images；因此舊的 `bitnami/*` tag 會出現 **manifest not found**。 > 補充：你安裝 chart 時 `NOTES` 內也會出現 2025-08-28 的警示文字。 --- **暫時解法**：把來源改到 `bitnamilegacy/*`，用仍存在的 tag： ```bash # 建議用 helm 升級（在同一個 namespace） helm upgrade --install postgresql bitnami/postgresql \ -n postgresql \ --set image.repository=bitnamilegacy/postgresql \ --set image.tag=17.6.0-debian-12-r4 \ --set metrics.enabled=true \ --set metrics.image.repository=bitnamilegacy/postgres-exporter \ --set metrics.image.tag=0.17.1-debian-12-r14 ``` > 說明：`bitnamilegacy/postgresql:17.6.0-debian-12-r4` 與 `bitnamilegacy/postgres-exporter:0.17.1-debian-12-r14` 目前可抓到；`-r16` 未必存在於 legacy。([Docker Hub](https://hub.docker.com/r/bitnamilegacy/postgresql/tags)) - ### `[ImagePullBackOff] Back-off pulling image "docker.io/bitnami/postgresql:17.6.0-debian-12-r4"` **解法**：`bitnami` -> `bitnamilegacy` ```bash # 直接測試節點能否拉到影像（containerd） sudo ctr -n k8s.io images pull docker.io/bitnamilegacy/postgresql:17.6.0-debian-12-r4 ``` - ### `[ImagePullBackOff] Back-off pulling image "docker.io/bitnami/postgres-exporter:0.17.1-debian-12-r16"` **解法**：`bitnami` -> `bitnamilegacy` ```bash # 直接測試節點能否拉到影像（containerd） sudo ctr -n k8s.io images pull quay.io/prometheuscommunity/postgres-exporter:v0.18.1 ``` --- ### 方案二：使用 CloudNativePG Operator CloudNativePG 是專為 Kubernetes 設計的 PostgreSQL Operator，提供更進階的功能如高可用性、備份恢復等。 - ### 1. 安裝 CloudNativePG Operator ```bash kubectl apply -f https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.23/releases/cnpg-1.23.0.yaml ``` - ### 2. 建立 PostgreSQL Cluster 建立 `postgres-cluster.yaml`： ```yaml= apiVersion: postgresql.cnpg.io/v1 kind: Cluster metadata: name: postgresql namespace: postgresql spec: instances: 1 postgresql: parameters: max_connections: "200" shared_buffers: "256MB" bootstrap: initdb: database: slurm_acct_db owner: slurm secret: name: postgresql-credentials storage: size: 10Gi storageClass: "" resources: requests: memory: "512Mi" cpu: "500m" limits: memory: "2Gi" cpu: "2" monitoring: enablePodMonitor: false ``` - ### 3. 建立 Credentials Secret ```bash kubectl create namespace postgresql kubectl create secret generic postgresql-credentials \ --namespace postgresql \ --from-literal=username=slurm \ --from-literal=password=slurm_password ``` - ### 4. 部署 Cluster ```bash kubectl apply -f postgres-cluster.yaml ``` --- --- ## 測試 PostgreSQL 連線與查詢 > 使用方案一驗證 ### 當前 PostgreSQL server 提供的資訊 | 屬性 | 屬性值 | | |-----|-----|-----| | **host** | `postgresql.postgresql.svc.cluster.local` | 叢集內的服務 DNS（ClusterIP），供 K8s 內部連線使用 | | **port** | `5432` | PostgreSQL 連接埠（預設 5432） | | **database** | `slurm_acct_db` | 目標資料庫名稱 | | **username** | `slurm` | 使用者帳號 | | **password** | `slurm_password` | 使用者密碼 | ### 方式 A：用一次性 psql 客戶端 Pod 直接測 > 不用搬 Secret，直接把密碼讀出來塞進環境變數。 1. 先把密碼讀到本機變數（Secret 在 `postgresql` ns）： ```bash PW=$(kubectl -n postgresql get secret postgresql \ -o jsonpath='{.data.password}' | base64 -d) echo $PW # 預期：PW=slurm_password ``` 2. 在 **default** ns 起一個一次性 Pod，立刻執行查詢： ```bash kubectl -n default run psql-test \ --image=postgres:16 \ --restart=Never \ --env="PGPASSWORD=$PW" \ --command -- psql \ -h postgresql.postgresql.svc.cluster.local \ -p 5432 \ -U slurm \ -d slurm_acct_db \ -c 'select current_database(), current_user, version();' ``` 3. **驗證結果** ```bash kubectl -n default get pod kubectl -n default logs pod/psql-test ``` - **執行結果** ``` $ kubectl -n default get pod NAME READY STATUS RESTARTS AGE psql-test 0/1 Completed 0 88s $ kubectl -n default logs pod/psql-test current_database | current_user | version ------------------+--------------+------------------------------------------------------------------------------ slurm_acct_db | slurm | PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit (1 row) ``` 4. （可選）清掉測試 Pod： ```bash kubectl -n default delete pod psql-test --ignore-not-found ``` 成功時你會看到目前資料庫、使用者與 PostgreSQL 版本。 --- ### 方式 B：先起一個「工具箱」Pod，再進去測 > 方便多次測試或跑 `pg_isready`、`nslookup` 等工具。 1. 起一個空轉的工具箱 Pod： ```bash kubectl -n default run toolbox --image=postgres:16 --restart=Never -- sleep 3600 ``` 2. 先把密碼讀到本機變數（Secret 在 `postgresql` ns）： ```bash PW=$(kubectl -n postgresql get secret postgresql \ -o jsonpath='{.data.password}' | base64 -d) echo $PW # 預期：PW=slurm_password ``` 3. 進去執行測試（把上面取到的 `$PW` 帶進去）： ```bash kubectl -n default exec -it toolbox -- bash -lc \ 'PGPASSWORD='"$PW"' psql -h postgresql.postgresql.svc.cluster.local -p 5432 -U slurm -d slurm_acct_db -c "select current_database(), current_user, version()"' ``` - **執行結果** ``` current_database | current_user | version ------------------+--------------+------------------------------------------------------------------------------ slurm_acct_db | slurm | PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit (1 row) ``` 4. 也可做純連線健康檢查： ```bash kubectl -n default exec toolbox -- pg_isready -h postgresql.postgresql.svc.cluster.local -p 5432 -U slurm -d slurm_acct_db ``` - **執行結果** ``` postgresql.postgresql.svc.cluster.local:5432 - accepting connections ``` 4. 測完刪掉： ```bash kubectl -n default delete pod toolbox ``` --- ### 方式 C：本機 psql + 轉址（如果你偏好在自己電腦上測） ```bash kubectl -n postgresql port-forward svc/postgresql 5432:5432 # 另開一個終端 PGPASSWORD="$PW" psql -h 127.0.0.1 -p 5432 -U slurm -d slurm_acct_db -c 'select 1' ``` --- ### 注意事項 * **Secrets 是有命名空間的**：`default` 裡的 Pod 不能直接掛載 `postgresql` 命名空間的 Secret；上面做法是把密碼讀出後用環境變數傳給一次性 Pod。若你要長期跑的 Pod 讀 Secret，請把 Secret 複製一份到 `default`（或用 External Secrets 同步），再掛載使用。 * 若有 **NetworkPolicy**，請確認 `default → postgresql` 的 5432/TCP 沒被擋。 * 若你實際的使用者/資料庫不是 `slurm`/`slurm_acct_db`，把指令中的 `-U` 與 `-d` 改掉即可。 {%hackmd vaaMgNRPS4KGJDSFG0ZE0w %}