還在用MySQL？這份PostgreSQL教學讓你秒懂它為何是企業級首選

/ 作者:

在當今數據驅動的世界中，選擇一個功能強大且可靠的資料庫管理系統 (RDBMS) 是專案成功的基石。PostgreSQL是一個開源的物件關聯式資料庫系統，常被簡稱為 “Postgres”，以其超過三十年的活躍開發歷史、無與倫比的穩定性、豐富的功能集以及對資料完整性的堅定承諾而聞名於世。

與許多其他資料庫系統不同，PostgreSQL 不僅僅是一個純粹的關聯式資料庫，它融入了物件導向的概念，支援更複雜的資料結構和自訂資料類型。這使得它在處理地理空間資訊 (GIS)、時間序列數據以及需要複雜查詢和高頻寫入操作的企業級應用中，表現尤為出色，在現代雲端架構中也扮演著關鍵角色。

本篇文章旨在為初學者提供一份詳盡的入門指南。希望這份 postgresql教學能激發您的學習興趣；我們將從比較 PostgreSQL 與另一流行資料庫 MySQL 的差異開始，幫助您理解其獨特優勢；接著，我們將手把手帶您完成在 Windows 及其他作業系統上的安裝與環境設定；最後，透過圖形化介面 (pgAdmin) 與命令列工具 (psql) 進行實際操作，讓您穩固地踏出使用 PostgreSQL 的第一步。

為何選擇 PostgreSQL？

雖然 PostgreSQL 和 MySQL 都是頂尖的開源關聯式資料庫，但它們在核心架構和功能支援上存在顯著差異。了解這些差異有助於您根據專案需求做出最合適的選擇。

類別 MySQL PostgreSQL
資料庫技術 純粹的關聯式資料庫管理系統 (RDBMS)。 物件關聯式資料庫管理系統 (ORDBMS)，支援物件、繼承等進階概念。
ACID 合規性 依賴儲存引擎，例如 InnoDB 和 NDB Cluster 引擎才能完全符合 ACID。 在所有設定中皆完全符合 ACID (原子性、一致性、隔離性、持久性)。
並行控制 使用傳統的讀寫鎖。在某些引擎 (如 InnoDB) 中支援 MVCC。 內建並支援多版本並行控制 (MVCC)，讀取操作不會阻塞寫入操作，在高併發寫入場景下效能更佳。
資料類型 支援標準的數字、字元、日期、JSON 等類型。 支援所有 MySQL 資料類型，並額外提供更豐富的類型，如陣列、XML、幾何、網路位址、範圍及高效的 JSONB。
索引支援 主要支援 B-Tree 和 R-Tree 索引。 支援多種進階索引類型，如 B-Tree、雜湊、GIN、GiST、表達式索引和部分索引，提供更靈活的查詢優化選項。
進階功能 對檢視、觸發程序和預存程序的支援相對基礎。 支援具體化視觀表 (Materialized Views)、INSTEAD OF 觸發程序，且預存程序可使用多種語言 (如 PL/pgSQL, Python, Perl) 編寫。
適用情境 非常適合讀取密集型的工作負載、內容管理系統 (CMS) 和較簡單的 Web 應用。 極其適合需要處理複雜查詢、高頻率寫入、要求嚴格資料完整性和地理空間數據的企業級應用。

總結來說，MySQL 這個產品以其易用性和在讀取密集型應用中的高效能而廣受歡迎。然而，當您的應用程式需要處理複雜的資料邏輯、保證最嚴格的資料一致性，並利用更豐富的資料類型和索引時，PostgreSQL 無疑是更為強大和可靠的選擇。

在 Windows 上安裝 PostgreSQL

對於 Windows 使用者，PostgreSQL 的安裝過程相當直觀，主要透過由 EDB 公司提供的圖形化安裝程式完成。

步驟 1: 下載安裝程式

  1. 前往 PostgreSQL 官網。
  2. 點擊頁面上的 “Download” 按鈕。
  3. 在作業系統列表中選擇 “Windows”。
  4. 您將被引導至「Interactive installer by EDB」頁面，點擊「Download the installer」。
  5. 根據您的系統架構選擇最新的版本進行下載 (例如，Windows x86-64)。

步驟 2: 執行安裝精靈

雙擊下載的 .exe 檔案，啟動安裝精靈，並按照下面的步驟說明進行操作：

  • 安裝路徑：除非有特殊需求，建議保留預設的安裝目錄 (例如 C:\Program Files\PostgreSQL\16)。
  • 選擇元件 (Select Components)：
    • PostgreSQL Server：核心資料庫伺服器，必選。
    • pgAdmin 4：官方推薦的圖形化管理工具，強烈建議初學者安裝。
    • Stack Builder：用於安裝額外工具和驅動程式的套件管理器，可選。
    • Command Line Tools：命令列工具，如 psql，必選。
  • 資料目錄 (Data Directory)：這是儲存所有資料庫檔案的位置。同樣，建議保留預設值。請注意，即使您將來移除 PostgreSQL，這個資料夾也需要手動刪除。
  • 設定超級使用者密碼 (Password)：您需要為預設的超級使用者 postgres 設定一個密碼。請務必牢記此密碼，後續連接資料庫時將會用到。
  • 連接埠 (Port)：預設連接埠為 5432。如果此連接埠已被其他應用程式佔用，您需要更換一個，否則請保留預設值。
  • 地區設定 (Locale)：選擇 [Default locale] 即可，安裝程式會根據您的作業系統設定自動選擇。

完成上述設定後，持續點擊 “Next”，安裝程式便會開始安裝。

步驟 3: 安裝後的重要設定

安裝完成後，為了方便日後使用，強烈建議進行以下兩項設定：

設定環境變數

為了能從系統任何路徑的命令提示字元 (cmd) 或 PowerShell 中直接執行 psql 等工具，您需要將 PostgreSQL 的 bin 目錄加入到系統的 Path 環境變數中。這對於開發者和系統管理員來說都是一個重要的設定。

  1. 在 Windows 搜尋欄中輸入「編輯系統環境變數」，並開啟它。
  2. 在「系統屬性」視窗中，點擊「環境變數」。
  3. 在「系統變數」區塊中，找到並選取 Path 變數，然後點擊「編輯」。
  4. 在「編輯環境變數」視窗中，點擊「新增」，並輸入您的 PostgreSQL bin 資料夾路徑，例如：C:\Program Files\PostgreSQL\16\bin (請根據您的安裝版本調整)。
  5. 一路點擊「確定」以儲存變更。

建立 pgAdmin 4 捷徑

安裝程式通常不會自動在桌面上建立 pgAdmin 的捷徑。您需要手動找到它：

  1. 前往 pgAdmin 的安裝路徑，通常位於：C:\Program Files\PostgreSQL\16\pgAdmin 4\bin。
  2. 在該目錄下找到 pgAdmin4.exe。
  3. 對其點擊右鍵，選擇「傳送到」>「桌面 (建立捷徑)」，方便日後快速啟動。

其他平台的安裝方式

PostgreSQL 跨平台支援性極佳，在 macOS 和 Linux 上也有便捷的安裝方法。

macOS：

若您已安裝 Homebrew 套件管理器，只需一行指令即可完成安裝：

brew install postgresql

Linux (Debian/Ubuntu)：

使用 apt 套件管理器進行安裝：

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

postgresql-contrib 套件包含了許多有用的額外模組和工具。

使用 Docker 容器：

對於偏好容器化開發的用戶，使用 Docker 啟動一個 PostgreSQL 實例非常方便：

docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

此指令會啟動一個名為 my-postgres 的容器，設定超級使用者密碼為 mysecretpassword，並將容器的 5432 埠對應到主機的 5432 埠。

啟動伺服器與基本操作

安裝完成後，資料庫伺服器通常會自動啟動為系統服務。接下來，我們將學習如何使用 pgAdmin 和 psql 這兩種工具來連接並操作資料庫。

使用圖形化介面 (pgAdmin)

  • 啟動與連接：打開您剛才建立的 pgAdmin 4 桌面捷徑。首次啟動時，它可能會要求您設定一個主密碼來保護儲存的伺服器資訊。接著，在左側的瀏覽器中，點開 Servers，您會看到一個預設的 PostgreSQL 伺服器連線。點擊它，並輸入您在安裝時設定的超級使用者 (postgres) 密碼。
  • 建立資料庫：
    1. 在成功連接伺服器後，對 Databases 項目點擊右鍵，選擇 Create > Database…。
    2. 在 Database 欄位中輸入一個名稱，例如 mydemodb，然後點擊 Save。
  • 建立資料表與執行查詢：
    1. 展開您剛建立的 mydemodb，對 Schemas > public > Tables 點擊右鍵，選擇 Create > Table…。不過，對於初學者，使用 SQL 查詢工具會更直接。這個部份我們將專注於此。
    2. 點擊 mydemodb 資料庫，然後從頂部選單中選擇 Tools > Query Tool，開始您的 SQL 查詢體驗。
    3. 在右側的查詢編輯器中，輸入以下 SQL 指令來建立一個員工資料表： 
      CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);
    4. 點擊工具列上的 “Execute/Refresh” 按鈕 (通常是一個播放圖示) 來執行指令。
    5. 接著，插入一些資料： 
      INSERT INTO employees (first_name, last_name, hire_date) VALUES
('Alice', 'Smith', '2023-05-10'),
('Bob', 'Johnson', '2024-01-15');
    6. 最後，查詢資料以驗證結果： 
      SELECT * FROM employees;

使用命令列工具 (psql)

psql 是一個功能強大的互動式終端機，適合快速執行查詢和腳本。

  • 啟動與連接：打開 Windows 的命令提示字元 (cmd) 或 PowerShell。由於您已設定好環境變數，可以直接輸入以下指令來連接資料庫： 
    psql -U postgres

    系統會提示您輸入 postgres 使用者的密碼。

  • 基本 psql 指令：
    • \l：列出所有可用的資料庫。
    • \c mydemodb：連接到您剛才用 pgAdmin 建立的 mydemodb 資料庫。
    • \dt：顯示目前資料庫中所有的資料表。
    • \d employees：顯示 employees 資料表的詳細結構。
    • \q：退出 psql。
  • 執行 SQL 查詢：在連接到資料庫後，您可以直接輸入 SQL 指令，並以分號 (;) 結尾。例如： 
    SELECT first_name, hire_date FROM employees ORDER BY hire_date DESC;

常見問題 (FAQ)

Q1: 安裝後忘記了超級使用者 (postgres) 的密碼怎麼辦？

A: 找回密碼需要修改設定檔以暫時繞過密碼驗證。請謹慎操作：

  1. 找到您的資料目錄下的 pg_hba.conf 檔案。
  2. 用文字編輯器打開它，找到類似 host all all ::1/128 scram-sha-256 的一行，將 scram-sha-256 (或 md5) 暫時修改為 trust。
  3. 重新啟動 PostgreSQL 服務。
  4. 使用 psql -U postgres 無密碼登入。
  5. 執行 ALTER USER postgres WITH PASSWORD ‘new_password’; 來設定新密碼。
  6. 將 pg_hba.conf 檔案改回原來的設定，並再次重啟服務。

Q2: 為什麼我無法從遠端電腦連接到我的 PostgreSQL 資料庫？

A: 預設情況下，PostgreSQL 只允許本機連線。您需要修改兩個設定檔：

  1. postgresql.conf：找到 listen_addresses 這一行，將其值從 ‘localhost’ 改為 ‘*’，表示監聽所有網路介面。
  2. pg_hba.conf：新增一行來允許遠端 IP 位址的連線，例如 host all all 192.168.1.0/24 scram-sha-256，允許 192.168.1.x 網段的使用者使用密碼連線。

    修改後，別忘了重啟 PostgreSQL 服務，並檢查防火牆是否已開放 5432 連接埠。

Q3: pgAdmin 和 psql 有什麼不同？我應該用哪一個？

A: pgAdmin 是一個功能齊全的圖形化介面 (GUI)，非常適合初學者，它提供了視覺化的方式來瀏覽資料庫物件、設計資料表和編寫查詢，操作直觀。psql 則是一個命令列介面 (CLI)，雖然學習的困難程度稍高，但對於有經驗的開發者和資料庫管理員來說，它執行效率更高，並且易於整合到自動化腳本中。建議初學者從 pgAdmin 開始，熟悉概念後，再逐步學習使用 psql 以提升效率。

總結

恭喜您！透過本篇教學，您已經成功地從零開始，完成了 PostgreSQL 的安裝、環境設定，並學會了使用 pgAdmin 和 psql 兩種核心工具來進行基本的資料庫創建與操作。您不僅理解了 PostgreSQL 相較於其他資料庫的獨特優勢，也為後續更深入的學習奠定了堅實的基礎。

PostgreSQL 的世界遠比本篇介紹的更為廣闊。從複雜的 JOIN 查詢、索引優化、交易控制到使用者權限管理，還有無數強大的功能等待您去探索。建議您下一步可以深入閱讀官方文件，持續練習，將 PostgreSQL 的強大能力應用到您的下一個專案中。

資料來源

相關文章

返回頂端