PostgreSQL 資料庫管理及使用

PostgreSQL 是一款功能強大、高度可擴展且遵循標準的開源關聯式資料庫管理系統，深受開發者社群喜愛。ServBay 作為一套專為 macOS 設計的本地 Web 開發環境，內建 PostgreSQL，大幅簡化了其安裝與管理流程。

本文將作為開發者指南，詳細闡述如何在 ServBay 環境下高效地管理與運用 PostgreSQL，涵蓋從安裝、設定到日常操作、備份還原、效能優化及安全防護等核心面向。

安裝與設定 PostgreSQL

ServBay 提供直觀的圖形化介面來管理各種軟體套件，包括 PostgreSQL。

安裝 PostgreSQL

開啟 ServBay 應用程式。
點擊左側導覽列的 軟體包。
在軟體包清單中找到 PostgreSQL。
選擇您需要的 PostgreSQL 版本（ServBay 支援多個版本，例如 PostgreSQL 10 至 17）。
點擊安裝按鈕開始安裝。

圖：於 ServBay 介面選擇並安裝 PostgreSQL 版本

啟動、停止與管理 PostgreSQL 服務

安裝完成後，您可以透過 ServBay 的管理平台或命令列工具 servbayctl 來管理 PostgreSQL 服務的運作狀態。

使用 ServBay 管理平台

開啟 ServBay 應用程式。
點擊左側導覽列的 軟體包。
找到已安裝的 PostgreSQL 版本。
透過軟體包旁的切換按鈕啟動、停止或重啟服務。

使用命令列工具 `servbayctl`

servbayctl 是 ServBay 提供的強大命令列介面，可用於管理 ServBay 各項組件。

bash

# 啟動指定版本的 PostgreSQL 服務（例如 PostgreSQL 16）
servbayctl start postgresql 16

# 停止指定版本的 PostgreSQL 服務（例如 PostgreSQL 16）
servbayctl stop postgresql 16

# 重啟指定版本的 PostgreSQL 服務（例如 PostgreSQL 16）
servbayctl restart postgresql 16

# 查詢指定版本的 PostgreSQL 服務狀態（例如 PostgreSQL 16）
servbayctl status postgresql 16

注意：命令中的版本號（如 16）需與您於 ServBay 實際安裝的 PostgreSQL 版本一致。

設定 PostgreSQL

ServBay 讓您可以方便地調整 PostgreSQL 的設定。有效的設定對於提升資料庫效能、確保安全性及合理運用系統資源至關重要。

ServBay 具備強大的圖形介面可協助設定 PostgreSQL 服務，您也能直接編輯設定檔。詳情請參考修改 PostgreSQL 設定文件，了解如何經由 ServBay 介面或手動編輯設定檔，以最佳化 PostgreSQL 各項參數。

連線 PostgreSQL 資料庫

要操作 PostgreSQL，首先需要連線至資料庫。您可以利用命令列工具 psql，或像 pgAdmin 這類圖形化工具，來連線 ServBay 中運行的 PostgreSQL 實例。

ServBay 支援兩種主要連線方式：TCP/IP 連線（適合外部工具或跨容器/服務的連線）與本地 Socket 連線（ServBay 內部推薦，效能更高且更安全）。

取得連線憑證

在連線前，您需要取得 PostgreSQL 的使用者名稱及密碼。ServBay 通常會在資料庫安裝或初始化時設置預設憑證，您可於 ServBay 管理介面中查找這些資訊，特別是預設使用者（如 postgres）或您自行新增的其他用戶。

使用命令列工具 `psql` 連線

psql 為 PostgreSQL 官方提供之互動式命令列客戶端。

TCP/IP 連線：此方式透過網路協定連線，適用於連接 localhost 或其他網路位址。ServBay 中 PostgreSQL 預設埠為 5432。
bash
```
psql -U your_username -h localhost -d your_database -p 5432
```
1
請將 your_username 替換成您的資料庫帳號，your_database 替換為欲連接之資料庫名稱。
本地 Socket 連線： ServBay 通常設定 PostgreSQL 採用本地 Unix Domain Socket 進行連線，效能更優且安全性更高。Socket 檔案一般位於 ServBay 的暫存目錄內。
bash
```
psql -U your_username -h /Applications/ServBay/tmp -d your_database
```
1
your_username 請填資料庫帳號，your_database 請填要連接的資料庫名。/Applications/ServBay/tmp 為 ServBay 預設的 PostgreSQL Socket 存放路徑。

使用圖形化工具 pgAdmin 連線

pgAdmin 是一款熱門的開源 PostgreSQL 圖形化管理工具，提供豐富的功能。

安裝 pgAdmin：如 ServBay 未內建，可於 pgAdmin 官方網站下載安裝。
開啟 pgAdmin。
建立新伺服器連線：點擊 Add New Server。
輸入連線資訊：
- General 分頁：輸入連線名稱（如 ServBay PostgreSQL）。
- Connection 分頁：
  - Hostname/address：localhost
  - Port：5432
  - Maintenance database：通常為 postgres
  - Username：從 ServBay 管理面板查詢的帳號（如 postgres）
  - Password：從 ServBay 管理面板查詢的密碼
- SSL mode：依需求選擇（通常選 Prefer 或 Require 更安全，但預設情況下 ServBay 允許 Allow 或 Disable 亦可，請參考 ServBay 的 SSL 設定說明）。
點擊 Save 以連線資料庫伺服器。

資料庫管理基本操作

成功連線 PostgreSQL 後，即可執行各項資料庫管理任務。

列出現有資料庫與用戶

於 psql 命令列中，可使用特殊命令（\ 開頭）查詢現有資料庫與用戶：

列出所有資料庫：
sql
```
\l
```
1
列出所有用戶（角色）：
sql
```
\du
```
1

建立資料庫與用戶

您可執行 SQL 指令，建立新資料庫及用戶，並指派相關權限。

建立新資料庫：
sql
```
CREATE DATABASE servbay_demo_db;
```
1
建議用專案名稱或具識別性的資料庫名稱。
建立新用戶（角色）並設密碼：
sql
```
CREATE USER servbay_user WITH PASSWORD 'a_strong_password';
```
1
請將 servbay_user 改為期望的用戶名稱，a_strong_password 替換為安全密碼。
授權用戶存取資料庫權限：
sql
```
GRANT ALL PRIVILEGES ON DATABASE servbay_demo_db TO servbay_user;
```
1
這條指令會賦予 servbay_user 使用者對 servbay_demo_db 資料庫的所有權限。依照資訊安全最佳實踐，建議只給用戶必需的最小權限。

重設 PostgreSQL Root 用戶密碼

若您忘記 PostgreSQL 資料庫的 root 用戶（預設多為 postgres）密碼，ServBay 提供便捷重設功能。通常，此操作可直接透過 ServBay 的管理介面完成，無須繁複命令列或手動編輯設定檔。請查閱 ServBay 用戶介面或相關文件，在 PostgreSQL 軟體包設定處尋找重設密碼的選項。

資料庫備份與還原

定期備份是保障資料安全的關鍵。於 ServBay 環境中，您能利用 PostgreSQL 標準工具 pg_dump 和 pg_restore，亦可透過 ServBay 提供的內建備份功能。

使用 `pg_dump` 手動備份

pg_dump 可將 PostgreSQL 資料庫導出成檔案。

建議將備份檔保存於 ServBay 指定備份目錄：

bash

/Applications/ServBay/backup/postgresql

使用 pg_dump 備份資料庫示例：

bash

pg_dump -U your_username -d your_database -F c -b -v -f /Applications/ServBay/backup/postgresql/your_database_backup.dump

-U your_username：指定登錄資料庫帳號。
-d your_database：指定要備份的資料庫名稱。
-F c：自訂匯出格式（推薦，方便彈性還原）。
-b：包含大物件（Blobs）。
-v：顯示詳細過程。
-f <檔案路徑>：指定備份輸出檔名及路徑。

使用 `pg_restore` 手動還原

pg_restore 用於由 pg_dump 匯出的備份檔案還原 PostgreSQL 資料庫。

bash

pg_restore -U your_username -d target_database -v /Applications/ServBay/backup/postgresql/your_database_backup.dump

-U your_username：指定登錄帳號。
-d target_database：指定還原目標資料庫名稱。如目標資料庫不存在，請先新建。
-v：顯示詳細過程。
<檔案路徑>：指定備份檔案完整路徑。

使用 ServBay 內建備份功能

ServBay 提供圖形化介面及自動化選項，協助備份您的整體在地開發環境，包含 PostgreSQL 資料庫。

ServBay 內建備份功能特點：

手動備份：經由 ServBay UI 一鍵快照當前環境。
自動備份：可設定排程，讓 ServBay 定期自動備份。

備份內容包括 ServBay 設定、網站檔案、所有資料庫（含 PostgreSQL）、SSL 憑證等。大力建議善用此功能，簡化備份流程並強化資料安全。可於 ServBay 設定或主介面中尋找有關備份的項目進行調整與管理。

效能優化

為確保您的 PostgreSQL 資料庫於 ServBay 環境下運作流暢，建議採取下述效能最佳化措施。

索引優化

資料庫索引能顯著提升資料搜尋速度，特別針對大型資料表與高頻查詢的欄位。

請確保經常作為查詢條件（WHERE 子句）、連接條件（JOIN 子句）或排序（ORDER BY 子句）的欄位已建立索引。

sql

CREATE INDEX idx_your_column_name ON your_table_name(your_column_name);

請將 your_table_name 替換對應表名，your_column_name 替換欄位名稱，idx_your_column_name 改為索引名稱。

查詢優化

建議利用 EXPLAIN 指令分析 SQL 查詢的執行計劃，了解資料庫實際如何執行查詢，以找出潛在效能瓶頸。

sql

EXPLAIN SELECT * FROM your_table_name WHERE column_name = 'value';

EXPLAIN 結果顯示查詢是否使用索引、連接方式與掃描筆數等資料，參考這些結果可進一步微調 SQL 或索引配置。

設定優化

藉由調整 PostgreSQL 設定檔（通常為 postgresql.conf），可根據硬體資源及作業量最佳化多項參數。

舉例來說，調整 shared_buffers 能控制 PostgreSQL 用於快取資料的記憶體上限，適度增加有助於減少磁碟 I/O、提升效能（但勿超出台機可用記憶體）。

ini

# postgresql.conf 設定範例
shared_buffers = 1GB # 依照機器記憶體與工作負載調整

調整後通常需重啟 PostgreSQL 服務才會生效。詳情請參考 ServBay 設定修改文件。

安全管理

資料庫安全性至關重要，尤其當開發環境涉及敏感資料時。

設置強密碼

務必為所有帳號，特別是高權限用戶（如 postgres），設定複雜且唯一的強密碼，並定期變更。

sql

ALTER USER your_username WITH PASSWORD 'your_new_strong_password';

定期備份

如前所述，定期備份是避免資料遺失的根本。建議結合 ServBay 內建備份以及手動 pg_dump 策略，建立穩定的備份制度。

限制用戶權限

依循最小權限原則，僅給予帳號其實際所需權限。避免為所有用戶開啟 ALL PRIVILEGES。

sql

-- 撤銷用戶於資料庫的所有權限
REVOKE ALL PRIVILEGES ON DATABASE mydatabase FROM your_username;

-- 賦予用戶連接資料庫及建立暫存表權限（範例）
GRANT CONNECT, TEMPORARY ON DATABASE mydatabase TO your_username;

-- 授權用戶於特定表進行 SELECT、INSERT、UPDATE、DELETE
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE your_table_name TO your_username;

網路存取控制

ServBay 預設多將資料庫服務綁定於本地端接口（localhost 或 127.0.0.1），有效限制外部網路直接存取資料庫，提升安全性。除非有特殊需求且明白相關風險，不建議將資料庫開放於外部網路。

常見問題與解決方法

無法連線至 PostgreSQL

確認 PostgreSQL 服務是否啟動：請利用 ServBay UI 或執行 servbayctl status postgresql <version> 指令檢查服務狀態，如未運作請立即啟動。
檢查連線參數：嚴格確認所填用戶名、密碼、資料庫名稱、主機位址（localhost 或 /Applications/ServBay/tmp）及埠號（5432）是否正確。特別留意 ServBay UI 上的憑證資訊。
查閱 ServBay 日誌：檢查 ServBay 的日誌檔，尋找 PostgreSQL 有關錯誤提示，往往可據以判斷連線失敗原因。
檢查防火牆設置：雖一般 ServBay 本地端配置不會發生問題，但如系統或第三方資安軟體有嚴格防火牆規則，請確認允許對 ServBay 目錄下的 PostgreSQL 程式進行本地存取。

權限相關問題

若執行資料庫操作時遇到 “權限拒絕” 的錯誤，多半是目前用戶權限不足。

檢查現用者權限：於 psql 執行 \du 查詢用戶與屬性，並以 \dp <table_name> 查詢特定表之權限分配。

補足必要權限：請以具有足夠權限的用戶（如 postgres）連線，並使用 GRANT 指令將所需權限賦予目標帳號。

sql

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO your_username; -- 開放資料庫層級權限
GRANT SELECT ON TABLE your_table_name TO your_username; -- 開放表層級查詢權限

總結

PostgreSQL 是一套強大且彈性的資料庫管理系統，ServBay 極大地簡化了於 macOS 上部署與管理 PostgreSQL 的全流程。透過本文介紹，您已能熟練地於 ServBay 環境下進行 PostgreSQL 的安裝、設定、連線、基本管理、使用 ServBay 內建與手動備份還原、效能調校及安全強化。掌握這些技巧，將助您在本地 Web 開發中提升效率與安全。

PostgreSQL 資料庫管理及使用 ​

安裝與設定 PostgreSQL ​

安裝 PostgreSQL ​

啟動、停止與管理 PostgreSQL 服務 ​

使用 ServBay 管理平台 ​

使用命令列工具 servbayctl ​

設定 PostgreSQL ​

連線 PostgreSQL 資料庫 ​

取得連線憑證 ​

使用命令列工具 psql 連線 ​

使用圖形化工具 pgAdmin 連線 ​

資料庫管理基本操作 ​

列出現有資料庫與用戶 ​

建立資料庫與用戶 ​

重設 PostgreSQL Root 用戶密碼 ​

資料庫備份與還原 ​

使用 pg_dump 手動備份 ​

使用 pg_restore 手動還原 ​

使用 ServBay 內建備份功能 ​

效能優化 ​

索引優化 ​

查詢優化 ​

設定優化 ​

安全管理 ​

設置強密碼 ​

定期備份 ​

限制用戶權限 ​

網路存取控制 ​

常見問題與解決方法 ​

無法連線至 PostgreSQL ​

權限相關問題 ​

總結 ​

PostgreSQL 資料庫管理及使用

安裝與設定 PostgreSQL

安裝 PostgreSQL

啟動、停止與管理 PostgreSQL 服務

使用 ServBay 管理平台

使用命令列工具 `servbayctl`

設定 PostgreSQL

連線 PostgreSQL 資料庫

取得連線憑證

使用命令列工具 `psql` 連線

使用圖形化工具 pgAdmin 連線

資料庫管理基本操作

列出現有資料庫與用戶

建立資料庫與用戶

重設 PostgreSQL Root 用戶密碼

資料庫備份與還原

使用 `pg_dump` 手動備份

使用 `pg_restore` 手動還原

使用 ServBay 內建備份功能

效能優化

索引優化

查詢優化

設定優化

安全管理

設置強密碼

定期備份

限制用戶權限

網路存取控制

常見問題與解決方法

無法連線至 PostgreSQL

權限相關問題

總結