Ubuntu 22.04 PostgreSQL 資料庫安裝設定

PostgreSQL 是一個功能強大的開源物件關聯式資料庫系統，本文將介紹如何在 Ubuntu 22.04 上安裝與設定 PostgreSQL。

PostgreSQL 簡介

PostgreSQL（簡稱 Postgres）是一個自由開源的物件關聯式資料庫管理系統（ORDBMS），強調擴展性和 SQL 相容性。它具有以下特點：

ACID 相容：完整支援事務處理的四大特性（原子性、一致性、隔離性、持久性）
高度擴展性：支援自訂資料類型、函數、運算子等
進階功能：支援 JSON/JSONB、全文搜尋、地理空間資料（PostGIS）
穩定可靠：經過 35 年以上的持續開發，擁有良好的穩定性
活躍社群：擁有龐大的開發者社群和豐富的文件資源

PostgreSQL 適用於各種應用場景，從小型專案到大型企業級應用都能勝任。

安裝步驟

1. 更新系統套件

首先，更新 Ubuntu 系統的套件清單：

1
2
sudo apt update
sudo apt upgrade -y

2. 安裝 PostgreSQL

Ubuntu 22.04 的官方套件庫已包含 PostgreSQL，可以直接安裝：

1
sudo apt install postgresql postgresql-contrib -y

postgresql-contrib 套件包含一些額外的實用工具和功能。

3. 確認服務狀態

安裝完成後，PostgreSQL 服務會自動啟動。確認服務狀態：

1
sudo systemctl status postgresql

若要設定開機自動啟動：

1
sudo systemctl enable postgresql

4. 確認安裝版本

檢查 PostgreSQL 版本：

1
psql --version

使用者與角色管理

PostgreSQL 使用「角色」（Role）的概念來管理權限，角色可以是使用者或群組。

切換到 postgres 使用者

安裝後，系統會自動建立一個名為 postgres 的系統使用者和 PostgreSQL 角色：

1
sudo -i -u postgres

進入 PostgreSQL 互動介面

1
psql

建立新角色

在 psql 介面中建立新角色：

1
CREATE ROLE myuser WITH LOGIN PASSWORD 'your_secure_password';

或使用命令列工具：

1
sudo -u postgres createuser --interactive --pwprompt myuser

賦予角色權限

賦予建立資料庫的權限：

1
ALTER ROLE myuser CREATEDB;

賦予超級使用者權限（謹慎使用）：

1
ALTER ROLE myuser SUPERUSER;

查看所有角色

1
\du

刪除角色

1
DROP ROLE myuser;

建立資料庫

使用 SQL 指令建立

在 psql 介面中：

1
CREATE DATABASE mydb;

建立資料庫並指定擁有者：

1
CREATE DATABASE mydb OWNER myuser;

使用命令列工具建立

1
sudo -u postgres createdb mydb

連線到資料庫

1
psql -d mydb -U myuser

常用資料庫操作指令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 列出所有資料庫
\l

-- 切換資料庫
\c mydb

-- 列出目前資料庫的所有資料表
\dt

-- 顯示資料表結構
\d tablename

-- 離開 psql
\q

刪除資料庫

1
DROP DATABASE mydb;

遠端連線設定

預設情況下，PostgreSQL 只允許本機連線。若需要遠端連線，需要修改設定檔。

1. 修改 postgresql.conf

編輯設定檔：

1
sudo nano /etc/postgresql/14/main/postgresql.conf

找到 listen_addresses 設定，修改為：

1
listen_addresses = '*'

或指定特定 IP：

1
listen_addresses = 'localhost, 192.168.1.100'

2. 修改 pg_hba.conf

編輯客戶端認證設定檔：

1
sudo nano /etc/postgresql/14/main/pg_hba.conf

在檔案末尾加入允許的連線規則：

1
2
3
4
5
# 允許特定 IP 連線
host    all             all             192.168.1.0/24          scram-sha-256

# 允許所有 IP 連線（不建議用於生產環境）
host    all             all             0.0.0.0/0               scram-sha-256

設定說明：

host：連線類型
all：適用的資料庫（可指定特定資料庫）
all：適用的使用者（可指定特定使用者）
192.168.1.0/24：允許的 IP 範圍
scram-sha-256：認證方式

3. 重新啟動服務

1
sudo systemctl restart postgresql

4. 設定防火牆

若有啟用防火牆，需開放 PostgreSQL 的預設埠號 5432：

1
sudo ufw allow 5432/tcp

5. 測試遠端連線

從遠端主機測試連線：

1
psql -h server_ip -U myuser -d mydb

安全性建議

使用強密碼：確保所有資料庫使用者都設定強密碼
限制連線來源：在 pg_hba.conf 中只允許必要的 IP 連線
定期更新：保持 PostgreSQL 和系統套件為最新版本
備份資料：定期使用 pg_dump 或 pg_dumpall 進行備份
監控日誌：定期檢查 PostgreSQL 日誌檔案

常用維護指令

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 備份單一資料庫
pg_dump mydb > mydb_backup.sql

# 備份所有資料庫
pg_dumpall > all_databases_backup.sql

# 還原資料庫
psql mydb < mydb_backup.sql

# 查看連線狀態
sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"

結語

本文介紹了在 Ubuntu 22.04 上安裝和設定 PostgreSQL 的基本步驟。PostgreSQL 是一個功能強大且穩定的資料庫系統，適合各種規模的應用程式使用。建議進一步閱讀官方文件以了解更多進階功能和最佳實踐。