📘 PostgreSQL 入門 （介紹 -> 安裝 -> 配置）
官方地址：https://www.postgresql.org/
中文官方文檔：http://www.postgres.cn/docs/14/
一：?? PostgreSQL 簡介
1、引言：為什么選擇 PostgreSQL？
在數(shù)據(jù)庫世界里，有這樣一款系統(tǒng)，它不僅開源免費，還擁有強大的功能、良好的兼容性、極高的擴展性，并被全球無數(shù)企業(yè)和開發(fā)者所信賴——它就是 PostgreSQL，簡稱 Postgres。
如果你曾經(jīng)使用過 MySQL、SQL Server 或 Oracle，那么 PostgreSQL 將是你通往更高階數(shù)據(jù)庫技術的理想選擇。它被譽為“世界上最先進的開源關系型數(shù)據(jù)庫系統(tǒng)”。
2、PostgreSQL 是什么？
PostgreSQL 是一個功能強大、符合 SQL 標準的對象-關系型數(shù)據(jù)庫系統(tǒng)。
-
關系型數(shù)據(jù)庫：數(shù)據(jù)以表格形式組織，通過 SQL 查詢語言操作。
-
對象特性：支持繼承、復雜數(shù)據(jù)類型、用戶自定義函數(shù)等面向對象特性。
-
開源免費：采用 PostgreSQL 許可證（類似于 MIT），可自由用于商業(yè)和個人項目。
-
跨平臺支持：Windows、Linux、macOS 皆可運行。
3、PostgreSQL 的核心優(yōu)勢
? 1. 標準合規(guī) + 功能強大
-
完全支持 SQL 標準（SQL:2011）
-
支持事務、外鍵、視圖、觸發(fā)器、存儲過程等
-
支持 多版本并發(fā)控制（MVCC），無鎖讀寫更高效
? 2. 豐富的數(shù)據(jù)類型
除了常規(guī)的 INTEGER、VARCHAR、BOOLEAN 外，PostgreSQL 還支持：
-
JSON/JSONB：高效處理結構化文檔數(shù)據(jù) -
ARRAY：數(shù)組類型，可直接存儲多個值 -
UUID,HSTORE,CIDR,GEOMETRY等專業(yè)類型
? 3. 高度可擴展
PostgreSQL 支持動態(tài)加載擴展模塊，比如：
-
PostGIS：空間地理信息處理 -
pg_trgm：字符串相似度搜索 -
full-text search：全文索引與搜索
開發(fā)者還可以創(chuàng)建自己的：
-
數(shù)據(jù)類型
-
操作符
-
索引方法
-
存儲過程（支持多種語言，如 PL/pgSQL、Python、SQL、C）
? 4. 安全性和可靠性
-
支持細粒度權限控制（基于用戶、角色）
-
提供備份/恢復工具（如
pg_dump、pg_restore） -
支持 WAL（Write-Ahead Logging）機制，保障數(shù)據(jù)完整性
4、PostgreSQL 應用場景
PostgreSQL 被廣泛應用于以下領域：
-
?? 企業(yè)級系統(tǒng)：銀行、ERP、CRM 系統(tǒng)
-
?? Web 開發(fā)：與 Django、Ruby on Rails、Node.js 等框架集成
-
?? 大數(shù)據(jù)分析：結合 TimescaleDB 做時間序列數(shù)據(jù)分析
-
?? 地理信息系統(tǒng)（GIS）：配合 PostGIS 處理地圖和位置數(shù)據(jù)
5、PostgreSQL 與其他數(shù)據(jù)庫的比較
| 特性 | PostgreSQL | MySQL | Oracle | SQLite |
|---|---|---|---|---|
| 開源免費 | ? | ? | ?（商業(yè)許可） | ? |
| 標準兼容性 | ????? | ??? | ???? | ?? |
| 擴展性 | ????? | ?? | ???? | ? |
| 支持事務 | ? | ? | ? | ? |
| JSON 支持 | 高效原生支持 | 基本支持 | 中等 | 基本支持 |
| MVCC 并發(fā)控制 | ?（強大） | 部分支持 | ?（強大） | ? |
二：?? PostgreSQL安裝
1、環(huán)境說明
在開始 PostgreSQL 的學習和實踐之前，首先明確我們的系統(tǒng)環(huán)境和目標版本：
-
操作系統(tǒng)：Ubuntu 22.04 LTS
-
PostgreSQL 版本：14.18，14.15
PostgreSQL 的安裝和配置方式會隨著系統(tǒng)和版本略有不同，本筆記以 Ubuntu 系統(tǒng)為例，演示兩種常見安裝方式：apt 安裝 和 源碼編譯安裝。
2、安裝方式詳解
方法一：使用 APT 包管理器安裝（推薦）-version14.18
Ubuntu 默認的軟件源通常包含 PostgreSQL，但版本可能不是最新。為確保安裝的是你指定的 14.18 版本，推薦添加官方 PostgreSQL 倉庫。
步驟：
# 1. 更新系統(tǒng)軟件包索引
sudo apt update
# 2. 安裝必要工具
sudo apt install wget ca-certificates gnupg lsb-release
# 3. 自動化存儲庫配置：
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
復制腳本
# 4. 要手動配置 Apt 存儲庫，請按照以下步驟操作：
> 導入存儲庫簽名密鑰：
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
> 創(chuàng)建存儲庫配置文件：
. /etc/os-release
sudo sh -c “echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list”
# 5. 更新軟件包列表：
sudo apt update
# 6. 安裝最新版本的 PostgreSQL：
# 如果您想要特定版本，請使用“postgresql-17”或類似版本代替“postgresql”
sudo apt -y install postgresql-14
服務管理命令：
- 確保服務啟動
# 啟動 PostgreSQL
sudo systemctl start postgresql
# 設置開機自啟
sudo systemctl enable postgresql
# 查看服務狀態(tài)
sudo systemctl status postgresql
安裝完成后，確認版本：
默認情況下，Postgres使用稱為“角色”的概念來處理認證和授權。這些在某些方面類似于常規(guī)的Unix/Linux用戶和組。安裝過程中創(chuàng)建了一個名為postgres的用戶賬戶，它與默認的Postgres角色相關聯(lián)。
有幾種方法可以使用這個賬戶訪問Postgres。一種是切換到服務器上的postgres賬戶：
su postgres
然后你可以通過運行以下命令訪問Postgres提示符：
psql
這將登錄到PostgreSQL提示符，從這里你可以立即與數(shù)據(jù)庫管理系統(tǒng)交互。
要退出PostgreSQL提示符，運行以下命令：
\q
這將帶你回到postgres Linux命令提示符。要返回到你的常規(guī)系統(tǒng)用戶，運行exit命令：
exit
另一種連接到Postgres提示符的方法是直接使用sudo運行psql命令：
sudo -u postgres psql
這將直接登錄到Postgres，無需中間的bash shell。
同樣，你可以通過運行以下命令退出交互式Postgres會話：
\q
查看版本
postgres@zabbix-proxy:~$ psql --version
psql (PostgreSQL) 14.18 (Ubuntu 14.18-1.pgdg22.04+1)
參考鏈接：https://www.postgresql.org/download/linux/ubuntu/
方法二：源碼編譯安裝（適用于自定義或特殊需求）-version14.15
源碼安裝適合需要自定義配置、調(diào)試源碼或安裝非官方補丁的高級用戶。
步驟：
# 1. 安裝依賴工具和庫
sudo apt update
sudo apt install build-essential libreadline-dev zlib1g-dev flex bison gcc make pkg-config liblz4-dev zlib1g-dev libssl-dev
# 2. 下載源碼（指定版本）
wget https://ftp.postgresql.org/pub/source/v14.15/postgresql-14.15.tar.gz
# 3. 解壓源碼包
tar -xvzf postgresql-14.15.tar.gz
cd postgresql-14.15
# 4. 配置編譯參數(shù)
./configure --prefix=/app/postgresql/
# 5. 編譯并安裝
make -j$(nproc)
sudo make install
創(chuàng)建運行用戶
sudo useradd -m -s /bin/bash postgres
sudo passwd postgres
用戶添加sudo權限
cat <<EOF | sudo tee /etc/sudoers.d/postgres > /dev/null
postgres ALL=(ALL) ALL
EOF
創(chuàng)建存儲目錄
sudo mkdir -p /data/{pg_data,pg_wal}
sudo chown -R postgres. /data/{pg_data,pg_wal}
sudo chmod 700 /data/{pg_data,pg_wal}
設置環(huán)境變量
sudo su - postgres
cat <<'EOF' | tee -a ~/.bashrc >>/dev/null
# Postgresql Service Environment variable
export PGHOME=/app/postgresql
export PGDATA=/data/pg_data
export LD_LIBRARY=$PGHOME/lib
export PATH=$PGHOME/bin:$PATH
EOF
source ~/.bashrc
初始化數(shù)據(jù)庫
sudo su - postgres
initdb --pgdata=/data/pg_data --waldir=/data/pg_wal --encoding=UTF8 --locale=en_US.utf8 --auth=scram-sha-256 --username=postgres --pwprompt
postgres@sql:~$ initdb --pgdata=/data/pg_data --waldir=/data/pg_wal --encoding=UTF8 --locale=en_US.utf8 --auth=scram-sha-256 --username=postgres --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
Enter new superuser password:#輸入密碼
Enter it again:#輸入密碼
fixing permissions on existing directory /data/pg_data ... ok
fixing permissions on existing directory /data/pg_wal ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
pg_ctl -D /data/pg_data -l logfile start
初始化報錯
initdb: error: invalid locale name "en_US.utf8"
Ubuntu解決辦法：安裝language-pack-en包，查看所有 locales 方法locale --all-locales
- 初始化報錯
initdb: error: invalid locale name "zh_CN.utf8"
Ubuntu解決辦法：安裝language-pack-kde-zh-hans包，查看所有 locales 方法locale --all-locales
添加postgresql配置
cat <<'EOF' | tee -a $PGDATA/postgresql.conf > /dev/null
# 服務監(jiān)聽相關配置
listen_addresses = '*' # 監(jiān)聽地址
port = 5432 # 監(jiān)聽端口
# 設置時區(qū)
timezone = 'Asia/Shanghai'
log_timezone = 'Asia/Shanghai'
# WAL相關配置
wal_level = replica # 該參數(shù)決定了系統(tǒng)生成和記錄 WAL 日志的程度 logical>replica>minimal
full_page_writes = on # 控制是否在 WAL（Write-Ahead Logging）記錄中包括整個頁面的副本，而不僅僅是被修改的部分【優(yōu)點】可以提高崩潰恢復的可靠性【缺點】WAL日志的空間使用高
wal_log_hints = on # 控制是否在 WAL 中記錄對表的索引的更改操作的提示信息【優(yōu)點】有助于在崩潰時加速恢復過程【缺點】增加 WAL 記錄的生成量
# 服務日志配置參數(shù)
log_directory = 'logs' # 日志目錄 $PGDATA/log_directory
logging_collector = on # 開啟日志采集
log_destination = stderr # 日志輸出格式
log_filename = 'postgresql-%Y-%m-%d.log' # 日志文件名稱
log_truncate_on_rotation = on # 可重用同名日志文件
log_rotation_age = 1d # 多長時間輪換文件記錄日志
log_rotation_size = 0 # 日志輪換大小的參數(shù)，當設置為0時不輪換
# 記錄慢SQL日志設置
log_statement = all # 需設置跟蹤所有語句，否則只能跟蹤出錯信息，設置跟蹤的語句類型，有4種類型：none(默認), ddl, mod, all。跟蹤所有語句時可設置為 "all"。
log_min_duration_statement = 5000 # milliseconds,記錄執(zhí)行5秒及以上的語句，跟蹤慢查詢語句，單位為毫秒。如設置 5000，表示日志將記錄執(zhí)行5秒以上的SQL語句
EOF
啟動服務
1. 手工啟動服務
pg_ctl start
2. systemd啟動
cat <<'EOF' | sudo tee /usr/lib/systemd/system/postgresql.service >> /dev/null
[Unit]
Description=Postgresql Server
Documentation=https://www.postgresql.org/docs/14/index.html
Wants=network.service
After=network.service
[Service]
Type=forking
User=postgres
Environment=PGDATA=/data/pg_data
ExecStart=/app/postgresql/bin/pg_ctl -D ${PGDATA} start
ExecStop=/app/postgresql/bin/pg_ctl -D ${PGDATA} stop
ExecReload=/app/postgresql/bin/pg_ctl -D ${PGDATA} reload
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo systemctl start postgresql
查看日志
cd $PGDATA/logs/
tail -f postgresql-`date "+%Y-%m-%d"`.log
檢查版本
postgres@sql:~$ psql --version
psql (PostgreSQL) 14.15
小結：選擇哪種安裝方式？
| 安裝方式 | 優(yōu)點 | 缺點 | 適合人群 |
|---|---|---|---|
| apt 安裝 | 快速、簡單、自動配置服務 | 不易定制，依賴系統(tǒng)版本 | 初學者、生產(chǎn)環(huán)境 |
| 編譯安裝 | 靈活，可自定義路徑/功能 | 復雜，需要手動配置環(huán)境變量等 | 高級用戶、開發(fā)調(diào)試 |
三：?? PostgreSQL配置
1、核心配置文件匯總
| 配置文件 | 主要功能 | 默認路徑 | 備注 |
|---|---|---|---|
postgresql.conf |
主配置文件，控制服務器行為 | [數(shù)據(jù)目錄]/ |
最重要的配置文件 |
pg_hba.conf |
客戶端連接認證配置 | [數(shù)據(jù)目錄]/ |
控制訪問權限 |
pg_ident.conf |
用戶身份映射配置 | [數(shù)據(jù)目錄]/ |
用于OS用戶到DB用戶的映射 |
postgresql.auto.conf |
系統(tǒng)自動管理配置 | [數(shù)據(jù)目錄]/ |
優(yōu)先級最高 |
2、配置文件詳解
2.1 postgresql.conf - 主配置文件
2.1.1 連接與認證配置
# 監(jiān)聽設置
listen_addresses = '*' # 監(jiān)聽所有IP地址，也可指定具體IP
port = 5432 # 默認監(jiān)聽端口
# 連接限制
max_connections = 100 # 最大并發(fā)連接數(shù)
authentication_timeout = 1min # 認證超時時間
# 密碼加密
password_encryption = scram-sha-256 # 推薦使用強加密
2.1.2 資源與內(nèi)存配置
# 內(nèi)存設置
shared_buffers = 128MB # 共享緩沖區(qū)大小，建議為內(nèi)存的25%
work_mem = 4MB # 每個操作使用的內(nèi)存
maintenance_work_mem = 64MB # 維護操作使用的內(nèi)存
effective_cache_size = 1GB # 系統(tǒng)估計緩存大小
2.1.3 預寫式日志(WAL)配置
wal_level = replica # WAL級別：minimal, replica, logical
2.1.4 日志記錄配置
# 日志基礎設置
logging_collector = on # 日志開啟
log_destination = 'stderr'
log_directory = 'pg_log' # 日志路徑
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' #日志文件名
# 慢查詢?nèi)罩?log_min_duration_statement = 1000 # 記錄執(zhí)行超過1秒的查詢
2.2 pg_hba.conf - 客戶端認證配置
2.2.1 文件格式
# 類型 數(shù)據(jù)庫 用戶 地址 認證方法
TYPE DATABASE USER ADDRESS METHOD
-----------
示例：
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostgssenc DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnogssenc DATABASE USER ADDRESS METHOD [OPTIONS]
2.2.2 連接類型
-
local：Unix域套接字連接 -
host：TCP/IP連接（SSL和非SSL） -
hostssl：僅SSL加密的TCP/IP連接 -
hostnossl：僅非SSL的TCP/IP連接
2.2.3 認證方法
-
trust：無條件允許連接（??生產(chǎn)環(huán)境慎用） -
scram-sha-256：SCRAM-SHA-256密碼認證（推薦） -
md5：MD5加密密碼認證 -
ident：通過操作系統(tǒng)用戶身份認證 -
peer：使用操作系統(tǒng)用戶名進行peer認證
2.2.4 配置示例
# 允許本地所有用戶信任連接
local all all trust
# 允許本地用戶密碼認證
local all all scram-sha-256
# 允許任意地址所有用戶對所有數(shù)據(jù)庫，密碼認證 -》遠程連接
host all all 0.0.0.0 md5
# 允許網(wǎng)絡段用戶連接
host all all 192.168.1.0/24 scram-sha-256
# 允許特定用戶從特定IP連接特定數(shù)據(jù)庫
host mydb myuser 192.168.1.100/32 scram-sha-256
# SSL連接要求
hostssl all all 0.0.0.0/0 scram-sha-256
2.3 pg_ident.conf - 用戶映射配置
2.3.1 文件格式
# 映射名 系統(tǒng)用戶名 PostgreSQL用戶名
MAPNAME SYSTEM-USERNAME PG-USERNAME
2.3.2 配置示例
# 將操作系統(tǒng)用戶映射到數(shù)據(jù)庫用戶
mymap john john_db
mymap jane jane_db
2.4 postgresql.auto.conf
- 由
ALTER SYSTEM命令自動維護 - 優(yōu)先級高于
postgresql.conf - 不應手動編輯此文件
3、配置管理操作
3.1 查找配置文件位置
-- 查看數(shù)據(jù)目錄
SELECT setting FROM pg_settings WHERE name = 'data_directory';
-- 查看配置文件路徑
SELECT name, setting FROM pg_settings WHERE name LIKE '%config_file%';
3.2 配置生效方法
3.2.1 重新加載配置（無需重啟）
# 方法1：使用pg_ctl
pg_ctl reload -D $PGDATA
# 方法2：使用SQL命令
SELECT pg_reload_conf();
# 方法3：使用系統(tǒng)服務
sudo systemctl reload postgresql
3.2.2 重啟服務（完全重啟）
# 方法1：使用pg_ctl
pg_ctl restart -D $PGDATA
# 方法2：使用系統(tǒng)服務
sudo systemctl restart postgresql
3.3 配置查看與修改
3.3.1 查看當前配置
-- 查看所有參數(shù)
SELECT name, setting, unit, context FROM pg_settings;
-- 查看特定參數(shù)
SELECT name, setting FROM pg_settings WHERE name = 'shared_buffers';
3.3.2 修改配置
-- 方法1：使用ALTER SYSTEM（寫入postgresql.auto.conf）
ALTER SYSTEM SET shared_buffers = '256MB';
-- 方法2：直接編輯postgresql.conf文件
-- 然后重新加載或重啟服務
4、安全最佳實踐
4.1 認證安全
-
避免使用
trust認證：在生產(chǎn)環(huán)境中禁止使用 -
使用強密碼加密：優(yōu)先選擇
scram-sha-256 -
限制網(wǎng)絡訪問：僅允許必要的IP段連接
-
使用SSL加密：對遠程連接啟用SSL
4.2 文件權限
# 配置文件權限設置
chmod 600 $PGDATA/postgresql.conf
chmod 600 $PGDATA/pg_hba.conf
chmod 600 $PGDATA/pg_ident.conf
5、故障排查技巧
5.1 連接問題排查
-
檢查
pg_hba.conf中的認證規(guī)則 -
確認
listen_addresses設置正確 -
驗證端口是否開放和防火墻設置
5.2 配置驗證
# 檢查配置語法
postgres --check-config
# 查看生效的配置
psql -c "SELECT name, setting, source FROM pg_settings WHERE source != 'default';"
6、重要提醒
-
修改前備份：修改配置文件前務必進行備份
-
測試環(huán)境驗證：重要變更先在測試環(huán)境驗證
-
變更記錄：記錄所有配置變更及原因
-
監(jiān)控影響：配置變更后監(jiān)控系統(tǒng)性能
浙公網(wǎng)安備 33010602011771號