[PostgreSQL 16免安裝版]架設資料庫Server與Client端使用筆記(含安裝向量資料擴充pgvector)

ChunJen Wang

Published in

jimmy-wang

13 min readMay 23, 2024

本篇文寫於2024/05，紀錄如何透過zip(二進位檔)安裝PostgreSQL，作業系統為Window10 x64。

PostgreSQL號稱地表最強開源資料庫，也不少新創或大企業用於軟體開發過程，其中EDB更是以PostgreSQL為基礎推出各種資料庫服務產品。

PostgreSQL Logo. source: https://www.postgresql.org/

PostgreSQL版本目前每次維護5個版次，可參照自己所需要的版本調整，避免碰到已更新終止(EOL)問題。

PostgreSQL release timeline(2024/05), source: https://en.wikipedia.org/wiki/PostgreSQL

一、下載檔案

檔案下載連結:
(免安裝版) https://www.enterprisedb.com/download-postgresql-binaries
(安裝版) https://www.postgresql.org/download/windows/

這篇文章採用免安裝版，因此下載的是zip file，安裝版本為16.2.1

建議解壓縮位置放在D:\或E:\。

二、初始化設定: 初始化DB、連線管理

2–1 初始化DB

# 到安裝檔路徑下bin資料夾
cd your_unzip_path/postgresql-16.2-1-windows-x64-binaries/pgsql/bin

# 初始化DB:只需執行一次
initdb.exe -D ../data --username=postgres --auth=trust

到這裡就可以開始啟動我們的PostgreSQL Server：

# 啟動postgresql server
pg_ctl -D D:\postgresql-16.2-1-windows-x64-binaries\pgsql\data -l logfile start

# 可以透過以下指令確認指定port是否有被占用
netstat -ao | findstr 5433

於windows cmd預期結果

工作管理員裡面有多個執行序，主要控制的是postmaster。
完整架構可以參考EDB的教材
> https://pgadminedb.readthedocs.io/en/latest/module_02/

2–2 連線管理

PostgreSQL Server預設只開給local連線，所以不必擔心在啟動後，就門戶大開。

這裡需要調整的設定檔有兩個：

postgresql.conf：server端連線參數
pg_hba.conf：可控制依據資料庫、使用者、IP、登入方式調整

!注意!

－pg_hba.conf如果調整為ip 0.0.0.0/0代表任意IP都可以進行連線。

建議實際控管人數若不多，可以個別設定IPv4來進行開放，記得設定對應DB或User，這點可以彈性運用；若為分單位需求，則可考慮分網段開放。

連線方式(METHOD)則分為：

trust :不須驗證密碼，直接相信客戶端所提供的資料庫使用者(必須已經存在的db user)，若客戶端沒有明確提供資料庫使用者，則自動根據執行客戶端程式的os使用者當作資料庫使用者。
md5：客戶端將密碼以md5方式加密傳送至資料庫端進行驗證(pg13之前)
scram-sha-256：客戶端將密碼以scram-sha-256方式加密傳送至資料庫端進行驗證(pg13開始預設使用)
password：客戶端將密碼以明文方式傳送至資料庫端進行驗證
ident：直接使用執行客戶端軟體的os user當作資料庫使用者
reject：拒絕連線

重新讀取設定方式有2種：

調整完畢後，需要重啟服務或重讀(reload)設定檔。

# 已使用psql連線來重讀設定檔
SELECT pg_reload_conf();

# 於cmd透過pg_ctl指定DB server重讀設定檔
pg_ctl -D D:\postgresql-16.2-1-windows-x64-binaries\pgsql\data reload

三、基本管理操作

1. 建立使用者與授權

# 建立使用者(含密碼)
CREATE USER jimmy WITH PASSWORD 'jimmy012300';

# 透過既定群組設定權限，例如開放使用者可以「讀」「寫」資料
GRANT pg_read_all_stats, pg_read_all_data, pg_write_all_data TO jimmy;

# 設定使用者可以連線的DATABASE
GRANT CONNECT ON DATABASE dev TO jimmy;

# 開放特定功能，例如讓使用者可以在指定的schema「建立」表
GRANT USAGE, CREATE ON SCHEMA public TO jimmy;

PostgreSQL內建幾種權限群組:

2. 透過csv匯入資料

假如我們已經建立了一張table想要塞資料進去，但手上只有csv檔案，可以怎麼操作?

--匯入csv檔案
--透過COPY指定資料表、從From中指定檔案路徑

COPY table_name(cyc_mn, count)
FROM 'D:\jimmy\Downloads\mock_data.csv'
DELIMITER ','
CSV HEADER;

生成模擬資料網站: https://www.mockaroo.com/

3. client端連線怎麼操作?

方式A. 透過zip檔案中的psql來進行連線

psql -h (host IP) -p (port) -U (Username) -d (DB name)

# 舉例:
psql -h 10.ooo.ooo.ooo -p 5433 -U jimmy -d dev

方式B. Tableau連線 (示範版本為2021.2)

如果有資料視覺化軟體(像是Tableau)也可以直接連線

方式C. Python連線 (示範版本為3.10.14)

需要安裝psycopg2套件或其他整合的DB連線套件(例如sqlalchemy)

import psycopg2
import pandas as pd

password = input(f'password:')

conn = psycopg2.connect(database="dev", user="jimmy", 
                        password=password, host="10.ooo.ooo.ooo", 
                        port="5433")

連線成功後，就可以透過自建table來測試query：

四、管理介面GUI: pgAdmin4

pgAdmin包含了監控用的儀表板，
可以查看目前DB server運行狀況、吞吐量等等。

第一次開次需要建立新的連線:

建議管理者可以設定一個專用的，個人是採用(DB Name)_(User Name)來識別，例如dev_admin。

在pgAdmin也可以快速做表操作、查看，或建立schema, index, table等設定

五、安裝擴充: 向量資料儲存

向量資料格式在此時通常為「大型語言模型LLM開發用」

使用大型語言模型(LLM)時，如有開發RAG應用需求，
通常需要先將input與文件庫進行向量轉換(embedding)，
主要考量：

避免個資或機敏資料直接透過API傳送給LLM供應商，例如OpenAI。
加速資料比對與提高找到解答正確率(可以參照內部文件)。

GitHub - pgvector/pgvector: Open-source vector similarity search for Postgres

Open-source vector similarity search for Postgres. Contribute to pgvector/pgvector development by creating an account…

github.com

安裝的擴充為pgvector

安裝前須先確認有無安裝Visual Studio

# 確認是否已安裝Visual Studio
call "C:\Program Files\Microsoft Visual Studio\2022\Community\VC\Auxiliary\Build\vcvars64.bat"

# Expected Output
'''
**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.7.3
** Copyright (c) 2022 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64'
'''

2. 開始安裝pgvector

D:\postgresql-16.2-1-windows-x64-binaries\pgsql
set "PGROOT=D:\postgresql-16.2-1-windows-x64-binaries\pgsql"
cd %TEMP%
git -c http.sslVerify=false clone --branch v0.7.0 https://github.com/pgvector/pgvector.git
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

在RMDBS背後其實還是如一般儲存方式，但data type為「vector」

[補充]更多操作介紹，請參考PostgreSQL使用手冊(中文版)：

簡介 | 16 | PostgreSQL 正體中文使用手冊

本使用手冊由台灣 PostgreSQL 社群提供，翻譯自 PostgreSQL 官方使用手冊，以推廣 PostgreSQL 於台灣的應用。

docs.postgresql.tw

[AWS EC2安裝指令note]

whoami      #確認
sudo su     #切換至root權限
passwd root #設定密碼

# 安裝postgresql
dnf install postgresql15-server.x86_64 -y
# 初始化db
postgresql-setup --initdb
# 啟動server
systemctl start postgresql
# 加入服務
systemctl enable postgresql
### [OUTPUT] Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.

[Linux RedHat指令安裝note]

# Linux RedHat指令安裝
## RedHat
https://www.postgresql.org/download/linux/redhat/
# Install the repository RPM:
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

# Install PostgreSQL:
sudo dnf install -y postgresql16-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

✍️感謝閱讀到這邊的你，如果有其他想知道的也可以留言給我。

或幫我在底下拍手👏👏👏，來支持一下這邊文章喔~😀