🗄️ PostgreSQL

中級資料庫 SQL JSONB ACID 進階查詢 PostGIS 更新：2026 年 5 月 20 日

功能最完整的開源關聯式資料庫

PostgreSQL 支援 JSONB、全文搜尋、視窗函數、CTE、地理空間等進階特性，嚴格的 ACID 合規性讓它成為複雜業務系統的首選。

安裝

$ sudo apt update && sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

$ sudo -u postgres psql    # 以 postgres 系統使用者登入

連線與認證（pg_hba.conf）

認證設定在 /etc/postgresql/16/main/pg_hba.conf（版本號依安裝而異）：

# TYPE  DATABASE  USER    ADDRESS    METHOD
local   all       postgres           peer      # 系統使用者認證
local   all       all                md5       # 本機密碼認證
host    all       all   127.0.0.1/32 scram-sha-256  # TCP 本機
host    all       all   0.0.0.0/0    scram-sha-256  # 遠端（需謹慎）

$ sudo systemctl reload postgresql   # 修改後重載

資料庫操作

-- 建立資料庫
CREATE DATABASE myapp
    WITH ENCODING 'UTF8'
         LC_COLLATE 'zh_TW.UTF-8'
         LC_CTYPE   'zh_TW.UTF-8';

-- 列出所有資料庫
\l

-- 切換資料庫
\c myapp

-- 列出所有資料表
\dt

-- 查看資料表結構
\d users

-- 退出
\q

使用者與權限

-- 建立使用者
CREATE USER appuser WITH PASSWORD '安全密碼';

-- 賦予資料庫所有權限
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- 賦予特定表的讀取權限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 設定預設權限（未來新建的表自動授權）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- 刪除使用者
DROP USER appuser;

允許遠端連線

$ # 1. 修改 postgresql.conf 允許外部監聽
sudo vim /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'    # 預設 localhost，改為 * 監聽所有介面

$ # 2. 在 pg_hba.conf 新增允許的 IP
echo "host myapp appuser 192.168.1.0/24 scram-sha-256" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
sudo systemctl restart postgresql

# 3. 開放防火牆
sudo ufw allow from 192.168.1.0/24 to any port 5432

備份與還原

$ # 備份單一資料庫（自訂格式，支援平行還原）
sudo -u postgres pg_dump -Fc myapp > myapp_$(date +%Y%m%d).dump

# 備份為純 SQL
sudo -u postgres pg_dump myapp > myapp_$(date +%Y%m%d).sql

# 備份所有資料庫（含角色）
sudo -u postgres pg_dumpall > all_$(date +%Y%m%d).sql

$ # 還原自訂格式備份
sudo -u postgres pg_restore -d myapp myapp_20260520.dump

# 還原 SQL 備份
sudo -u postgres psql myapp < myapp_20260520.sql

常見問題

✕ FATAL: role "username" does not exist

原因: 用 psql 連線時，預設使用當前 Linux 使用者名稱作為角色。
解法: 使用 sudo -u postgres psql 以 postgres 角色登入，再建立對應的使用者。

✕ connection refused (port 5432)

原因: PostgreSQL 未監聽外部 IP，或防火牆封鎖。
解法: 確認 listen_addresses 設定，並確認 pg_hba.conf 中允許來源 IP，以及防火牆開放 5432。

安裝