PostgreSQL 資料庫管理及使用

PostgreSQL 是一套強大、高度可擴充且完全遵循標準的開源關聯式資料庫管理系統，廣受全球開發者社群喜愛。ServBay 作為支援 macOS 與 Windows 的本地 Web 開發環境，內建了 PostgreSQL，使其安裝與管理更加簡單高效。

本指南專為開發者設計，將詳細說明如何在 ServBay 環境中高效管理與運用 PostgreSQL，涵蓋安裝、設定、日常操作、備份還原、效能優化與安全等關鍵主題。

安裝與設定 PostgreSQL

ServBay 提供直覺式的圖形介面來管理包含 PostgreSQL 的各項軟體套件。

安裝 PostgreSQL

1. 開啟 ServBay 應用程式。
2. 點選左側導覽列的 套件管理。
3. 在套件清單中找到 PostgreSQL。
4. 選擇您需要的 PostgreSQL 版本（ServBay 支援多版本，例如 PostgreSQL 10 到 17）。
5. 點擊安裝按鈕開始安裝。

圖：於 ServBay 選取並安裝合適版本的 PostgreSQL

啟動、停止與管理 PostgreSQL 服務

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

使用 ServBay 管理平台

1. 開啟 ServBay 應用程式。
2. 點選左側導覽列的 套件管理。
3. 找到已安裝的 PostgreSQL 版本。
4. 利用套件旁的開關按鈕啟動、停止或重新啟動服務。

使用命令列工具 servbayctl

servbayctl 是 ServBay 提供的強大命令列管理介面，可操控各個元件。

# 啟動指定版本 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 資料庫

資料庫操作第一步即是完成連線。您可選用命令列工具 psql 或圖形化工具如 pgAdmin 連線至 ServBay 上的 PostgreSQL。

ServBay 提供兩大連線方式：TCP/IP（多用於外部工具或跨容器）及本地 Socket（推薦 ServBay 內部用途，高效又安全）。

取得連線憑證

連線前需取得 PostgreSQL 的使用者名稱及密碼。ServBay 安裝或初始化時會設置預設憑證，你可於管理介面查詢，包括預設帳號（如 postgres）及自訂帳號。

使用命令列工具 psql 連線

psql 是 PostgreSQL 官方推出的互動命令列工具。

1. TCP/IP 連線： 適用於透過網路協定連線，支援 localhost 或其他網域。ServBay 預設 PostgreSQL 連接埠為 5432。

   psql -U your_username -h localhost -d your_database -p 5432

   請將 your_username 替換成你的使用者名稱；your_database 替換成資料庫名稱。

2. 本地 Socket 連線（僅限 macOS）： ServBay 在 macOS 以 Unix Domain Socket 快速連線，無須經網路堆疊，速度快且安全。Socket 檔通常置於 ServBay 臨時目錄。

   macOS:

   psql -U your_username -h /Applications/ServBay/tmp -d your_database

   請將 your_username 及 your_database 替換為你的帳號和庫名。/Applications/ServBay/tmp 為 Socket 預設存放路徑。

   Windows: Windows 版本以 TCP/IP 連線，請採用上述方式。

使用圖形化工具 pgAdmin 連線

pgAdmin 為熱門開源 PostgreSQL 管理工具，功能完整。

1. 安裝 pgAdmin：如 ServBay 未內建，請至 pgAdmin 官方網站 下載安裝。
2. 開啟 pgAdmin。
3. 新增伺服器連線：點選 新增伺服器。
4. 填寫連線資訊：
   • General：輸入連線名稱（如 ServBay PostgreSQL）。
   • Connection：
     • Hostname/address: localhost
     • Port: 5432
     • Maintenance database: 通常為 postgres
     • Username: 由 ServBay 取得（如 postgres）
     • Password: 由 ServBay 管理面板取得
   • SSL mode：依需求選擇（建議 Prefer 或 Require，如預設可用 Allow 或 Disable，請查閱 ServBay SSL 設定）。
5. 點選 儲存 完成連線。

資料庫管理基礎操作

成功連線後可進行各項資料庫管理操作。

列出資料庫與使用者

於 psql 命令列可用 meta 指令（\ 開頭）查詢目前庫與角色：

• 列出全部資料庫：

  \l

• 列出全部使用者（角色）：

  \du

新建資料庫與使用者

使用 SQL 指令建立新資料庫與帳號，並分配權限。

1. 建立新資料庫：

   CREATE DATABASE servbay_demo_db;

   建議資料庫命名具專案描述性。

2. 建立新使用者（角色）並設密碼：

   CREATE USER servbay_user WITH PASSWORD 'a_strong_password';

   請將 servbay_user 替換為需新增的帳號名稱，a_strong_password 替換為安全密碼。

3. 授予使用者庫權限：

   GRANT ALL PRIVILEGES ON DATABASE servbay_demo_db TO servbay_user;

   此指令授權 servbay_user 使用此庫。依資安原則，僅授予必要最小權限。

重設 PostgreSQL Root 使用者密碼

如忘記 PostgreSQL 的 root 使用者（預設為 postgres）密碼，ServBay 提供簡便的重設功能。通常可直接在 ServBay 管理界面操作，不需編輯設定檔或輸入複雜指令。請查閱 ServBay UI 或文件，於 PostgreSQL 套件設定頁找到重設密碼功能。

資料庫備份與還原

定期備份不可或缺！可善用 PostgreSQL 標準工具 pg_dump、pg_restore 或 ServBay 內建備份。

手動備份 pg_dump

pg_dump 能匯出 PostgreSQL 資料庫內容。

建議備份檔案放在 ServBay 建議的目錄：

macOS:

/Applications/ServBay/backup/postgresql

Windows:

C:\ServBay\backup\postgresql

使用 pg_dump 備份範例：

macOS:

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

Windows:

pg_dump -U your_username -d your_database -F c -b -v -f C:\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 備份還原。

macOS:

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

Windows:

pg_restore -U your_username -d target_database -v C:\ServBay\backup\postgresql\your_database_backup.dump

• -U your_username: 登入使用者。
• -d target_database: 欲還原的庫名，如未建立請先建庫。
• -v: 顯示詳細資訊。
• <備份路徑>: 指定還原來源。

利用 ServBay 內建備份功能

ServBay 支援圖形介面操作與自動化備份整個開發環境（含 PostgreSQL）。

ServBay 內建備份功能包含：

• 手動備份：在 ServBay UI 一鍵快照目前環境。
• 自動備份：可設排程，定期自動備份。

備份內容涵蓋 ServBay 設定、網站檔案、所有資料庫（含 PostgreSQL）、SSL 憑證等。強烈建議善用此功能，簡化備份流程、保障資料安全。您可於 ServBay 設定頁或主介面上尋找並配置相關選項。

效能優化

為確保 PostgreSQL 在 ServBay 上高效運作，建議採取以下優化措施。

索引優化

資料庫索引能顯著提升資料查詢速度，尤其對大型表或常用進行查詢、連接、排序的欄位尤為重要。

請務必為常作為 WHERE、JOIN 或 ORDER BY 的欄位建立索引。

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 查詢計畫，深度了解 PostgreSQL 如何執行查詢、查找效能瓶頸。

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

EXPLAIN 結果會顯示何表用索引、連接方式、掃描筆數等，據此可進一步修正 SQL 或調整索引。

設定優化

調整 PostgreSQL 設定檔（如 postgresql.conf）各項參數，配合硬體資源與負載。

舉例來說，shared_buffers 控制資料庫快取記憶體，增加可提升效能（但勿超過系統總記憶體）。

# postgresql.conf 設定範例
shared_buffers = 1GB # 請依你的主機硬體搭配調整

參數變更後，一般需重啟 PostgreSQL 服務方能生效。操作前請參考 ServBay 設定文檔。

資安管理

保障資料庫安全至關重要，尤其開發環境亦可能處理機密資料。

設定強密碼

所有資料庫使用者，特別是高權限帳戶（如 postgres），請設複雜且獨立的強密碼，並定期更換。

ALTER USER your_username WITH PASSWORD 'your_new_strong_password';

定期備份

如前述，定期備份是防止資料流失的核心策略。結合 ServBay 內建自動備份與手動 pg_dump，可建立穩健備份機制。

權限限制

奉行最小權限原則，僅給使用者完成其作業所需權益。避免任意授予 ALL PRIVILEGES。

-- 回收指定使用者在資料庫全部權限
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

1. 確認服務是否運行： 使用 ServBay UI 或 servbayctl status postgresql <version> 查詢。若未運行，請啟動。
2. 確認連線參數： 檢查使用的帳號、密碼、資料庫、主機、連接埠。尤其注意是否從 ServBay UI 取得正確憑證。
   • macOS: localhost 或 /Applications/ServBay/tmp，連接埠為 5432
   • Windows: localhost，連接埠為 5432
3. 檢查 ServBay 日誌： 查閱 ServBay 日誌檔，找尋 PostgreSQL 相關錯誤訊息以釐清原因。
4. 檢查防火牆設定： 雖然預設本地連線通常無防火牆問題，但若系統或防毒軟體有防火牆，須確保允許 ServBay 下 PostgreSQL 本地存取。

權限問題

遇到“權限被拒”錯誤，直指目前連線使用者權限不足。

1. 檢查權限： 於 psql 用 \du 查帳號清單與配置，用 \dp <table_name> 查表格權限。
2. 授予權限： 用高權限帳號如 postgres 連線，再以 GRANT 命令補權。

   GRANT ALL PRIVILEGES ON DATABASE mydatabase TO your_username; -- 賦予庫層級權限
   GRANT SELECT ON TABLE your_table_name TO your_username; -- 賦予表格 SELECT 權限

總結

PostgreSQL 是極為強大且多元的資料庫管理系統，ServBay 大幅精簡其於 macOS 與 Windows 的部署及管理流程。經由本指南，您可熟練在 ServBay 安裝、設定、連線 PostgreSQL，執行基礎資料庫管理，善用 ServBay 內建備份、手動備份還原、實施效能優化與強化資安。掌握這些技巧，能幫助您在本地 Web 開發環境運用資料庫，更安全、更高效！