# Deploy postgresql on k8s

## 部屬 postgres
* sles15-sp5 安裝 postgresql 套件

$ sudo zypper in postgresql

$ psql --version
psql (PostgreSQL) 15.3

* postgresql yaml 使用 hostpath 儲存

apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  ports:
    - port: 5432
  selector:
    app: postgres
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  selector:
    matchLabels:
      app: postgres 
  template:
    metadata:
      labels:
        app: postgres
    spec:
      restartPolicy: Always
      containers:
      - image: postgres
        imagePullPolicy: IfNotPresent
        name: postgres
        ports:
        - containerPort: 5432
          name: postgres
        env:
        - name: POSTGRES_DB
          value: postgres
        - name: POSTGRES_USER
          value: postgres
        - name: POSTGRES_PASSWORD
          value: postgres123
        volumeMounts:
        - name: postgresql-volume
          mountPath: /var/lib/postgresql/data
      volumes:
        - name: postgresql-volume
          hostPath:
            path: /opt/postgresql

### 除錯

* 如果部屬 postgresql 遇到 `Bus error (core dumped)` 錯誤訊息可能是因為 kernal 參數造成的。

$ kubectl logs postgres-564dd6ff8d-l5r5p 
......
selecting default max_connections ... 20
selecting default shared_buffers ... 400kB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
Bus error (core dumped)
child process exited with exit code 135
initdb: removing contents of data directory "/var/lib/postgresql/data"
running bootstrap script ... 

* 需將 `vm.nr_hugepages` 參數設為 0

$ sudo sysctl -a | grep nr_hugepages
vm.nr_hugepages = 183
vm.nr_hugepages_mempolicy = 183

# 此設定為立即套用，但重開機會消失
$ sudo sysctl -w vm.nr_hugepages=0
vm.nr_hugepages = 0

$ sudo sysctl -a | grep nr_hugepages
vm.nr_hugepages = 0
vm.nr_hugepages_mempolicy = 0

### 參考資料
https://stackoverflow.com/questions/67941955/kubernetes-postgres-bus-error-core-dumped

## postgresql 語法
* 指定進入 postgres database

# 密碼是 postgres123
$ psql -h 10.43.138.145 -p 5432 -U postgres --password
Password:

* 建立 database

postgres=# CREATE DATABASE dbname;

* 查看目前有的 database

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+------------+------------+------------+-----------------+-----------------------
 dbname    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
           |          |          |            |            |            |                 | postgres=CTc/postgres
(4 rows)

* 建 customer 資料表

postgres=# CREATE TABLE customer (
    ID int NOT NULL,
    NAME varchar(100) NOT NULL,
    AGE int,
    ADDRESS varchar(255),
    SALARY DECIMAL(10,2),
    PRIMARY KEY (ID)
);

* 新增 7 筆資料

postgres=# INSERT INTO customer
VALUES (1,'Ramesh',32,'Ahmedabad','2000.00'),
(2,'Khilan',25,'Delhi','1500.00'),
(3,'kaushik',23,'kota','2000.00'),
(4,'Chaitali',25,'Mumbai','6500.00'),
(5,'Hardik',27,'Bhopal','8500.00'),
(6,'Komal',22,'MP','4500.00'),
(7,'Muffy',24,'Indore','10000.00');

* 顯示 customer 的所有資料

postgres=# select * from customer;
 id |   name   | age |  address  |  salary
----+----------+-----+-----------+----------
  1 | Ramesh   |  32 | Ahmedabad |  2000.00
  2 | Khilan   |  25 | Delhi     |  1500.00
  3 | kaushik  |  23 | kota      |  2000.00
  4 | Chaitali |  25 | Mumbai    |  6500.00
  5 | Hardik   |  27 | Bhopal    |  8500.00
  6 | Komal    |  22 | MP        |  4500.00
  7 | Muffy    |  24 | Indore    | 10000.00
(7 rows)