跳至主要內容
日沐 SunBathe

Appearance

PostgreSQL 入門與環境建置 ​

PostgreSQL（常簡稱 Postgres）是世界上最先進的開源關聯式資料庫管理系統。它以 穩定性、標準相容性、擴充性 著稱，被許多大型企業採用，包括 Apple、Instagram、Spotify 等。

一、 PostgreSQL vs 其他資料庫 ​

在選擇資料庫時，常見的比較對象是 MySQL 和 SQLite：

特性PostgreSQLMySQLSQLite
定位企業級、功能完整Web 應用首選嵌入式、單檔案
SQL 標準高度遵循部分偏差基本支援
JSON 支援JSONB（二進位、可索引）JSON（純文字）JSON（基本）
進階功能視窗函數、CTE、物化檢視8.0 後支援部分有限
擴充性極強（自訂型別、函數、索引）中等無
適用場景複雜查詢、資料分析、GIS高併發 Web行動 App、原型開發

> **選擇建議**

  • 需要複雜查詢或 JSONB → PostgreSQL
  • 簡單 CRUD、追求速度 → MySQL
  • 單機應用、不想架 Server → SQLite

二、 安裝 PostgreSQL ​

方法 A：使用 Docker（推薦） ​

最簡單且不污染系統的方式：

bash
# 啟動 PostgreSQL 容器
docker run --name my-postgres \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:16

# 進入容器內的 psql
docker exec -it my-postgres psql -U admin -d mydb

方法 B：macOS 使用 Homebrew ​

bash
# 安裝
brew install postgresql@16

# 啟動服務
brew services start postgresql@16

# 進入 psql
psql postgres

方法 C：Ubuntu/Debian ​

bash
# 安裝
sudo apt update
sudo apt install postgresql postgresql-contrib

# 切換到 postgres 用戶
sudo -u postgres psql

方法 D：Windows ​

下載 官方安裝程式，安裝過程會引導你設定 superuser 密碼。

三、 psql 命令列工具 ​

psql 是 PostgreSQL 附帶的互動式終端機，是日常操作資料庫的主要工具。

連線語法 ​

bash
psql -h 主機 -p 埠號 -U 使用者 -d 資料庫

# 範例：連線到本機的 mydb
psql -h localhost -p 5432 -U admin -d mydb

常用 Meta 指令 ​

在 psql 內，以反斜線 \ 開頭的是特殊指令：

指令說明
\l列出所有資料庫
\c dbname切換到指定資料庫
\dt列出當前資料庫的所有資料表
\d tablename顯示資料表結構
\du列出所有使用者/角色
\x切換擴展顯示模式（直式輸出）
\timing開啟查詢計時
\q離開 psql
\?顯示所有 Meta 指令說明

四、 建立第一個資料庫 ​

建立資料庫 ​

sql
-- 在 psql 內執行
CREATE DATABASE bookstore;

-- 切換到新資料庫
\c bookstore

建立資料表 ​

sql
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100),
    price NUMERIC(10, 2),
    published_at DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

新增資料 ​

sql
INSERT INTO books (title, author, price, published_at)
VALUES
    ('深入淺出 PostgreSQL', '王小明', 580.00, '2024-01-15'),
    ('SQL 效能調校', '李大華', 650.00, '2023-08-20');

查詢資料 ​

sql
-- 查詢所有書籍
SELECT * FROM books;

-- 查詢價格超過 600 的書籍
SELECT title, price FROM books WHERE price > 600;

-- 依價格排序
SELECT title, price FROM books ORDER BY price DESC;

更新與刪除 ​

sql
-- 更新價格
UPDATE books SET price = 620.00 WHERE id = 1;

-- 刪除資料
DELETE FROM books WHERE id = 2;

五、 基本設定檔 ​

PostgreSQL 的設定主要由兩個檔案控制：

postgresql.conf ​

主要設定檔，控制效能參數、連線數等：

bash
# 檔案位置（視安裝方式而異）
/var/lib/postgresql/16/main/postgresql.conf  # Linux
/usr/local/var/postgresql@16/postgresql.conf # macOS Homebrew

常見參數：

conf
# 最大連線數
max_connections = 100

# 共享緩衝區（建議設為記憶體的 25%）
shared_buffers = 256MB

# 工作記憶體（每個查詢可用）
work_mem = 16MB

# 監聽地址（允許遠端連線）
listen_addresses = '*'

pg_hba.conf ​

控制連線認證規則：

conf
# 類型   資料庫   使用者   來源IP          認證方式
local    all      all                      peer
host     all      all      127.0.0.1/32    scram-sha-256
host     all      all      0.0.0.0/0       scram-sha-256

> **安全提醒**

生產環境請勿設定 0.0.0.0/0 搭配弱密碼，這會讓任何人都能嘗試連線。

六、 GUI 工具推薦 ​

如果你不習慣命令列，以下是常用的圖形化工具：

工具平台特點
pgAdmin跨平台官方工具，功能完整
DBeaver跨平台支援多種資料庫
TablePlusmacOS/Windows介面美觀、效能佳
DataGrip跨平台JetBrains 出品，功能強大

總結 ​

概念說明
PostgreSQL開源、功能強大、高度標準相容的關聯式資料庫
psql命令列互動工具，日常操作的主力
SERIAL自動遞增的整數型別（相當於其他 DB 的 AUTO_INCREMENT）
postgresql.conf效能與行為設定
pg_hba.conf連線認證設定

進階挑戰 ​

  1. 使用 Docker Compose 建立一個包含 PostgreSQL 和 pgAdmin 的開發環境
  2. 嘗試修改 postgresql.conf，將 shared_buffers 調整為系統記憶體的 25%，並觀察效能變化
  3. 建立一個新的資料庫使用者，只給予特定資料庫的讀取權限

延伸閱讀與資源 ​

← 返回專題首頁 | 下一章：資料型別完全指南 →