关系型数据库PostgreSQL

第一章：PostgreSQL 简介与安装

1.1 PostgreSQL 概述

概念名称	说明	注意事项
PostgreSQL	开源对象-关系型数据库系统，支持 SQL 标准、扩展性强、ACID 兼容	不是 MySQL 的分支，而是独立发展的数据库项目
ACID 特性	原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）	PostgreSQL 默认事务隔离级别为 READ COMMITTED
MVCC	多版本并发控制，允许多个事务同时读写而不阻塞	避免了读写锁，但需定期 VACUUM 清理旧版本
扩展机制	支持通过 CREATE EXTENSION 添加功能（如 PostGIS、uuid-ossp）	扩展需预先安装到系统或通过包管理器提供
社区与生态	由全球开发者社区维护，无商业公司主导	企业可选择 EDB（EnterpriseDB）等提供商业支持

1.2 安装 PostgreSQL（Linux / Windows / macOS）

步骤名称	操作细节	注意事项
Linux (Ubuntu/Debian) 安装	sudo apt update && sudo apt install postgresql postgresql-contrib	安装后自动创建 postgres 用户和默认集群
Linux (RHEL/CentOS) 安装	sudo dnf install postgresql-server postgresql-contrib（或 yum）	需启用并启动服务：sudo systemctl enable --now postgresql；然后运行 sudo postgresql-setup --initdb
Windows 安装	从官网 https://www.postgresql.org/download/windows/ 下载图形化安装程序（如 EDB installer）	安装过程中会设置超级用户密码和端口（默认 5432）
macOS 安装（Homebrew）	brew install postgresql	安装后需手动初始化：initdb /opt/homebrew/var/postgres（Apple Silicon 路径）
验证安装	终端执行 psql --version 或 pg_config --version	若命令未找到，需检查 PATH 或使用完整路径（如 /usr/lib/postgresql/16/bin/psql）

1.3 初始化数据库集群

操作名称	操作细节	注意事项
initdb 命令	initdb -D /path/to/data/directory	必须由 postgres 用户或具有写权限的用户执行；例如：sudo -u postgres initdb -D /var/lib/postgresql/16/main
指定编码与区域	initdb -D /data --encoding=UTF8 --locale=en_US.UTF-8	推荐使用 UTF8 编码，避免后续字符集问题
数据目录结构	包含 base/（数据库文件）、global/（全局表）、pg_wal/（WAL 日志）、postgresql.conf、pg_hba.conf 等	不要手动修改数据目录内文件，除非明确知道后果
重复初始化风险	若目标目录非空，initdb 会拒绝执行	需先清空目录或指定新路径
自动初始化	某些发行版（如 Ubuntu）在安装包时自动调用 initdb 创建默认集群	可通过 pg_lsclusters 查看已存在集群

1.4 启动与停止服务

操作名称	操作细节	注意事项
使用 systemctl（Linux）	启动：sudo systemctl start postgresql；停止：sudo systemctl stop postgresql；状态：sudo systemctl status postgresql	服务名可能带版本号，如 postgresql@16-main
使用 pg_ctl（通用）	启动：pg_ctl -D /data/dir start；停止：pg_ctl -D /data/dir stop -m fast；重启：pg_ctl -D /data/dir restart	-m 模式：smart（默认）、fast、immediate；immediate 会中断所有连接
Windows 服务管理	通过”服务”管理器启动/停止 PostgreSQL 服务，或使用 net start postgresql-x64-16	服务名取决于安装时指定的版本和位数
检查端口监听	ss -tulnp | grep 5432 或 lsof -i :5432	若未监听，检查 postgresql.conf 中 listen_addresses 和 port
自动启动配置	Linux：sudo systemctl enable postgresql；Windows：安装时默认设为自动启动	禁用自动启动：sudo systemctl disable postgresql

第二章：命令行工具基础

2.1 psql 命令行客户端入门

方法/命令名称	语法/说明	用途	代码示例	注意事项
启动 psql（默认连接）	psql	以当前系统用户身份连接同名数据库	psql	需存在同名数据库和用户；通常需先切换到 postgres 用户
指定数据库连接	psql -d dbname	连接指定数据库	psql -d mydb	若用户未指定，默认使用当前系统用户名
指定用户连接	psql -U username -d dbname	以指定用户身份连接数据库	psql -U alice -d mydb	可能提示输入密码，取决于 pg_hba.conf 配置
退出 psql	\q 或 Ctrl+D	退出 psql 会话	\q	不会提交未完成的事务，如有 BEGIN 未 COMMIT 会被回滚
显示帮助	\?	查看 psql 元命令帮助	\?	与 SQL 帮助 \h 不同，\? 是 psql 自身命令

2.2 连接数据库（本地与远程）

方法/命令名称	语法/说明	用途	代码示例	注意事项
本地 socket 连接	psql -h /var/run/postgresql -d dbname	使用 Unix 域套接字连接（Linux/macOS）	psql -h /tmp -d postgres	默认路径因发行版而异，Ubuntu 通常为 /var/run/postgresql
TCP/IP 本地连接	psql -h localhost -p 5432 -U user -d dbname	通过 TCP 连接本地实例	psql -h 127.0.0.1 -p 5432 -U postgres -d testdb	需确保 postgresql.conf 中 listen_addresses 包含 localhost
远程连接	psql -h remote_ip -p port -U user -d dbname	连接远程 PostgreSQL 服务器	psql -h 192.168.1.100 -p 5432 -U appuser -d appdb	需配置：1. postgresql.conf: listen_addresses = '*'；2. pg_hba.conf: 添加 host 记录；3. 防火墙开放端口
使用 .pgpass 文件免密	在 ~/.pgpass 中写入 host:port:database:username:password	自动填充密码，避免交互输入	192.168.1.100:5432:appdb:appuser:mypassword	文件权限必须为 600（chmod 600 ~/.pgpass），否则被忽略
环境变量连接	设置 PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD	通过环境变量指定连接参数	export PGUSER=alice && psql	PGPASSWORD 存在安全风险，建议仅用于脚本且及时清除

2.3 常用 psql 元命令（\d, \l, \c 等）

元命令	语法/说明	用途	代码示例	注意事项
\l / \list	列出所有数据库	查看集群中所有数据库	\l	显示数据库名、所有者、编码、访问权限等
\c / \connect	\c [dbname] [username] [host] [port]	切换当前连接的数据库或用户	\c mydb alice	若省略参数，保持原值；可仅切换数据库：\c newdb
\d	列出当前数据库中的表、视图、序列等	快速浏览对象	\d	等价于 \dtvms（table, view, materialized view, sequence）
\dt	列出所有表	仅显示普通表	\dt	可加模式限定：\dt public.*
\d table_name	显示表结构（列、类型、约束）	查看表定义	\d users	显示主键、外键、默认值、是否为空等
\dv	列出视图	查看用户定义视图	\dv	不包括系统视图
\du	列出角色/用户	查看用户及其权限	\du	显示超级用户、创建 DB 权限等
\dn	列出模式（schema）	查看数据库中的 schema	\dn	默认有 public schema
\x	切换扩展显示模式（横向/纵向）	使宽表结果更易读	\x → SELECT * FROM large_table;	再次执行 \x 可关闭；也可用 \x auto 自动切换
\timing	开启/关闭 SQL 执行时间显示	性能调试	\timing → SELECT count(*) FROM logs;	显示毫秒级耗时

2.4 脚本执行与输出重定向

操作名称	语法/说明	用途	代码示例	注意事项
执行 SQL 脚本文件	psql -f script.sql -d dbname	批量运行 SQL 语句	psql -U postgres -d mydb -f init.sql	脚本中可用 \echo 输出信息
从标准输入执行	cat script.sql | psql -d dbname	管道方式执行	echo "SELECT version();" | psql -d postgres	适用于 shell 脚本中动态生成 SQL
输出查询结果到文件	psql -d dbname -c "SQL" -o output.txt	将结果保存为文件	psql -d sales -c "SELECT * FROM orders;" -o orders.txt	默认包含标题和边框；可用 -t（元组模式）去除
静默模式（无提示）	psql -q -f script.sql	抑制启动/结束消息	psql -q -d test -f setup.sql	适合自动化脚本，避免日志污染
设置输出格式	使用 \pset 或命令行选项控制格式	控制 CSV、对齐、分隔符等	psql -d db -c "SELECT a,b FROM t;" --csv -o data.csv	常用选项：--csv（CSV 格式）、-t（仅数据，无标题）、-A（非对齐模式）
错误处理（ON_ERROR_STOP）	在脚本开头加 \set ON_ERROR_STOP on	遇错立即退出	\set ON_ERROR_STOP on\nCREATE TABLE ...	默认 psql 遇错继续执行，可能掩盖问题
导出为 COPY 格式	psql -d db -c "\copy (SELECT ...) TO 'file.csv' WITH CSV HEADER"	客户端侧导出（无需 superuser）	\copy (SELECT id,name FROM users) TO '/tmp/users.csv' WITH CSV HEADER	\copy 是 psql 命令，不同于服务器端的 COPY

第三章：数据库与用户管理

3.1 创建与删除数据库

方法/命令名称	语法/说明	用途	代码示例	注意事项
CREATE DATABASE	CREATE DATABASE dbname [WITH [OWNER [=] user] [TEMPLATE [=] template] [ENCODING [=] encoding] ...];	创建新数据库	CREATE DATABASE myapp WITH OWNER = alice ENCODING = 'UTF8';	需具有 CREATEDB 权限；不能在事务块中执行
DROP DATABASE	DROP DATABASE [IF EXISTS] dbname;	删除数据库	DROP DATABASE IF EXISTS myapp;	数据库必须为空（无连接）；无法删除当前连接的数据库
使用模板创建	指定 TEMPLATE 选项	基于现有模板数据库克隆结构	CREATE DATABASE testdb WITH TEMPLATE = template0;	template0 是干净模板，template1 可能被修改过；避免使用非标准模板
查看数据库列表	\l 或查询 pg_database	列出所有数据库	SELECT datname FROM pg_database;	系统数据库包括 postgres、template0、template1
连接限制	在 CREATE DATABASE 中指定 CONNECTION LIMIT n	限制该数据库的最大并发连接数	CREATE DATABASE limited_db CONNECTION LIMIT 5;	超级用户不受此限制

3.2 用户与角色管理

方法/命令名称	语法/说明	用途	代码示例	注意事项
CREATE ROLE	CREATE ROLE name [WITH] option [...];	创建角色（用户是带 LOGIN 的角色）	CREATE ROLE alice WITH LOGIN PASSWORD 'secret123' CREATEDB;	默认不带 LOGIN，仅为权限组；需显式指定 LOGIN 才可连接
CREATE USER	CREATE USER name [WITH option];	创建可登录用户（等价于 CREATE ROLE ... LOGIN）	CREATE USER bob WITH PASSWORD 'pass';	实际是 CREATE ROLE 的别名，官方推荐统一用 CREATE ROLE
ALTER ROLE	ALTER ROLE name [WITH] option; 或 ALTER ROLE name RENAME TO newname;	修改角色属性或重命名	ALTER ROLE alice VALID UNTIL '2027-01-01';	可修改密码、有效期、权限等；不能修改超级用户为非超级用户（除非自身是超级用户）
DROP ROLE	DROP ROLE [IF EXISTS] name;	删除角色	DROP ROLE IF EXISTS bob;	角色不能拥有任何对象（表、函数等），否则需先转移所有权或删除对象
查看角色列表	\du 或查询 pg_roles	列出所有角色及其属性	SELECT rolname, rolsuper, rolcreatedb FROM pg_roles;	包含系统角色（如 pg_signal_backend）
设置默认权限	ALTER ROLE name SET parameter = value;	为角色设置会话级 GUC 参数	ALTER ROLE alice SET search_path TO myapp, public;	影响该用户后续所有会话的默认行为

3.3 权限控制（GRANT / REVOKE）

方法/命令名称	语法/说明	用途	代码示例	注意事项
GRANT on DATABASE	GRANT {CONNECT | CREATE | TEMPORARY} ON DATABASE db TO role;	授予数据库级权限	GRANT CONNECT, CREATE ON DATABASE myapp TO alice;	默认 PUBLIC 有 CONNECT 和 TEMPORARY 权限；可 REVOKE PUBLIC 撤销
GRANT on SCHEMA	GRANT {USAGE | CREATE} ON SCHEMA schema TO role;	授予模式级权限	GRANT USAGE ON SCHEMA public TO bob;	USAGE 允许访问模式内对象；CREATE 允许在模式中建表
GRANT on TABLE	GRANT {SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER} ON TABLE tbl TO role;	授予表级 DML/DDL 权限	GRANT SELECT, INSERT ON TABLE users TO app_role;	可批量授权：GRANT SELECT ON ALL TABLES IN SCHEMA public TO role;
GRANT on SEQUENCE	GRANT {USAGE | SELECT | UPDATE} ON SEQUENCE seq TO role;	授予序列权限	GRANT USAGE ON SEQUENCE user_id_seq TO app_role;	SERIAL 类型自动创建序列，需单独授权
GRANT on FUNCTION	GRANT EXECUTE ON FUNCTION func(...) TO role;	授予函数执行权限	GRANT EXECUTE ON FUNCTION get_user(int) TO webuser;	函数需指定完整参数签名
REVOKE	REVOKE privilege ON object FROM role [CASCADE];	撤销权限	REVOKE INSERT ON TABLE logs FROM guest;	默认 RESTRICT 模式，若权限被转授则失败；CASCADE 会级联撤销
查看权限	查询 information_schema.table_privileges 或 \dp tablename	检查对象权限分配	\dp users	\dp 显示 ACL（访问控制列表），格式如 alice=arwdDxt/postgres

3.4 默认数据库与模板数据库

概念/操作名称	说明	用途	代码示例	注意事项
postgres 数据库	安装后默认存在的数据库	用于管理操作、工具连接、初始连接	psql -U postgres -d postgres	建议保留，不要删除；许多工具默认连接它
template0	只读模板数据库，编码和区域固定	作为干净模板创建新数据库	CREATE DATABASE clean_db WITH TEMPLATE = template0;	不可连接、不可修改；用于恢复标准环境
template1	默认模板数据库，可被用户修改	新数据库默认从此克隆	CREATE DATABASE newdb;（等价于 WITH TEMPLATE template1）	若 template1 被污染（如安装了扩展），会影响所有新库
重置 template1	用 template0 覆盖 template1	修复被修改的默认模板	DROP DATABASE template1; CREATE DATABASE template1 WITH TEMPLATE = template0;	需先断开所有连接；操作需谨慎
自定义模板数据库	任意数据库设为模板（datistemplate = true）	快速部署预配置环境	UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template';	需超级用户权限；模板数据库不能被普通用户删除

第四章：SQL 基础操作

4.1 数据类型与表结构设计

数据类型名称	语法/说明	用途	代码示例	注意事项
INTEGER / INT	4 字节整数，范围 -2147483648 到 +2147483647	存储普通整数 ID、计数等	user_id INTEGER	可用 SERIAL 自动递增（实际是 INTEGER + 序列）
BIGINT	8 字节整数	存储大数值（如时间戳、海量计数）	created_at BIGINT	对应 SERIAL8（BIGSERIAL）
TEXT	可变长字符串，无长度限制	存储任意长度文本	description TEXT	比 VARCHAR 更灵活；性能差异在现代 PostgreSQL 中极小
VARCHAR(n)	最大 n 字符的可变长字符串	需限制长度的文本字段	email VARCHAR(255)	超长会报错；n=0 表示无限制（等同 TEXT）
CHAR(n)	固定长度字符串，不足补空格	兼容旧系统或固定码（如国家代码）	country_code CHAR(2)	一般不推荐，浪费存储且易引发比较问题
BOOLEAN	TRUE / FALSE / NULL	存储逻辑标志	is_active BOOLEAN	可用 ‘t’/‘f’、1/0 等输入，但输出为 true/false
TIMESTAMP	不带时区的时间戳	记录事件发生时间（本地视角）	login_time TIMESTAMP	默认精度为微秒；可用 TIMESTAMP(0) 精确到秒
TIMESTAMPTZ	带时区的时间戳	存储 UTC 时间，自动转换客户端时区	event_time TIMESTAMPTZ	实际存储为 UTC；显示时按当前 timezone GUC 转换
DATE	日期（年-月-日）	仅需日期部分	birth_date DATE	支持日期运算（如 CURRENT_DATE + INTERVAL '1 day'）
NUMERIC(p,s)	任意精度十进制数	金融、科学计算等需精确小数场景	price NUMERIC(10,2)	p=总位数，s=小数位；比 FLOAT8 更精确但稍慢
UUID	128 位通用唯一标识符	分布式系统主键	id UUID PRIMARY KEY	需启用扩展：CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; 生成：uuid_generate_v4()
JSONB	二进制存储的 JSON，支持索引和查询	存储半结构化数据	metadata JSONB	推荐用 JSONB 而非 JSON（后者为文本，无索引支持）

4.2 CREATE / ALTER / DROP TABLE

方法/命令名称	语法/说明	用途	代码示例	注意事项
CREATE TABLE	CREATE TABLE table_name (col type [constraints], ...);	创建新表	CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL);	可加 IF NOT EXISTS 避免报错
主键约束	PRIMARY KEY 或 UNIQUE NOT NULL	定义唯一标识行	id INTEGER PRIMARY KEY	自动创建唯一索引
外键约束	FOREIGN KEY (col) REFERENCES parent(col)	建立表间引用关系	user_id INTEGER REFERENCES users(id)	被引用表必须有主键或唯一约束；默认 ON DELETE NO ACTION
默认值	DEFAULT value	插入时未指定则使用默认	created_at TIMESTAMP DEFAULT NOW()	可用函数（如 NOW()）、常量或表达式
ALTER TABLE ADD COLUMN	ALTER TABLE tbl ADD COLUMN col type [DEFAULT val];	增加新列	ALTER TABLE users ADD COLUMN email TEXT;	加带 DEFAULT 的列在大表上可能锁表（PG ≥11 优化）
ALTER TABLE DROP COLUMN	ALTER TABLE tbl DROP COLUMN col [CASCADE];	删除列	ALTER TABLE users DROP COLUMN temp_flag;	CASCADE 会删除依赖该列的对象（如视图）
ALTER TABLE ALTER COLUMN TYPE	ALTER TABLE tbl ALTER COLUMN col TYPE new_type [USING expr];	修改列数据类型	ALTER TABLE logs ALTER COLUMN message TYPE TEXT;	若类型不兼容，需提供 USING 表达式转换
DROP TABLE	DROP TABLE [IF EXISTS] tbl [CASCADE];	删除表及其数据	DROP TABLE IF EXISTS temp_data;	CASCADE 会删除依赖对象（如外键、视图）；RESTRICT 为默认

4.3 INSERT / UPDATE / DELETE

方法/命令名称	语法/说明	用途	代码示例	注意事项
INSERT INTO	INSERT INTO tbl (col1, col2) VALUES (val1, val2);	插入单行或多行	INSERT INTO users (name, email) VALUES ('Alice', 'a@example.com');	可省略列名（需按表定义顺序提供所有值）
INSERT RETURNING	INSERT ... RETURNING * | cols;	返回插入后的行	INSERT INTO users (name) VALUES ('Bob') RETURNING id;	常用于获取自增 ID 或完整记录
UPDATE	UPDATE tbl SET col = val WHERE condition;	修改符合条件的行	UPDATE users SET email = 'new@a.com' WHERE id = 1;	必须带 WHERE，否则更新全表！
UPDATE RETURNING	UPDATE ... RETURNING * | cols;	返回更新后的行	UPDATE users SET last_login = NOW() WHERE id = 1 RETURNING *;	可用于审计或链式操作
DELETE FROM	DELETE FROM tbl WHERE condition;	删除符合条件的行	DELETE FROM sessions WHERE expired < NOW();	必须带 WHERE，否则清空整表！
DELETE RETURNING	DELETE ... RETURNING * | cols;	返回被删除的行	DELETE FROM logs WHERE created < '2020-01-01' RETURNING id;	可用于归档前捕获数据
UPSERT (ON CONFLICT)	INSERT ... ON CONFLICT (col) DO UPDATE SET ...	插入或冲突时更新	INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;	需目标列有唯一约束或索引；EXCLUDED 表示拟插入的值

4.4 SELECT 查询基础（WHERE, ORDER BY, LIMIT）

方法/命令名称	语法/说明	用途	代码示例	注意事项
SELECT 列	SELECT col1, col2 FROM tbl;	查询指定列	SELECT name, email FROM users;	可用 * 表示所有列（不推荐用于生产）
WHERE 条件	WHERE expression	过滤行	SELECT * FROM users WHERE age >= 18;	支持 AND/OR/NOT、IN、BETWEEN、LIKE、IS NULL 等
ORDER BY	ORDER BY col [ASC|DESC] [, col2 ...]	排序结果	SELECT * FROM products ORDER BY price DESC, name ASC;	默认 ASC；NULL 值默认排最后（可用 NULLS FIRST/LAST 控制）
LIMIT / OFFSET	LIMIT n [OFFSET m]	分页或限制结果数量	SELECT * FROM logs ORDER BY ts DESC LIMIT 10 OFFSET 20;	OFFSET 效率低，大数据分页建议用游标或基于 ID
列别名	SELECT expr AS alias	重命名输出列	SELECT name AS full_name FROM users;	AS 可省略；别名可用于 ORDER BY
去重	SELECT DISTINCT col FROM tbl;	返回唯一值	SELECT DISTINCT country FROM users;	多列 DISTINCT 基于组合去重
聚合初探	SELECT COUNT(*), MAX(age) FROM users;	基础聚合	SELECT COUNT(*) FROM orders;	无 GROUP BY 时返回单行；NULL 值通常被忽略（COUNT(*) 除外）

第五章：高级 SQL 功能

5.1 聚合函数与 GROUP BY

方法/函数名称	语法/说明	用途	代码示例	注意事项
COUNT	COUNT(*) 或 COUNT(column)	统计行数；COUNT(*) 包含 NULL，COUNT(col) 忽略 NULL	SELECT department, COUNT(*) FROM employees GROUP BY department;	性能良好，常用于分组统计
SUM	SUM(numeric_column)	求和	SELECT SUM(salary) FROM employees;	仅适用于数值类型；全 NULL 返回 NULL
AVG	AVG(numeric_column)	求平均值	SELECT AVG(age) FROM users;	自动忽略 NULL 值；结果为 numeric 类型
MIN / MAX	MIN(col), MAX(col)	求最小/最大值	SELECT MIN(created_at), MAX(created_at) FROM logs;	适用于任意可排序类型（包括 TEXT、DATE）
GROUP BY	SELECT col, agg(...) FROM tbl GROUP BY col;	按列分组聚合	SELECT status, COUNT(*) FROM orders GROUP BY status;	SELECT 中非聚合列必须出现在 GROUP BY 中（除非使用 ANY_VALUE 等扩展）
HAVING	GROUP BY ... HAVING condition	对分组结果过滤	SELECT dept, AVG(sal) FROM emp GROUP BY dept HAVING AVG(sal) > 5000;	不能用 WHERE 替代；WHERE 过滤行，HAVING 过滤组
STRING_AGG	STRING_AGG(text_col, delimiter)	字符串拼接	SELECT dept, STRING_AGG(name, ', ') FROM emp GROUP BY dept;	可加 ORDER BY：STRING_AGG(name, ',' ORDER BY name)
ARRAY_AGG	ARRAY_AGG(col)	聚合成数组	SELECT user_id, ARRAY_AGG(tag) FROM user_tags GROUP BY user_id;	保留 NULL 值；结果为数组类型

5.2 JOIN 操作（INNER, LEFT, RIGHT, FULL）

JOIN 类型	语法/说明	用途	代码示例	注意事项
INNER JOIN	FROM a INNER JOIN b ON a.id = b.a_id	返回两表匹配的行	SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;	等价于 JOIN（省略 INNER）；不匹配行被丢弃
LEFT JOIN	FROM a LEFT JOIN b ON a.id = b.a_id	返回左表所有行，右表无匹配则为 NULL	SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id;	常用于”主表+可选附属信息”场景
RIGHT JOIN	FROM a RIGHT JOIN b ON a.id = b.a_id	返回右表所有行，左表无匹配则为 NULL	SELECT u.name, p.title FROM users u RIGHT JOIN posts p ON u.id = p.author_id;	较少使用，多数可转为 LEFT JOIN
FULL OUTER JOIN	FROM a FULL JOIN b ON a.id = b.id	返回两表所有行，无匹配侧为 NULL	SELECT COALESCE(a.id, b.id), a.val, b.val FROM a FULL JOIN b ON a.id = b.id;	需处理两侧 NULL；性能较低，慎用于大表
USING 子句	FROM a JOIN b USING (common_col)	简化等值连接（列名相同）	SELECT * FROM orders JOIN customers USING (customer_id);	结果中 common_col 仅出现一次
NATURAL JOIN	FROM a NATURAL JOIN b	自动按同名列连接	SELECT * FROM table1 NATURAL JOIN table2;	极不推荐！易因新增同名列导致逻辑错误
CROSS JOIN	FROM a CROSS JOIN b	笛卡尔积（无条件连接）	SELECT * FROM colors CROSS JOIN sizes;	结果行数 = 行数(a) × 行数(b)，谨慎使用

5.3 子查询与 CTE（WITH 语句）

方法/结构名称	语法/说明	用途	代码示例	注意事项
标量子查询	(SELECT col FROM tbl WHERE ...)	返回单值，用于 SELECT/WHERE	SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;	必须返回 0 或 1 行 1 列；否则报错
行子查询	(SELECT col1, col2 FROM tbl LIMIT 1)	返回一行多列	SELECT * FROM products WHERE (price, category) = (SELECT MAX(price), 'Electronics' FROM products);	需用括号包裹；可用于比较元组
EXISTS 子查询	WHERE EXISTS (SELECT 1 FROM tbl WHERE ...)	判断是否存在匹配行	SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);	子查询通常用 SELECT 1，性能优于 COUNT
IN 子查询	WHERE col IN (SELECT key FROM tbl)	判断是否在结果集中	SELECT * FROM products WHERE id IN (SELECT product_id FROM promotions);	若子查询含 NULL，IN 可能返回 UNKNOWN（导致行被过滤）
CTE（公用表表达式）	WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name;	定义临时结果集，提升可读性	WITH active_users AS (SELECT id FROM users WHERE last_login > NOW() - INTERVAL '7 days') SELECT COUNT(*) FROM orders WHERE user_id IN (SELECT id FROM active_users);	CTE 仅在当前语句有效；可递归（见下）
递归 CTE	WITH RECURSIVE cte AS (base_case UNION ALL recursive_part) ...	处理层次/图结构数据	WITH RECURSIVE tree AS (SELECT id, parent_id FROM nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id) SELECT * FROM tree;	必须用 UNION ALL；需有终止条件，否则无限循环
多 CTE	WITH a AS (...), b AS (...) SELECT ...	定义多个临时结果集	WITH sales AS (SELECT ...), targets AS (SELECT ...) SELECT s.region, s.amount / t.goal FROM sales s JOIN targets t ON s.region = t.region;	各 CTE 间可用逗号分隔；后定义的可引用前面的

5.4 窗口函数（Window Functions）

函数/结构名称	语法/说明	用途	代码示例	注意事项
OVER() 子句	func() OVER ([PARTITION BY cols] [ORDER BY cols] [frame_clause])	定义窗口计算范围	SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) FROM employees;	无 PARTITION BY 则整个结果集为一个窗口
ROW_NUMBER()	ROW_NUMBER() OVER (ORDER BY col)	为每行分配唯一序号	SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students;	即使值相同，序号也不同
RANK()	RANK() OVER (ORDER BY col)	跳跃排名（相同值同排名，后续跳过）	SELECT name, score, RANK() OVER (ORDER BY score DESC) FROM students;	如分数 100,100,90 → 排名 1,1,3
DENSE_RANK()	DENSE_RANK() OVER (ORDER BY col)	密集排名（相同值同排名，后续连续）	SELECT ..., DENSE_RANK() OVER (ORDER BY score DESC) FROM students;	如 100,100,90 → 排名 1,1,2
LAG / LEAD	LAG(col, offset, default) OVER (ORDER BY time)	访问前/后行数据	SELECT ts, value, LAG(value) OVER (ORDER BY ts) AS prev_value FROM sensor_data;	offset 默认为 1；default 默认为 NULL
FIRST_VALUE / LAST_VALUE	FIRST_VALUE(col) OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)	获取窗口首/末值	SELECT id, group_id, FIRST_VALUE(id) OVER (PARTITION BY group_id ORDER BY id) FROM items;	LAST_VALUE 默认窗口可能不包含全部行，需显式指定 frame
窗口帧（Frame Clause）	ROWS BETWEEN start AND end 或 RANGE BETWEEN ...	精确控制窗口行范围	SUM(sales) OVER (ORDER BY month ROWS 2 PRECEDING)	常用：UNBOUNDED PRECEDING、CURRENT ROW、N PRECEDING/FOLLOWING
累积和	SUM(col) OVER (ORDER BY time ROWS UNBOUNDED PRECEDING)	计算累计值	SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) AS running_total FROM daily_sales;	必须指定 ORDER BY，否则 SUM 作用于整个分区

第六章：索引与性能优化

6.1 索引类型（B-tree, Hash, GIN, GiST）

索引类型	语法/说明	用途	代码示例	注意事项
B-tree	默认索引类型；支持等值、范围、排序查询	通用场景（如主键、外键、WHERE col = ? 或 col > ?）	CREATE INDEX idx_users_email ON users(email);	支持 ASC/DESC、NULLS FIRST/LAST；适用于大多数数据类型
Hash	仅支持等值查询（=）	高速精确匹配（不支持排序或范围）	CREATE INDEX idx_status_hash ON orders USING HASH(status);	仅在 PostgreSQL ≥10 中可用于 WAL 日志（可复制）；不支持多列
GIN（Generalized Inverted Index）	倒排索引；适用于数组、JSONB、全文搜索	查询包含特定元素的复合结构	CREATE INDEX idx_tags_gin ON articles USING GIN(tags);；CREATE INDEX idx_meta_gin ON products USING GIN(metadata JSONB_PATH_OPS);	插入/更新开销大；适合读多写少场景
GiST（Generalized Search Tree）	支持多种”相似性”操作（如几何、全文搜索、范围类型）	全文检索、地理空间（PostGIS）、范围重叠查询	CREATE INDEX idx_docs_gist ON documents USING GiST(content_tsvector);；CREATE INDEX idx_events_gist ON events USING GiST(period tsrange);	可能返回假阳性（需 recheck）；比 GIN 更节省空间
BRIN（Block Range Index）	按数据块记录 min/max 值	超大表且数据物理有序（如时间序列日志）	CREATE INDEX idx_logs_brin ON logs USING BRIN(created_at);	极小存储开销；仅当数据按索引列物理排序时高效
SP-GiST	空间分区 GiST；支持非平衡树结构	IP 路由、电话号码前缀、k-d 树等	CREATE INDEX idx_ips_spgist ON network USING SPGiST(ip inet);	使用场景较 niche；需明确匹配操作符类

6.2 创建与管理索引

方法/命令名称	语法/说明	用途	代码示例	注意事项
CREATE INDEX	CREATE [UNIQUE] INDEX name ON table (col [ASC|DESC] [NULLS FIRST/LAST]);	创建普通索引	CREATE INDEX idx_orders_user_id ON orders(user_id);	默认并发构建会锁表（禁止写）；大表建议用 CONCURRENTLY
CREATE INDEX CONCURRENTLY	CREATE INDEX CONCURRENTLY name ON table (col);	不阻塞写操作地创建索引	CREATE INDEX CONCURRENTLY idx_users_name ON users(name);	执行时间更长；若失败会留下 INVALID 索引，需手动 DROP
多列索引	CREATE INDEX ON tbl (col1, col2);	支持组合条件查询	CREATE INDEX idx_orders_status_date ON orders(status, created_at);	最左前缀原则：WHERE status = ? AND created_at > ? 可用；仅 created_at 不能用
部分索引（Partial Index）	CREATE INDEX ... WHERE condition;	仅索引满足条件的行	CREATE INDEX idx_active_users ON users(id) WHERE active = true;	节省空间和维护开销；查询需包含相同 WHERE 条件才能命中
表达式索引	CREATE INDEX ON tbl ((lower(email)));	对函数/表达式结果建索引	CREATE INDEX idx_users_email_lower ON users((lower(email)));	查询必须使用相同表达式：WHERE lower(email) = 'a@example.com'
DROP INDEX	DROP INDEX [IF EXISTS] name;	删除索引	DROP INDEX IF EXISTS idx_old;	删除后查询可能变慢；无回滚机制
重建索引	REINDEX INDEX name; 或 REINDEX TABLE tbl;	修复膨胀或损坏的索引	REINDEX INDEX idx_users_email;	会锁表（禁止读写）；PG ≥12 可用 REINDEX CONCURRENTLY（实验性）
查看索引	\d table_name 或查询 pg_indexes	列出表的索引	SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';	\d+ table 显示更详细信息（包括索引大小）

6.3 EXPLAIN 与查询计划分析

方法/命令名称	语法/说明	用途	代码示例	注意事项
EXPLAIN	EXPLAIN SELECT ...;	显示查询执行计划（估算）	EXPLAIN SELECT * FROM users WHERE email = 'a@example.com';	默认输出为”计划树”，显示节点类型、成本估算
EXPLAIN ANALYZE	EXPLAIN (ANALYZE, BUFFERS) SELECT ...;	实际执行并返回运行时统计	EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM logs;	会真实执行查询！生产环境慎用；显示实际时间、行数、I/O 等
输出字段含义	cost=startup..total, rows=估算行数, width=平均行宽	理解计划成本模型	Seq Scan on users (cost=0.00..12.50 rows=1 width=64)	cost 单位为磁盘页读取；rows 是优化器估算，可能不准
常见节点类型	Seq Scan（全表扫描）、Index Scan、Bitmap Heap Scan、Hash Join、Nested Loop	识别执行策略	Index Scan using idx_users_email on users	Index Scan 适合少量行；Bitmap Scan 适合中等数量
缓冲区信息	BUFFERS 选项显示 shared/local/temp read/hit	分析 I/O 性能	Buffers: shared hit=10 read=5	hit 表示缓存命中；read 表示从磁盘读取
触发器与函数	EXPLAIN 不显示触发器执行；函数内 SQL 需单独分析	注意黑盒操作	—	复杂函数可能隐藏性能瓶颈
强制禁用索引	SET enable_indexscan = off;	测试不同计划（调试用）	SET enable_seqscan = off; EXPLAIN SELECT ...;	仅当前会话有效；切勿用于生产

6.4 VACUUM 与 ANALYZE

方法/命令名称	语法/说明	用途	代码示例	注意事项
VACUUM	VACUUM [table];	回收死元组空间（MVCC 产生）	VACUUM users;	不释放空间给 OS（仅标记为可复用）；可并发执行
VACUUM FULL	VACUUM FULL [table];	重建表并释放空间给 OS	VACUUM FULL logs;	锁表（禁止读写）；耗时长；仅在空间极度紧张时使用
ANALYZE	ANALYZE [table [(column [, ...])]];	收集表统计信息供优化器使用	ANALYZE orders;	自动由 autovacuum 触发；也可手动运行以更新统计
VACUUM ANALYZE	VACUUM ANALYZE table;	同时回收空间并更新统计	VACUUM ANALYZE sessions;	常用于大批量 UPDATE/DELETE 后
autovacuum	后台进程自动执行 VACUUM/ANALYZE	防止表膨胀和统计过期	无需手动调用	通过 postgresql.conf 配置：autovacuum_vacuum_scale_factor 等
查看膨胀情况	查询 pg_stat_user_tables 的 n_dead_tup	判断是否需手动 VACUUM	SELECT schemaname, tablename, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 1000;	死元组过多会导致查询变慢、WAL 增长
FREEZE	VACUUM FREEZE table;	防止事务 ID 回卷（wraparound）	VACUUM FREEZE large_table;	通常由 autovacuum 自动处理；极端情况下需手动干预
成本延迟控制	vacuum_cost_delay, vacuum_cost_limit	控制 VACUUM I/O 资源占用	在 postgresql.conf 中调整	避免 VACUUM 影响在线业务性能

第七章：事务与并发控制

7.1 事务基础（BEGIN, COMMIT, ROLLBACK）

方法/命令名称	语法/说明	用途	代码示例	注意事项
BEGIN / START TRANSACTION	BEGIN; 或 START TRANSACTION;	显式开启一个事务块	BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1;	PostgreSQL 中每个语句默认自动提交；显式 BEGIN 禁用自动提交
COMMIT	COMMIT;	提交当前事务，使更改持久化	COMMIT;	提交后无法回滚；WAL 日志确保持久性
ROLLBACK	ROLLBACK;	回滚当前事务，丢弃所有更改	ROLLBACK;	可在任何时刻执行；包括因错误自动回滚
SAVEPOINT	SAVEPOINT name;	在事务内设置保存点	BEGIN; INSERT INTO logs VALUES (1); SAVEPOINT sp1; DELETE FROM temp; ROLLBACK TO sp1; COMMIT;	允许部分回滚；可嵌套多个保存点
ROLLBACK TO SAVEPOINT	ROLLBACK TO SAVEPOINT name;	回滚到指定保存点	ROLLBACK TO sp1;	保存点之后的操作被撤销，但事务仍继续
隐式事务	单条 SQL 语句自动作为事务执行	简化简单操作	UPDATE users SET name = 'Alice' WHERE id = 1;	即使未显式 BEGIN，也满足 ACID；失败则整条语句回滚
事务结束行为	执行 COMMIT/ROLLBACK 后自动退出事务模式	返回自动提交状态	—	下一条语句又成为独立事务

7.2 隔离级别（READ COMMITTED, SERIALIZABLE 等）

隔离级别	语法/说明	用途	代码示例	注意事项
READ COMMITTED	默认隔离级别；只能看到已提交数据	平衡一致性与并发性	BEGIN ISOLATION LEVEL READ COMMITTED;	同一事务内多次 SELECT 可能返回不同结果（不可重复读）
REPEATABLE READ	事务内首次读取建立快照，后续读一致	防止不可重复读	BEGIN ISOLATION LEVEL REPEATABLE READ;	PostgreSQL 中实际提供快照隔离（SI），强于标准 RR
SERIALIZABLE	最高隔离；防止幻读、写偏斜等	严格串行化执行效果	BEGIN ISOLATION LEVEL SERIALIZABLE;	若检测到冲突（如写偏斜），会报错 serialization_failure，需重试
设置方式	SET TRANSACTION ISOLATION LEVEL level;（在 BEGIN 后首条语句）	动态指定当前事务隔离级别	BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;	也可在连接时通过 default_transaction_isolation GUC 设置默认值
幻读处理	SERIALIZABLE 级别下，新增行导致的幻读会被检测并拒绝提交	保证逻辑一致性	—	READ COMMITTED 和 REPEATABLE READ 不阻止幻读（但 PG 的 RR 实际避免了多数幻读）
写偏斜（Write Skew）	两个事务读取相同数据集，各自修改互斥部分，导致不一致	SERIALIZABLE 可检测此类冲突	例如：两人同时检查库存充足后各减 1，总和超限	仅 SERIALIZABLE 能可靠防止；需应用层重试机制

7.3 锁机制与死锁处理

锁类型/操作名称	语法/说明	用途	代码示例	注意事项
行级锁（FOR UPDATE）	SELECT ... FOR UPDATE;	获取行排他锁，防止其他事务修改	SELECT * FROM accounts WHERE id = 1 FOR UPDATE;	锁持续到事务结束；阻塞其他 FOR UPDATE/FOR NO KEY UPDATE
行级锁（FOR SHARE）	SELECT ... FOR SHARE;	获取行共享锁，允许读但阻塞写	SELECT * FROM products WHERE id = 100 FOR SHARE;	其他事务可 FOR SHARE，但不能 FOR UPDATE
FOR NO KEY UPDATE	SELECT ... FOR NO KEY UPDATE;	弱于 FOR UPDATE，不阻塞 KEY SHARE	SELECT * FROM orders WHERE id = 123 FOR NO KEY UPDATE;	适用于不修改主键/唯一键的更新
FOR KEY SHARE	SELECT ... FOR KEY SHARE;	弱共享锁，仅阻塞修改主键/唯一键	SELECT * FROM users WHERE id = 1 FOR KEY SHARE;	允许并发 UPDATE 非键列
表级锁（LOCK TABLE）	LOCK TABLE tbl IN MODE MODE;	显式获取表锁	LOCK TABLE orders IN EXCLUSIVE MODE;	ACCESS EXCLUSIVE（如 ALTER TABLE）会阻塞所有其他操作；MODE: ACCESS SHARE, ROW EXCLUSIVE, SHARE, EXCLUSIVE, ACCESS EXCLUSIVE
查看锁信息	查询 pg_locks 和 pg_stat_activity	诊断阻塞或死锁	SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON ...	被阻塞会话的 wait_event_type = 'Lock'
死锁检测	自动检测循环等待并终止一个事务	避免永久阻塞	—	报错：deadlock detected；应用需捕获并重试
避免死锁策略	按固定顺序访问表/行	减少死锁概率	总是先更新 accounts 表再更新 logs 表	无法完全避免，但可大幅降低发生率

7.4 MVCC（多版本并发控制）原理

概念名称	说明	用途	注意事项
元组可见性	每行（元组）包含 xmin（创建事务 ID）、xmax（删除/更新事务 ID）	判断当前事务是否能看到该行	事务只能看到：xmin ≤ 当前事务 ID 且 xmax = 0 或 xmax ≥ 当前事务 ID（且未提交）
快照（Snapshot）	事务开始时记录活跃事务 ID 集合	确定哪些修改对本事务不可见	快照在 READ COMMITTED 下每条语句刷新，REPEATABLE READ/SERIALIZABLE 下整个事务固定
版本链	更新/删除不立即覆盖原行，而是保留旧版本并标记 xmax	支持历史版本读取	旧版本由 VACUUM 清理；未清理前占用空间（“膨胀”）
无读写阻塞	读操作不加锁，写操作不阻塞读	高并发读写性能	写操作之间仍可能冲突（如更新同一行）
事务 ID 回卷（Wraparound）	32 位事务 ID 循环使用（约 42 亿）	防止 ID 耗尽	需定期 FREEZE（将 xmin 设为 FrozenTransactionId）；autovacuum 自动处理
CLOG（Commit Log）	记录每个事务的提交状态（in-progress / committed / aborted）	判断 xmax 对应事务是否已提交	存储在 pg_xact 目录；影响可见性判断
HOT（Heap-Only Tuples）	若更新未改变索引列，则新版本与旧版本在同一页面	减少索引更新开销	提升 UPDATE 性能；需 fillfactor < 100 预留空间

第八章：备份与恢复

8.1 逻辑备份（pg_dump / pg_restore）

方法/命令名称	语法/说明	用途	代码示例	注意事项
pg_dump（纯 SQL 格式）	pg_dump -U user -d dbname > backup.sql	导出数据库为可读 SQL 脚本	pg_dump -U postgres myapp > myapp_20260202.sql	默认包含 CREATE TABLE、INSERT 等；不含角色和表空间
pg_dump（自定义格式）	pg_dump -Fc -f backup.dump dbname	生成压缩、可选择性恢复的二进制格式	pg_dump -Fc -f myapp.dump myapp	必须用 pg_restore 恢复；支持并行、按表恢复
pg_dumpall	pg_dumpall > cluster.sql	备份整个集群（含角色、表空间、所有数据库）	pg_dumpall -U postgres > full_cluster.sql	包含全局对象；恢复时需先创建角色
pg_restore	pg_restore -U user -d dbname backup.dump	从自定义/目录格式恢复	pg_restore -U postgres -d myapp myapp.dump	可加 -j 4 并行恢复（仅自定义/目录格式支持）
仅结构备份	pg_dump -s -d dbname	仅导出 DDL（无数据）	pg_dump -s myapp > schema.sql	用于迁移表结构
仅数据备份	pg_dump -a -d dbname	仅导出 INSERT 语句（无 DDL）	pg_dump -a myapp > data.sql	需目标库已有相同结构
指定模式/表	pg_dump -n schema_name 或 -t table_name	按需备份部分对象	pg_dump -t users -t orders myapp > partial.sql	支持通配符：-t 'user*'
一致性保证	pg_dump 默认在单事务中运行（--serializable-deferrable 可选）	确保备份数据一致	pg_dump --serializable-deferrable -d myapp	大库备份期间仍允许读写

8.2 物理备份（文件系统级 + WAL）

方法/操作名称	语法/说明	用途	代码示例	注意事项
基础物理备份	停库后直接复制整个数据目录（$PGDATA）	快速完整备份	systemctl stop postgresql && cp -r /var/lib/postgresql/16/main /backup/pg_base	必须停库（或使用 pg_basebackup）；恢复时需替换整个数据目录
pg_basebackup	pg_basebackup -D /backup/dir -Fp -Xs -P -R	在线物理备份（无需停库）	pg_basebackup -h localhost -U replicator -D /backup/base -Fp -Xs -P -R	需配置 replication 权限；-R 自动生成 recovery.signal 和 primary_conninfo
备份格式	-Fp（plain，目录）或 -Ft（tar）	控制输出格式	pg_basebackup -Ft -z -D /backup/tar	-Fp 更易管理；-Ft 适合网络传输
WAL 包含选项	-Xs（stream，实时流 WAL）、-Xf（fetch，备份后获取）	确保备份可恢复到一致状态	—	-Xs 推荐，避免备份期间 WAL 被清理
恢复物理备份	替换 $PGDATA 目录，启动 PostgreSQL	从物理备份还原实例	rm -rf /var/lib/postgresql/16/main && cp -r /backup/base /var/lib/postgresql/16/main && systemctl start postgresql	需确保权限正确（通常属主为 postgres）；版本必须完全兼容
表空间处理	pg_basebackup 自动处理符号链接	备份含表空间的实例	—	表空间路径需在目标机存在或重定向
备份验证	启动备份副本并连接测试	确认备份可用	pg_ctl -D /backup/base start && psql -h /tmp -d postgres -c "SELECT version();"	定期验证是良好实践

8.3 持续归档与时间点恢复（PITR）

概念/操作名称	语法/说明	用途	代码示例	注意事项
WAL 归档配置	在 postgresql.conf 中设置：archive_mode = on；archive_command = 'cp %p /wal_archive/%f'	将 WAL 文件持续复制到归档位置	archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'	%p=WAL 路径，%f=文件名；命令返回 0 表示成功
基准备份 + WAL	先做 pg_basebackup，再持续归档 WAL	实现任意时间点恢复	—	基准备份是 PITR 起点；WAL 必须连续
恢复目标配置	创建 recovery.signal，并在 postgresql.conf 或 postgresql.auto.conf 中设置：restore_command = 'cp /wal_archive/%f %p'；recovery_target_time = '2026-02-01 12:00:00'	指定恢复到的时间/事务/LSN	recovery_target_name = 'before_deploy'（配合 pg_create_restore_point）	恢复完成后自动删除 recovery.signal
创建还原点	SELECT pg_create_restore_point('name');	标记可恢复的命名点	SELECT pg_create_restore_point('pre_upgrade');	便于精确回滚到关键操作前
时间线（Timeline）	每次 PITR 后生成新 timeline	防止 WAL 覆盖历史分支	恢复后 WAL 文件名含 timeline ID（如 00000002.history）	多次 PITR 需管理多 timeline WAL
恢复后提升	pg_ctl promote 或创建 promote.signal	将恢复中的实例转为主库	touch $PGDATA/promote.signal	结束只读恢复模式，开始接受写入
归档失败处理	若 archive_command 失败，WAL 不会被清理	防止数据丢失	—	需监控归档命令日志；磁盘满会导致数据库暂停

8.4 备份策略与自动化脚本

策略/操作名称	说明	用途	代码示例（简化）	注意事项
全量 + 增量组合	每周一次 pg_basebackup（全量），每日 pg_dump（逻辑增量）	平衡恢复速度与存储成本	0 2 * * 0 /backup/full.sh；0 2 * * 1-6 /backup/daily.sh	逻辑备份适合小表；物理备份适合大库
WAL 归档保留	保留至少 7 天 WAL + 最近 2 个基准备份	支持 PITR 回溯窗口	find /wal_archive -mtime +7 -delete	需计算 WAL 生成速率，避免空间耗尽
自动化脚本要素	1. 错误检查；2. 日志记录；3. 备份验证；4. 清理旧备份	提高可靠性	if ! pg_dump mydb > /b/mydb.sql; then echo "Fail" >> /var/log/backup.log; exit 1; fi	使用 set -e 确保脚本遇错退出
加密与传输	使用 gpg 加密、rsync/scp 传输到异地	满足安全与容灾要求	pg_dump mydb | gzip | gpg -c --passphrase-file key > mydb.sql.gz.gpg	密钥需安全保管；考虑使用 Vault 或 KMS
监控与告警	检查备份文件大小、时间、日志错误	及早发现备份失败	if [ $(stat -c%s /b/latest.dump) -lt 1000 ]; then alert "Backup too small"; fi	集成到 Prometheus/Zabbix 等监控系统
测试恢复流程	每季度执行完整恢复演练	验证备份有效性	在隔离环境：pg_ctl -D /test_restore start && psql -d testdb -c "SELECT COUNT(*) FROM critical_table;"	”未测试的备份等于没有备份”
使用 pgBackRest / Barman	第三方工具提供企业级备份管理	简化 PITR、压缩、加密、远程存储	pgbackrest --stanza=mydb backup	推荐用于生产环境；比原生工具更健壮

第九章：扩展与高级功能

9.1 扩展安装与管理（CREATE EXTENSION）

方法/命令名称	语法/说明	用途	代码示例	注意事项
CREATE EXTENSION	CREATE EXTENSION [IF NOT EXISTS] extension_name [WITH SCHEMA schema_name];	在当前数据库中启用扩展	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";	扩展必须已安装到 PostgreSQL 的 share/extension/ 目录
DROP EXTENSION	DROP EXTENSION [IF EXISTS] extension_name [CASCADE];	卸载扩展及其对象	DROP EXTENSION "hstore";	CASCADE 会删除依赖该扩展的对象（如使用 hstore 的表）
查看已安装扩展	\dx 或查询 pg_extension	列出当前数据库启用的扩展	SELECT extname, extversion FROM pg_extension;	不同数据库可启用不同扩展
常用官方扩展	uuid-ossp（UUID 生成）、pgcrypto（加密）、hstore（键值存储）、postgis（地理空间）	提供额外数据类型与函数	CREATE EXTENSION postgis;	部分扩展需操作系统包（如 postgresql-contrib）
扩展脚本位置	$PGSHARE/extension/extension_name--version.sql	定义扩展安装逻辑	—	升级通过 ALTER EXTENSION UPDATE TO 'new_version';
扩展权限	通常仅超级用户可 CREATE/DROP	控制扩展安装安全	—	可通过 GRANT CREATE ON DATABASE db TO user; 允许非超级用户安装（PG ≥15）
扩展依赖	某些扩展依赖其他扩展或库	确保环境完整	CREATE EXTENSION postgis; 需 libgeos 等	安装前需确认系统依赖已满足

9.2 JSON / JSONB 数据类型与操作

方法/操作名称	语法/说明	用途	代码示例	注意事项
JSON vs JSONB	JSON：文本存储，保留空格和键顺序；JSONB：二进制存储，去重键、忽略空格	JSONB 支持索引和高效查询；JSON 适合日志等原始存储	metadata JSONB	推荐优先使用 JSONB
插入 JSONB	直接写 JSON 字面量或使用函数	存储半结构化数据	INSERT INTO logs (data) VALUES ('{"user": "alice", "action": "login"}'::JSONB);	自动验证 JSON 格式
-> 操作符	col->'key' 返回 JSONB 对象的字段（结果为 JSONB）	提取嵌套值	SELECT data->'user' FROM logs;	若 key 不存在，返回 NULL
->> 操作符	col->>'key' 返回文本形式的字段值	用于 WHERE 或显示	SELECT data->>'user' AS username FROM logs;	结果为 TEXT 类型
#> 操作符	col#>'{a,b,c}' 按路径提取嵌套值	多层嵌套访问	SELECT config#>'{database,host}' FROM apps;	路径为文本数组
? 操作符	'key' ? col 判断 JSONB 是否包含指定键	条件过滤	SELECT * FROM logs WHERE data ? 'error';	仅适用于 JSONB
@> 操作符	col @> '{"k":"v"}' 判断是否包含子集	包含查询	SELECT * FROM products WHERE specs @> '{"color": "red"}';	可配合 GIN 索引加速
jsonb_set	jsonb_set(target, path, new_value [, create_missing])	修改 JSONB 中的值	UPDATE logs SET data = jsonb_set(data, '{status}', '"processed"');	create_missing 默认 true
索引支持	GIN 索引加速包含、键存在等查询	提升 JSONB 查询性能	CREATE INDEX idx_logs_data ON logs USING GIN (data); 或更高效：CREATE INDEX idx_logs_data_ops ON logs USING GIN (data jsonb_path_ops);	jsonb_path_ops 仅支持 @>，但更小更快
转换函数	to_jsonb(row)、jsonb_build_object()	构造 JSONB	SELECT jsonb_build_object('id', id, 'name', name) FROM users;	row_to_json() 用于 JSON 类型

9.3 全文搜索（tsvector / tsquery）

方法/操作名称	语法/说明	用途	代码示例	注意事项
tsvector	文本的词位向量（经分词、去停用词、词干化）	存储可搜索的文档表示	body_ts tsvector	通常由触发器自动维护
tsquery	搜索条件（词位 + 布尔操作符）	表达查询意图	'search & engine'	支持 &（AND）、|（OR）、!（NOT）
to_tsvector	to_tsvector([config,] document)	将文本转为 tsvector	SELECT to_tsvector('english', 'The quick brown fox'); → 'brown':3 'fox':4 'quick':2	config 控制分词规则（如 english、simple）
to_tsquery	to_tsquery([config,] querytext)	将查询字符串转为 tsquery	SELECT to_tsquery('english', 'quick & fox');	自动词干化；不支持短语搜索
plainto_tsquery	plainto_tsquery([config,] phrase)	将自然语言转为 AND 查询	SELECT plainto_tsquery('english', 'quick brown fox'); → 'quick' & 'brown' & 'fox'	更适合用户输入
@@ 操作符	tsvector @@ tsquery	执行全文匹配	SELECT * FROM articles WHERE body_ts @@ to_tsquery('english', 'database');	返回 boolean
全文索引	GIN 或 GiST 索引加速 @@ 查询	提升搜索性能	CREATE INDEX idx_articles_ts ON articles USING GIN (body_ts);	GIN 更快但更大；GiST 更小但慢
高亮结果	ts_headline([config,] document, query [, options])	显示匹配片段并高亮	SELECT ts_headline('english', body, q) FROM articles, to_tsquery('english', 'search') q WHERE body_ts @@ q;	默认用 <b>...</b> 高亮
权重与排序	setweight(tsvector, 'A'/'B'/'C'/'D') + ts_rank()	按字段重要性排序	UPDATE articles SET title_ts = setweight(to_tsvector(title), 'A'), body_ts = setweight(to_tsvector(body), 'B');；SELECT *, ts_rank(title_ts || body_ts, q) FROM ...	权重 A 最高，D 最低
自定义配置	创建文本搜索配置（CREATE TEXT SEARCH CONFIGURATION）	支持中文、专业术语等	需集成外部分词器（如 zhparser）	官方不直接支持中文，需第三方扩展

9.4 分区表与继承表

方法/操作名称	语法/说明	用途	代码示例	注意事项
声明式分区（PG ≥10）	CREATE TABLE parent (...) PARTITION BY RANGE (col);；CREATE TABLE child PARTITION OF parent FOR VALUES FROM (min) TO (max);	按范围、列表或哈希自动路由数据	CREATE TABLE logs (ts TIMESTAMPTZ, msg TEXT) PARTITION BY RANGE (ts);；CREATE TABLE logs_202601 PARTITION OF logs FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');	推荐方式；优化器可分区裁剪（partition pruning）
列表分区	PARTITION BY LIST (status)	按离散值分区	CREATE TABLE orders PARTITION BY LIST (country);；CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');	适合状态、区域等枚举字段
哈希分区	PARTITION BY HASH (id)	均匀分布数据	CREATE TABLE users PARTITION BY HASH (id);；CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);	适合无自然分界的大表
继承表（旧方式）	CREATE TABLE child () INHERITS (parent);	手动实现分区（PG <10）	CREATE TABLE logs_202601 (CHECK (ts >= '2026-01-01' AND ts < '2026-02-01')) INHERITS (logs);	需手动创建触发器路由 INSERT；无分区裁剪优化
分区维护	添加/删除分区表	管理时间序列数据生命周期	DROP TABLE logs_202501;；CREATE TABLE logs_202603 PARTITION OF logs FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');	删除分区即删除数据；添加新分区无需锁主表
分区索引	在父表建索引，自动应用到所有分区	统一索引管理	CREATE INDEX ON logs (ts);	PG ≥11 支持；各分区有独立索引
查询父表	SELECT * FROM parent WHERE ...	自动扫描相关分区	SELECT COUNT(*) FROM logs WHERE ts > '2026-01-15';	优化器跳过无关分区（需条件含分区键）
分区限制	不支持主键跨分区、BEFORE ROW 触发器等	注意功能边界	—	主键必须包含分区键；外键需谨慎设计

第十章：命令行运维与监控

10.1 查看连接与活动会话（pg_stat_activity）

方法/查询名称	语法/说明	用途	代码示例	注意事项
查询 pg_stat_activity	SELECT * FROM pg_stat_activity;	查看所有当前会话状态	SELECT pid, usename, application_name, state, query FROM pg_stat_activity;	需要适当权限（通常超级用户或 pg_read_all_stats 角色）
活动会话过滤	WHERE state = 'active'	仅显示正在执行的查询	SELECT pid, query, now() - query_start AS duration FROM pg_stat_activity WHERE state = 'active';	state 可为 active、idle、idle in transaction 等
长事务检测	WHERE now() - xact_start > interval '5 minutes'	发现长时间未提交事务	SELECT pid, usename, xact_start, query FROM pg_stat_activity WHERE xact_start IS NOT NULL AND now() - xact_start > '10 min';	长事务阻塞 VACUUM，导致膨胀
终止会话	SELECT pg_terminate_backend(pid);	强制断开指定会话	SELECT pg_terminate_backend(12345);	会回滚该会话未提交事务；比 pg_cancel_backend 更彻底
取消查询	SELECT pg_cancel_backend(pid);	仅取消当前正在执行的查询	SELECT pg_cancel_backend(12345);	会话保持连接，可继续执行新语句
应用名识别	application_name 字段	识别客户端来源	SELECT application_name, COUNT(*) FROM pg_stat_activity GROUP BY application_name;	应用应设置有意义的 application_name（如 “web-api”）
等待事件分析	wait_event_type, wait_event	诊断性能瓶颈	SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event IS NOT NULL;	常见：Lock（锁等待）、IO（磁盘 I/O）、Client（等待客户端）

10.2 日志配置与查看

配置项/操作名称	语法/说明	用途	代码示例（postgresql.conf）	注意事项
logging_collector	logging_collector = on	启用日志写入文件（而非 stderr）	logging_collector = on	必须开启才能使用 log_directory 等选项
log_directory / log_filename	log_directory = 'log'；log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	控制日志存储位置与命名	log_directory = '/var/log/postgresql'	路径相对于 $PGDATA，除非是绝对路径
log_statement	log_statement = 'all' | 'ddl' | 'mod' | 'none'	记录 SQL 语句	log_statement = 'mod'	'mod' 记录 INSERT/UPDATE/DELETE；'all' 影响性能，慎用于生产
log_min_duration_statement	log_min_duration_statement = 1000	记录超过阈值（毫秒）的慢查询	log_min_duration_statement = 500	设为 0 相当于 log_statement='all'；常用于性能分析
log_connections / log_disconnections	log_connections = on；log_disconnections = on	记录客户端连接/断开事件	log_connections = on	有助于审计和排查连接泄漏
log_checkpoints	log_checkpoints = on	记录检查点详细信息	log_checkpoints = on	用于分析 I/O 峰值和恢复时间
查看日志文件	使用系统命令查看	实时监控日志	tail -f /var/lib/postgresql/16/main/log/postgresql-*.log	日志轮转由 log_rotation_age / log_rotation_size 控制
CSV 日志格式	log_destination = 'csvlog'	生成结构化日志便于分析	log_destination = 'stderr,csvlog'	CSV 文件含固定字段，适合导入 ELK 等系统

10.3 使用 pg_ctl 管理服务

命令/操作名称	语法/说明	用途	代码示例	注意事项
pg_ctl start	pg_ctl -D datadir start	启动 PostgreSQL 实例	pg_ctl -D /var/lib/postgresql/16/main start	需由 postgres 用户执行；默认前台启动，加 -l logfile 可后台
pg_ctl stop	pg_ctl -D datadir stop [-m mode]	停止实例	pg_ctl -D /data stop -m fast	-m 模式：smart（默认，等连接结束）、fast（回滚活跃事务）、immediate（立即终止，下次启动需恢复）
pg_ctl restart	pg_ctl -D datadir restart	重启服务	pg_ctl -D /data restart	等价于 stop + start
pg_ctl reload	pg_ctl -D datadir reload	重载配置（不中断服务）	pg_ctl -D /data reload	仅对支持 SIGHUP 的参数生效（如 shared_buffers 无效）
pg_ctl status	pg_ctl -D datadir status	检查服务是否运行	pg_ctl -D /data status	返回进程 PID 或提示未运行
pg_ctl promote	pg_ctl -D datadir promote	将备库提升为主库	pg_ctl -D /standby_data promote	用于流复制故障转移
指定用户/端口	通过环境变量或配置	多实例管理	export PGDATA=/data2 && pg_ctl start	或使用 -o "-p 5433" 指定额外启动参数
日志输出控制	-l logfile 指定日志文件	避免日志混杂	pg_ctl -D /data -l /var/log/pg.log start	若未指定，日志输出到终端或 systemd journal

10.4 常用监控命令与脚本

监控项/命令名称	语法/说明	用途	代码示例	注意事项
查看数据库大小	SELECT pg_size_pretty(pg_database_size('dbname'));	评估存储占用	SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;	pg_size_pretty() 将字节转为易读单位（MB/GB）
查看表大小	pg_relation_size('table') + pg_total_relation_size()	分析大表	SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::regclass)) FROM pg_tables WHERE schemaname = 'public';	pg_total_relation_size 包含索引和 TOAST
锁等待监控脚本	查询阻塞链	诊断锁竞争	SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.pid AS blocking_pid, blocking.query AS blocking_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked ON blocked.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON (blocking_locks.transactionid = blocked_locks.transactionid AND blocking_locks.pid != blocked_locks.pid) JOIN pg_catalog.pg_stat_activity blocking ON blocking.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;	定期运行可发现长期阻塞
自动清理状态	SELECT * FROM pg_stat_progress_vacuum;	监控 VACUUM 进度	SELECT pid, datname, relname, phase, heap_blks_scanned, heap_blks_total FROM pg_stat_progress_vacuum;	PG ≥13 支持
WAL 发送状态（主库）	SELECT * FROM pg_stat_replication;	监控流复制延迟	SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;	LSN 差值反映备库延迟
检查点统计	SELECT * FROM pg_stat_bgwriter;	分析 I/O 压力	SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint FROM pg_stat_bgwriter;	checkpoints_req 高表示 shared_buffers 不足
简单健康检查脚本	psql -c "SELECT 1;"	验证服务可用性	if psql -d postgres -c "SELECT 1;" >/dev/null 2>&1; then echo "OK"; else echo "DOWN"; fi	可集成到 Nagios/Zabbix
磁盘空间预警	df -h $PGDATA	防止磁盘写满	[ $(df -P /var/lib/postgresql | awk 'NR==2 {print $5}' | tr -d '%') -gt 80 ] && echo "WARNING: Disk usage > 80%"	建议磁盘使用率不超过 80%