## pg
建立表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入資料

INSERT INTO users (username, email) VALUES ('user1', '[e-mail]');
INSERT INTO users (username, email) VALUES ('user2', '[e-mail]');
INSERT INTO products (name, price, description) VALUES ('Product 1', 10.99, 'Description of Product 1');
INSERT INTO products (name, price, description) VALUES ('Product 2', 19.99, 'Description of Product 2');

查詢資料

SELECT * FROM users;

SELECT * FROM products;

## pgvector
查詢

select * from pg_extension;

建立extension

CREATE EXTENSION vector;

建立表

CREATE TABLE items (
  id bigserial PRIMARY KEY, 
  item text, 
  embedding vector(2)
);

插入資料

INSERT INTO
  items (item, embedding)
VALUES
  ('苹果', '[1, 1]'),
  ('香蕉', '[1.2, 0.8]'),
  ('猫', '[6, 0.4]');

查詢資料

SELECT * FROM items;

SELECT
  item,
  1 - (embedding <=> '[1.2, 0.8]') AS cosine_similarity
FROM
  items
ORDER BY
  cosine_similarity DESC;


## PG 升級研究

我們參考以下兩篇文章內容：
* https://github.com/sorintlab/stolon/blob/master/examples/kubernetes/postgresql_upgrade.md
* https://github.com/tianon/docker-postgres-upgrade/tree/6cc4d9314bf68563dafa1b3e1b64d068b619488e/10-to-13

### 實驗步驟

1. 建立10版的PG，連線進入，建立表格，填寫資料。
2. 紀錄pv,pvc

persistentvolume/pvc-a38ca27e-aed1-4848-8c1a-28b6388c795f
persistentvolumeclaim/data-stolon-keeper-0

3. 備份、刪掉 statefulset

kubectl -n achilles get statefulset.apps/stolon-keeper -o yaml > stolon-keeper.yaml

kubectl -n achilles delete statefulset.apps/stolon-keeper

4. 撰寫 stolon-upgrade.yaml

vi stolon-upgrade.yaml

kind: Pod
apiVersion: v1
metadata:
  name: stolon-upgrade
spec:
  volumes:
    - name: data-stolon-keeper-0
      persistentVolumeClaim:
       claimName: data-stolon-keeper-0
  containers:
    - name: stolon-upgrade
      args:
      - bash
      stdin: true
      tty: true
      image: harbor.arfa.wise-paas.com/database/postgres-upgrade:10-to-13
      volumeMounts:
      - mountPath: "/stolon-data"
        name: data-stolon-keeper-0
      resources:
        limits:
          cpu: "1"
          ephemeral-storage: 256M
          memory: 1Gi
        requests:
          cpu: "1"
          ephemeral-storage: 256M
          memory: 1Gi

5. 建立 pod

kubectl -n achilles create -f stolon-upgrade.yaml

6. 啟動完成後，連進去

kubectl -n achilles attach -it stolon-upgrade

7. 建立user，切換成postgres

useradd --uid 1000 postgres

#若已存在，需要變更uid 
usermod -u 1000 postgres

gosu postgres bash

8. 建立新資料夾，複製，啟動pg_upgrade

PGDATA=/stolon-data/postgres-new initdb

cp /stolon-data/postgres-new/pg_hba.conf /stolon-data/postgres/pg_hba.conf

cd /tmp
pg_upgrade -d /stolon-data/postgres -D /stolon-data/postgres-new --link

9. 上面步驟完成後，搬回來

rm -fr /stolon-data/postgres
mv /stolon-data/postgres-new/ /stolon-data/postgres

10. 退出pod，刪除stolon-upgrade

kubectl -n achilles delete -f stolon-upgrade.yaml

11. 使用新的鏡像新建 stolon-keeper.yaml

kubectl create -f stolon-keeper.yaml

12. 更新image

 harbor.arfa.wise-paas.com/wilson/stolon:v0.17.0-pg13vector