一：基本概念和快速了解

PostgreSQL 的历史可以追溯到 1986 年，起源于加州大学伯克利分校（UC Berkeley）由 Michael Stonebraker 教授主导的 POSTGRES 项目。该项目的目标是研究下一代关系型数据库管理系统，探索面向对象和规则系统等新特性。发展时间线：PostgreSQL 的开发由全球志愿者社区维护，核心团队称为 PostgreSQL Global Development G

不过普通话一乙不改名

386人浏览 · 2026-03-04 11:20:11

不过普通话一乙不改名 · 2026-03-04 11:20:11 发布

PostgreSQL 入门完全指南：从零开始掌握企业级开源数据库

作者注：本文面向有一定编程基础、希望系统学习 PostgreSQL 的开发者和 DBA 初学者。文章将从历史背景出发，逐步深入到架构原理、实战操作和常见问题，带你完成 PostgreSQL 的入门之旅。

学习目标

理解 PostgreSQL 的核心概念和体系结构
掌握数据库、模式、表等基本对象的创建与管理
学会使用 psql 和图形化工具进行基本操作
理解 PostgreSQL 与其他数据库的区别和优势
完成第一个完整的数据库应用案例

2.1 PostgreSQL 是什么？

PostgreSQL 的历史与发展

PostgreSQL 的历史可以追溯到 1986 年，起源于加州大学伯克利分校（UC Berkeley）由 Michael Stonebraker 教授主导的 POSTGRES 项目。该项目的目标是研究下一代关系型数据库管理系统，探索面向对象和规则系统等新特性。

发展时间线：

年份	里程碑
1986	UC Berkeley 启动 POSTGRES 项目
1989	POSTGRES 4.2 版本发布，供外部使用
1994	Andrew Yu 和 Jolly Chen 添加 SQL 语言解释器，项目改名为 Postgres95
1996	更名为 PostgreSQL，以体现其对 SQL 标准的完整支持
1997	PostgreSQL 6.0 发布，开始社区驱动开发
2005	PostgreSQL 8.0 发布，首次原生支持 Windows
2010	PostgreSQL 9.0 发布，引入流复制（Streaming Replication）
2016	PostgreSQL 9.6 发布，支持并行查询
2017	PostgreSQL 10 发布，引入逻辑复制、分区表
2019	PostgreSQL 12 发布，显著优化分区表性能
2021	PostgreSQL 14 发布，改善高并发性能
2023	PostgreSQL 16 发布，进一步增强并行能力和逻辑复制
2024	PostgreSQL 17 发布，持续优化性能与功能

PostgreSQL 的开发由全球志愿者社区维护，核心团队称为 PostgreSQL Global Development Group（PGDG），每年发布一个主版本，支持周期为 5 年。

为什么选择 PostgreSQL

PostgreSQL 凭借以下核心优势，成为众多企业和开发者的首选：

1. 高度 SQL 标准兼容性

PostgreSQL 是目前对 SQL 标准（SQL:2016、SQL:2023）支持最完整的开源数据库之一，支持窗口函数、CTE（公用表表达式）、递归查询等高级特性。

2. 丰富的数据类型

除标准 SQL 类型外，PostgreSQL 原生支持 JSON/JSONB、数组、范围类型、几何类型、网络地址类型、全文检索类型等，极大扩展了数据建模能力。

3. 强大的扩展性

通过扩展机制（Extension），可以无缝集成：

PostGIS：地理空间数据处理
TimescaleDB：时序数据
pg_trgm：模糊匹配
pgvector：向量搜索（AI 场景）
Citus：分布式扩展

4. ACID 完整性

PostgreSQL 严格遵循 ACID（原子性、一致性、隔离性、持久性）原则，通过 MVCC 机制实现高并发下的数据一致性。

5. 企业级稳定性

被 Apple、Instagram、Spotify、Reddit、GitHub、Notion 等知名企业在核心业务中大规模使用，经历了极端生产环境的考验。

6. 完全开源，无许可费用

采用 PostgreSQL License（类 BSD 协议），允许商业使用、修改和分发，无任何版税。

PostgreSQL vs MySQL vs Oracle vs SQL Server

对比维度	PostgreSQL	MySQL	Oracle	SQL Server
许可证	开源（PostgreSQL License）	开源（GPL）/ 商业	商业	商业
费用	免费	免费/收费	昂贵	中等
SQL 标准	极高	中等	高	高
事务支持	完整 ACID	InnoDB 支持	完整	完整
JSON 支持	原生 JSONB（优秀）	JSON（较弱）	JSON	JSON
全文搜索	内置	有限	强	强
地理空间	PostGIS（领先）	基础	SDO_Geometry	空间类型
并发模型	MVCC	MVCC（InnoDB）	MVCC	锁 + MVCC
存储过程	PL/pgSQL 等多语言	SQL	PL/SQL	T-SQL
分区表	声明式分区（强）	支持	成熟	支持
复制	流复制/逻辑复制	binlog 复制	DataGuard	AlwaysOn
社区活跃度	非常活跃	活跃	商业支持	商业支持
Windows 支持	支持	支持	支持	原生

核心差异点：

vs MySQL：PostgreSQL 在 SQL 标准兼容性、复杂查询性能、数据类型丰富度上全面领先。MySQL 在简单 OLTP 场景下配置更简单，历史生态更广。
vs Oracle：PostgreSQL 是 Oracle 的免费平替，功能覆盖率超过 80%，迁移成本可控，节省巨额许可费用。
vs SQL Server：PostgreSQL 跨平台能力更强，成本更低，在 Linux 环境下性能更优。

PostgreSQL 的应用场景

PostgreSQL 适用于几乎所有数据库使用场景：

1. 传统 OLTP（在线事务处理）

电商订单系统
金融交易系统
用户账户管理

2. 数据分析（OLAP）

结合窗口函数、CTE 进行复杂报表
通过 Citus 扩展实现列存储分析

3. 地理信息系统（GIS）

地图应用（配合 PostGIS）
物流路径规划
位置服务

4. 时序数据

IoT 设备数据（配合 TimescaleDB）
监控指标存储

5. 全文检索

文档管理系统
内容搜索平台

6. AI / 向量数据库

配合 pgvector 实现向量相似度搜索
RAG（检索增强生成）应用的向量存储

7. JSON 文档存储

半结构化数据存储
配置管理
日志存储

开源社区与商业支持

社区资源：

官方网站：postgresql.org
邮件列表：pgsql-general、pgsql-hackers 等
年度会议：PGConf 系列（全球各地）
中文社区：PostgreSQL 中文社区（pgfans.cn）

商业支持提供商：

EnterpriseDB（EDB）：最大的 PostgreSQL 商业支持商，提供 EDB Postgres Advanced Server
Crunchy Data：提供企业版支持和 Kubernetes Operator
Amazon RDS / Aurora PostgreSQL：云托管服务
Google Cloud SQL / AlloyDB：Google 提供的兼容版本
Azure Database for PostgreSQL：微软云托管

2.2 核心概念速览

理解 PostgreSQL 的对象层次结构是入门的第一步。PostgreSQL 采用严格的层级对象模型：

PostgreSQL 实例（Cluster）
├── 数据库（Database）
│   ├── 模式（Schema）
│   │   ├── 表（Table）
│   │   ├── 视图（View）
│   │   ├── 序列（Sequence）
│   │   ├── 函数（Function）
│   │   └── 其他对象...
│   └── 扩展（Extension）
├── 表空间（Tablespace）
├── 用户/角色（User/Role）
└── 全局对象（数据库列表、角色列表等）

集群（Cluster）与实例（Instance）

在 PostgreSQL 中，集群（Cluster） 是一个特殊术语，指的是由单个 PostgreSQL 服务进程管理的一组数据库的集合，对应一个数据目录（PGDATA）。

⚠️ 注意：PostgreSQL 的"集群"与通常理解的"分布式集群"不同，它指的是单个 PostgreSQL 实例管理的所有数据库的总体，等同于"实例（Instance）"的概念。

关键特征：

一个集群对应一个 PGDATA 数据目录
一个集群运行一个 postgres 主进程
一个集群可以包含多个数据库
一个集群监听一个端口（默认 5432）
集群中的用户/角色是全局共享的

# 查看当前集群的数据目录
SHOW data_directory;

# 查看集群版本
SELECT version();

数据库（Database）

数据库是集群中逻辑隔离的命名空间单元。不同数据库之间完全隔离，无法直接跨数据库查询（需要借助 dblink 或 postgres_fdw 扩展）。

系统默认数据库：

数据库名	用途
`postgres`	默认数据库，通常用于管理操作
`template1`	新数据库的默认模板，在此创建的对象会复制到新库
`template0`	干净模板，不应修改，用于数据库恢复场景

-- 列出所有数据库
\l   -- psql 元命令
-- 或
SELECT datname, encoding, datcollate FROM pg_database;

-- 创建数据库
CREATE DATABASE myapp
    ENCODING 'UTF8'
    LC_COLLATE 'zh_CN.UTF-8'
    LC_CTYPE 'zh_CN.UTF-8'
    TEMPLATE template0;

-- 连接数据库
\c myapp

模式（Schema）

模式是数据库内部的命名空间，用于组织和隔离数据库对象（表、视图、函数等）。类似于操作系统中的目录概念。

默认模式：

public：每个数据库默认存在，所有用户默认可访问
pg_catalog：系统目录表
information_schema：SQL 标准信息视图
pg_temp_xxx：临时对象

模式搜索路径（search_path）：

-- 查看当前搜索路径
SHOW search_path;
-- 输出：$user, public

-- 设置搜索路径
SET search_path TO myschema, public;

-- 创建模式
CREATE SCHEMA hr;
CREATE SCHEMA finance;

-- 在指定模式中创建表
CREATE TABLE hr.employees (...);
CREATE TABLE finance.accounts (...);

-- 授权模式
GRANT USAGE ON SCHEMA hr TO analyst_role;

使用模式的优势：

多租户应用中为每个租户创建独立模式
按功能模块组织数据库对象
权限隔离（不同用户访问不同模式）

表（Table）、视图（View）、序列（Sequence）

表（Table）：数据存储的基本单位，由行（Row）和列（Column）组成。

-- 普通表
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       TEXT NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 临时表（会话结束自动删除）
CREATE TEMP TABLE temp_results AS SELECT * FROM users WHERE active = true;

-- 分区表
CREATE TABLE orders (
    id          BIGINT,
    order_date  DATE NOT NULL,
    amount      NUMERIC(12,2)
) PARTITION BY RANGE (order_date);

视图（View）：基于查询的虚拟表，不存储数据。

-- 普通视图
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE deleted_at IS NULL;

-- 物化视图（存储查询结果，需手动或定期刷新）
CREATE MATERIALIZED VIEW user_stats AS
SELECT date_trunc('day', created_at) AS day, COUNT(*) AS new_users
FROM users
GROUP BY 1;

-- 刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;

序列（Sequence）：自动生成递增数字的对象，常用于主键生成。

-- 创建序列
CREATE SEQUENCE user_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    CACHE 10;

-- 使用序列
SELECT nextval('user_id_seq');  -- 获取下一个值
SELECT currval('user_id_seq');  -- 获取当前值
SELECT lastval();               -- 获取本会话最后获取的序列值

-- SERIAL / BIGSERIAL 是序列的语法糖
-- BIGSERIAL 等价于：
-- CREATE SEQUENCE ... 并设置列的 DEFAULT nextval(...)

用户（User）与角色（Role）

在 PostgreSQL 中，用户（User）和角色（Role）本质上是同一概念，区别仅在于用户默认具有 LOGIN 权限，而角色默认没有。

-- 创建角色（无登录权限）
CREATE ROLE readonly;

-- 创建用户（有登录权限，等价于 CREATE ROLE ... LOGIN）
CREATE USER appuser WITH PASSWORD 'secure_password';

-- 角色可以赋予用户，实现权限继承
GRANT readonly TO appuser;

-- 查看所有角色
\du
-- 或
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin FROM pg_roles;

内置特殊角色（PostgreSQL 14+）：

角色名	权限
`pg_read_all_data`	读取所有表数据
`pg_write_all_data`	写入所有表数据
`pg_monitor`	访问监控视图
`pg_signal_backend`	发送信号给后端进程

表空间（Tablespace）

表空间定义了数据文件在文件系统中的物理存储位置，允许 DBA 将不同对象存储在不同的磁盘上，实现 I/O 分离。

-- 创建表空间（需要操作系统目录已存在）
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pgdata';

-- 在指定表空间创建表
CREATE TABLE hot_data (
    id BIGSERIAL PRIMARY KEY,
    data TEXT
) TABLESPACE fast_ssd;

-- 移动表到另一个表空间
ALTER TABLE hot_data SET TABLESPACE pg_default;

-- 查看所有表空间
\db
-- 或
SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;

默认表空间：

pg_default：默认表空间，对应 $PGDATA/base 目录
pg_global：用于共享系统目录

扩展（Extension）

扩展机制允许将第三方模块打包安装到 PostgreSQL 中，是 PostgreSQL 强大生态的基础。

-- 查看可用扩展
SELECT name, default_version, comment FROM pg_available_extensions ORDER BY name;

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS postgis;         -- 地理空间
CREATE EXTENSION IF NOT EXISTS pg_trgm;         -- 模糊匹配
CREATE EXTENSION IF NOT EXISTS uuid-ossp;       -- UUID 生成
CREATE EXTENSION IF NOT EXISTS pgcrypto;        -- 加密函数
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- SQL 统计

-- 查看已安装扩展
SELECT extname, extversion FROM pg_extension;

-- 更新扩展
ALTER EXTENSION postgis UPDATE TO '3.4.0';

-- 卸载扩展
DROP EXTENSION pg_trgm;

2.3 体系结构初探

深入理解 PostgreSQL 的内部架构，是进行性能调优和故障排查的基础。

进程架构：后台进程与用户进程

PostgreSQL 采用多进程架构（而非多线程），每个客户端连接对应一个独立的后端进程。

操作系统
└── postmaster（postgres 主进程，PID 监听）
    ├── 后端进程（Backend Process）× N  ← 每个客户端连接一个
    ├── bgwriter（后台写进程）
    ├── checkpointer（检查点进程）
    ├── walwriter（WAL 写进程）
    ├── autovacuum launcher（自动清理启动器）
    │   └── autovacuum worker × N
    ├── stats collector（统计收集器）
    ├── wal receiver（WAL 接收器，备库上运行）
    ├── wal sender（WAL 发送器，主库上运行）
    └── logical replication worker（逻辑复制工作进程）

核心进程说明：

进程名	职责
`postmaster`	主进程，监听连接请求，派生子进程，管理系统启停
`postgres`（后端）	处理单个客户端的 SQL 请求，执行查询
`bgwriter`	将 shared_buffers 中的脏页异步写入磁盘，减轻检查点压力
`checkpointer`	执行检查点（Checkpoint），确保数据持久化
`walwriter`	将 WAL 缓冲区的日志刷入 WAL 文件
`autovacuum`	自动执行 VACUUM 和 ANALYZE，清理死元组和更新统计信息
`stats collector`	收集数据库活动统计信息（PostgreSQL 15 前）
`wal sender`	将 WAL 流式发送给备库（流复制）
`wal receiver`	接收主库发来的 WAL 数据（备库上运行）

# 查看 PostgreSQL 相关进程
ps aux | grep postgres

# 通过系统视图查看后端进程
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity
WHERE state != 'idle';

内存架构：共享内存与本地内存

PostgreSQL 的内存分为共享内存（所有进程共享）和本地内存（每个后端进程私有）两大部分。

共享内存（Shared Memory）

共享内存
├── Shared Buffer Pool（共享缓冲池）      ← 核心，由 shared_buffers 控制
├── WAL Buffers（WAL 缓冲区）            ← 由 wal_buffers 控制
├── Commit Log（CLOG）                   ← 事务状态
├── Lock Table（锁表）
└── 其他共享数据结构

Shared Buffer Pool 是最关键的内存区域：

缓存数据页（默认页大小 8KB）
采用 LRU-like 的时钟扫描（Clock Sweep）替换算法
推荐配置：物理内存的 25%（如 32GB 内存配置 8GB）

-- 查看 shared_buffers 配置
SHOW shared_buffers;

-- 查看缓冲池命中率（应高于 99%）
SELECT
    sum(heap_blks_read)  AS heap_read,
    sum(heap_blks_hit)   AS heap_hit,
    sum(heap_blks_hit) * 100.0 / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS ratio
FROM pg_statio_user_tables;

本地内存（Process-Local Memory）

每个后端进程独立拥有：

内存区域	参数	默认值	用途
Work Memory	`work_mem`	4MB	排序、哈希连接等操作
Maintenance Work Memory	`maintenance_work_mem`	64MB	VACUUM、CREATE INDEX 等
Temp Buffers	`temp_buffers`	8MB	临时表缓存
Catalog Cache	—	—	系统目录缓存
Optimizer / Planner	—	—	查询计划

⚠️ 注意：work_mem 是每个操作（不是每个连接）的内存。一个复杂查询可能使用多个 work_mem。最大总内存消耗可达：max_connections × work_mem × 操作数，需谨慎设置。

存储架构：数据文件组织

$PGDATA/
├── base/                    # 每个数据库一个子目录（以 OID 命名）
│   ├── 1/                   # template1 数据库
│   ├── 16384/               # 用户创建的数据库
│   │   ├── 16385            # 表或索引文件（以 OID 命名）
│   │   ├── 16385_fsm        # 空闲空间映射文件
│   │   ├── 16385_vm         # 可见性映射文件
│   │   └── ...
├── global/                  # 共享的系统目录（pg_database、pg_roles 等）
├── pg_wal/                  # WAL 日志文件（每个文件 16MB）
├── pg_xact/                 # 事务提交状态（CLOG）
├── pg_multixact/            # 多事务状态
├── pg_tblspc/               # 表空间符号链接
├── pg_stat_tmp/             # 统计临时文件
├── postgresql.conf          # 主配置文件
├── pg_hba.conf              # 客户端认证配置
├── pg_ident.conf            # 用户名映射
├── PG_VERSION               # 版本号文件
└── postmaster.pid           # 主进程 PID 文件

数据页（Page）结构：

PostgreSQL 存储的基本单位是页（Page），默认大小 8KB：

┌─────────────────────────────────────┐
│ Page Header (24 bytes)              │ ← LSN、校验和、空闲空间指针等
├─────────────────────────────────────┤
│ Item Identifiers（行指针数组）       │ ← 指向每行数据的指针
├─────────────────────────────────────┤
│         Free Space（空闲空间）       │
├─────────────────────────────────────┤
│         Tuples（实际行数据）         │ ← 从后往前增长
└─────────────────────────────────────┘

关键文件：

数据文件（Relation File）：存储表数据，每个文件最大 1GB，超出后自动创建 .1、.2 等分段文件
空闲空间映射（FSM）：记录每个页中的可用空间，用于高效插入
可见性映射（VM）：记录哪些页全部是可见元组，加速 VACUUM 和 Index-Only Scan

WAL 机制简介

WAL（Write-Ahead Logging，预写式日志） 是 PostgreSQL 数据持久性和崩溃恢复的核心机制。

基本原则：任何数据修改写入磁盘之前，必须先将对应的 WAL 记录写入磁盘。

WAL 的作用：

客户端 SQL → 修改 Shared Buffer 中的数据页
                          ↓
              生成 WAL 记录写入 WAL Buffer
                          ↓
              事务提交时 WAL 强制刷盘（fsync）
                          ↓
              后续由 bgwriter/checkpointer 将数据页刷盘

WAL 实现的功能：

崩溃恢复（Crash Recovery）：数据库异常重启后，重放 WAL 恢复到一致状态
PITR（Point-in-Time Recovery）：备份 + WAL 归档，可恢复到任意时间点
流复制（Streaming Replication）：将 WAL 实时传输到备库
逻辑复制（Logical Replication）：基于 WAL 的行级变更传播

-- 查看 WAL 相关配置
SHOW wal_level;          -- minimal | replica | logical
SHOW archive_mode;       -- WAL 归档是否开启
SHOW max_wal_size;       -- WAL 最大占用空间

-- 查看当前 WAL 位置
SELECT pg_current_wal_lsn();
SELECT pg_walfile_name(pg_current_wal_lsn());

MVCC 多版本并发控制基础

MVCC（Multi-Version Concurrency Control） 是 PostgreSQL 实现高并发的核心技术，允许读操作不阻塞写操作，写操作不阻塞读操作。

核心思想：每次数据修改不是原地覆盖，而是创建新版本，旧版本继续保留。不同事务根据自己的"快照（Snapshot）"看到数据的不同版本。

元组（行）的隐藏字段：

字段	含义
`xmin`	创建该版本的事务 ID（插入事务）
`xmax`	删除/更新该版本的事务 ID（0 表示未删除）
`ctid`	物理位置（页号 + 行号）
`cmin/cmax`	命令 ID，用于同一事务内可见性判断

-- 查看行的隐藏字段
SELECT xmin, xmax, ctid, id, username FROM users LIMIT 5;

MVCC 的代价——Dead Tuples（死元组）：

UPDATE 操作实际上是：将旧行标记为删除（设置 xmax）+ 插入新行。DELETE 操作只是设置 xmax，不立即释放空间。这些过期版本称为死元组（Dead Tuples），需要 VACUUM 定期清理回收空间。

-- 查看表中的死元组数量
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

2.4 第一个数据库

让我们从零开始，完成第一个数据库的完整操作。

创建数据库

-- 连接到默认 postgres 数据库
-- psql -U postgres

-- 创建数据库（推荐明确指定编码和本地化设置）
CREATE DATABASE myapp
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1    -- -1 表示不限制
    TEMPLATE = template0;

-- 查看数据库列表
\l

-- 或使用 SQL 查询
SELECT datname,
       pg_encoding_to_char(encoding) AS encoding,
       datcollate,
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY datname;

中文环境注意事项：

-- 支持中文排序的数据库（Linux 系统需先安装 zh_CN 语言包）
CREATE DATABASE myapp_cn
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8'
    TEMPLATE = template0;

-- 如果系统不支持 zh_CN.UTF-8，可以使用 C.UTF-8（性能更好，但排序按 Unicode 码点）
CREATE DATABASE myapp_c
    ENCODING = 'UTF8'
    LC_COLLATE = 'C.UTF-8'
    LC_CTYPE = 'C.UTF-8'
    TEMPLATE = template0;

连接数据库

使用 psql 命令行连接：

# 基本连接格式
psql -h hostname -p port -U username -d dbname

# 本地连接（使用 UNIX socket，无需密码）
psql -U postgres

# 连接指定数据库
psql -U postgres -d myapp

# 使用连接字符串
psql "postgresql://postgres:password@localhost:5432/myapp"

# 使用环境变量（推荐在脚本中使用）
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=myapp
export PGPASSWORD=yourpassword
psql

在 psql 内切换数据库：

-- 在 psql 内连接到另一个数据库
\c myapp

-- 查看当前连接信息
\conninfo

创建第一张表

-- 切换到 myapp 数据库
\c myapp

-- 创建用户表
CREATE TABLE users (
    id          BIGSERIAL       PRIMARY KEY,
    username    VARCHAR(50)     NOT NULL,
    email       TEXT            NOT NULL,
    full_name   VARCHAR(100),
    age         SMALLINT        CHECK (age >= 0 AND age <= 150),
    is_active   BOOLEAN         NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    CONSTRAINT users_username_unique UNIQUE (username),
    CONSTRAINT users_email_unique    UNIQUE (email)
);

-- 添加注释
COMMENT ON TABLE users IS '用户基本信息表';
COMMENT ON COLUMN users.id IS '用户唯一标识';
COMMENT ON COLUMN users.username IS '用户名，全局唯一';
COMMENT ON COLUMN users.email IS '电子邮件地址，全局唯一';

-- 查看表结构
\d users

-- 查看表的详细信息
\d+ users

插入、查询、更新、删除数据

插入数据（INSERT）

-- 单行插入
INSERT INTO users (username, email, full_name, age)
VALUES ('alice', 'alice@example.com', 'Alice Chen', 28);

-- 多行插入（推荐批量操作）
INSERT INTO users (username, email, full_name, age) VALUES
    ('bob',     'bob@example.com',     'Bob Wang',   32),
    ('charlie', 'charlie@example.com', 'Charlie Li',  25),
    ('diana',   'diana@example.com',   'Diana Zhang', 30);

-- INSERT ... RETURNING（返回插入的数据）
INSERT INTO users (username, email, full_name)
VALUES ('eve', 'eve@example.com', 'Eve Liu')
RETURNING id, username, created_at;

-- ON CONFLICT（Upsert：有则更新，无则插入）
INSERT INTO users (username, email, full_name)
VALUES ('alice', 'alice_new@example.com', 'Alice Updated')
ON CONFLICT (username) DO UPDATE
    SET email = EXCLUDED.email,
        updated_at = NOW();

查询数据（SELECT）

-- 查询所有数据
SELECT * FROM users;

-- 指定列查询
SELECT id, username, email, created_at FROM users;

-- 条件过滤
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE age BETWEEN 25 AND 35;
SELECT * FROM users WHERE username LIKE 'a%';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age ASC, username ASC;

-- 分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;  -- 第 1 页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- 第 2 页

-- 聚合查询
SELECT COUNT(*) FROM users;
SELECT is_active, COUNT(*) AS cnt FROM users GROUP BY is_active;
SELECT AVG(age), MAX(age), MIN(age) FROM users WHERE age IS NOT NULL;

更新数据（UPDATE）

-- 更新单条记录
UPDATE users
SET full_name = 'Alice Chen Updated', updated_at = NOW()
WHERE username = 'alice';

-- 批量更新
UPDATE users
SET is_active = FALSE, updated_at = NOW()
WHERE age > 60;

-- UPDATE ... RETURNING
UPDATE users
SET is_active = TRUE
WHERE id = 1
RETURNING id, username, is_active, updated_at;

-- 使用子查询更新
UPDATE users
SET updated_at = NOW()
WHERE id IN (SELECT id FROM users WHERE email LIKE '%@example.com');

删除数据（DELETE）

-- 删除单条记录
DELETE FROM users WHERE username = 'charlie';

-- 批量删除
DELETE FROM users WHERE is_active = FALSE;

-- DELETE ... RETURNING
DELETE FROM users WHERE id = 5 RETURNING *;

-- 清空全表（比 DELETE 快，不可回滚）
TRUNCATE TABLE users;

-- 清空并重置序列
TRUNCATE TABLE users RESTART IDENTITY CASCADE;

使用 psql 命令行工具

psql 是 PostgreSQL 自带的命令行客户端，功能强大。

常用元命令（Meta-commands）：

数据库对象查看：
  \l          列出所有数据库
  \c [db]     连接到指定数据库
  \dn         列出所有模式（Schema）
  \dt         列出当前模式中的所有表
  \dt *.*     列出所有模式中的所有表
  \d  [表名]  查看表结构
  \d+ [表名]  查看表详细结构（包含注释、存储信息）
  \di         列出索引
  \dv         列出视图
  \ds         列出序列
  \df         列出函数
  \du         列出用户/角色
  \db         列出表空间
  \dx         列出已安装扩展

输出格式：
  \x          切换扩展显示模式（竖向显示，适合宽表）
  \timing     开启/关闭执行时间显示
  \pset pager off   关闭分页（输出直接显示）

脚本操作：
  \i [file]   执行 SQL 文件
  \o [file]   将输出重定向到文件
  \e          使用系统编辑器编辑命令
  \! [cmd]    执行 shell 命令

帮助：
  \?          psql 元命令帮助
  \h [SQL]    SQL 语法帮助（如 \h CREATE TABLE）
  \h          列出所有 SQL 命令

其他：
  \q          退出 psql
  \conninfo   显示当前连接信息
  \set        显示/设置 psql 变量

psql 使用技巧：

# 执行单条 SQL 并退出
psql -U postgres -d myapp -c "SELECT COUNT(*) FROM users;"

# 执行 SQL 文件
psql -U postgres -d myapp -f init.sql

# 输出为 CSV 格式
psql -U postgres -d myapp -c "\COPY users TO STDOUT CSV HEADER"

# 无交互模式（适合脚本）
psql -U postgres -d myapp --no-psqlrc -q -c "SELECT 1;"

psql 配置文件（~/.psqlrc）：

-- 推荐的 ~/.psqlrc 配置
\set PROMPT1 '%[%033[1;32m%]%n@%/%[%033[0m%]# '
\timing on
\x auto
\set HISTSIZE 10000
\set COMP_KEYWORD_CASE upper

使用图形化管理工具

1. pgAdmin 4（官方推荐）

pgAdmin 是 PostgreSQL 官方图形化管理工具，支持 Web 和桌面模式。

主要功能：

数据库对象可视化管理
SQL 查询编辑器（带语法高亮、自动补全）
查询计划可视化（Explain 图形化）
数据导入/导出
服务器监控和性能仪表盘
备份恢复向导

# Docker 快速启动 pgAdmin
docker run -d \
    --name pgadmin \
    -e PGADMIN_DEFAULT_EMAIL=admin@example.com \
    -e PGADMIN_DEFAULT_PASSWORD=admin \
    -p 5050:80 \
    dpage/pgadmin4
# 浏览器访问 http://localhost:5050

2. DBeaver（通用数据库工具）

开源跨平台数据库工具，支持几乎所有主流数据库。推荐给需要同时管理多种数据库的用户。

3. DataGrip（JetBrains 出品）

商业 IDE，智能代码补全最强，适合重度 SQL 开发者。

4. TablePlus（macOS/Windows）

界面简洁优雅的原生应用，操作流畅，适合 macOS 用户。

5. 命令行增强：pgcli

# 安装 pgcli（带自动补全和语法高亮的 psql 替代品）
pip install pgcli

# 连接数据库
pgcli -U postgres -d myapp

2.5 SQL 基础速成

DDL：数据定义语言

DDL（Data Definition Language）用于定义和修改数据库对象结构。

-- ===== CREATE =====
-- 创建表
CREATE TABLE products (
    id      BIGSERIAL   PRIMARY KEY,
    name    TEXT        NOT NULL,
    price   NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    stock   INTEGER     NOT NULL DEFAULT 0
);

-- 创建索引
CREATE INDEX idx_products_name ON products (name);
CREATE UNIQUE INDEX idx_products_sku ON products (sku);

-- 创建视图
CREATE VIEW expensive_products AS
SELECT * FROM products WHERE price > 1000;

-- ===== ALTER =====
-- 添加列
ALTER TABLE products ADD COLUMN description TEXT;
ALTER TABLE products ADD COLUMN category_id BIGINT;

-- 修改列类型
ALTER TABLE products ALTER COLUMN description TYPE VARCHAR(500);

-- 重命名列
ALTER TABLE products RENAME COLUMN description TO product_desc;

-- 删除列
ALTER TABLE products DROP COLUMN IF EXISTS product_desc;

-- 添加约束
ALTER TABLE products ADD CONSTRAINT products_price_positive CHECK (price >= 0);
ALTER TABLE products ADD CONSTRAINT fk_products_category
    FOREIGN KEY (category_id) REFERENCES categories(id);

-- ===== DROP =====
-- 删除表（CASCADE 同时删除依赖此表的对象）
DROP TABLE IF EXISTS products CASCADE;

-- 删除索引
DROP INDEX IF EXISTS idx_products_name;

-- ===== TRUNCATE =====
TRUNCATE TABLE products RESTART IDENTITY;

DML：数据操作语言

-- ===== INSERT =====
INSERT INTO products (name, price, stock)
VALUES ('MacBook Pro', 14999.00, 50);

-- 从另一张表插入
INSERT INTO product_archive
SELECT * FROM products WHERE created_at < '2023-01-01';

-- ===== UPDATE =====
UPDATE products
SET price = price * 1.1,    -- 涨价 10%
    updated_at = NOW()
WHERE category_id = 1;

-- ===== DELETE =====
DELETE FROM products WHERE stock = 0;

-- ===== SELECT =====
-- 基础查询
SELECT p.id, p.name, p.price, c.name AS category
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.price BETWEEN 1000 AND 5000
ORDER BY p.price DESC
LIMIT 20;

-- 子查询
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- CTE（公用表表达式）
WITH top_products AS (
    SELECT * FROM products ORDER BY price DESC LIMIT 10
)
SELECT * FROM top_products WHERE stock > 0;

-- 窗口函数
SELECT
    name,
    price,
    category_id,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS price_rank,
    AVG(price) OVER (PARTITION BY category_id) AS category_avg_price
FROM products;

DCL：数据控制语言

-- 授权
GRANT SELECT ON TABLE products TO readonly_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- 撤权
REVOKE DELETE ON TABLE products FROM app_role;
REVOKE ALL PRIVILEGES ON TABLE sensitive_data FROM public;

-- 查看权限
\dp products   -- psql 元命令
-- 或
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'products';

TCL：事务控制语言

-- 显式事务
BEGIN;  -- 或 START TRANSACTION;
    INSERT INTO orders (user_id, total) VALUES (1, 299.99);
    UPDATE users SET order_count = order_count + 1 WHERE id = 1;
COMMIT;  -- 提交

-- 回滚
BEGIN;
    DELETE FROM products WHERE id = 100;
    -- 发现删错了
ROLLBACK;  -- 撤销所有修改

-- 保存点（Savepoint）
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    SAVEPOINT sp1;
    INSERT INTO order_items (order_id, product_id) VALUES (1, 999);  -- 可能失败
    ROLLBACK TO SAVEPOINT sp1;  -- 只回滚到保存点
    INSERT INTO order_items (order_id, product_id) VALUES (1, 1);    -- 重试
COMMIT;

-- 设置事务隔离级别
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    ...
COMMIT;

PostgreSQL SQL 方言特性

PostgreSQL 在标准 SQL 之上提供了许多强大的扩展语法：

-- 1. RETURNING 子句（MySQL 不支持）
INSERT INTO users (username) VALUES ('test') RETURNING id, created_at;

-- 2. DISTINCT ON（PostgreSQL 独有）
-- 按 category_id 分组，取每组 price 最高的记录
SELECT DISTINCT ON (category_id) id, name, price, category_id
FROM products
ORDER BY category_id, price DESC;

-- 3. 横向连接（LATERAL JOIN）
SELECT u.username, recent_orders.*
FROM users u,
LATERAL (
    SELECT * FROM orders o WHERE o.user_id = u.id ORDER BY created_at DESC LIMIT 3
) AS recent_orders;

-- 4. 数组操作
SELECT ARRAY[1, 2, 3] || ARRAY[4, 5];          -- 数组拼接
SELECT UNNEST(ARRAY['a', 'b', 'c']);            -- 数组展开为行
SELECT 1 = ANY(ARRAY[1, 2, 3]);                 -- 数组成员检测

-- 5. FILTER 子句（条件聚合）
SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE is_active)  AS active_count,
    COUNT(*) FILTER (WHERE NOT is_active) AS inactive_count
FROM users;

-- 6. 生成行（generate_series）
SELECT generate_series(1, 10);
SELECT generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 month'::INTERVAL);

-- 7. COPY 命令（高效数据导入/导出）
COPY products FROM '/tmp/products.csv' CSV HEADER;
COPY (SELECT * FROM products WHERE active = true) TO '/tmp/active_products.csv' CSV HEADER;

2.6 数据类型初识

PostgreSQL 拥有极其丰富的内置数据类型，选择合适的类型是设计高质量数据库的第一步。

数值类型

类型	存储	范围	说明
`SMALLINT`	2 字节	-32,768 ~ 32,767	小整数
`INTEGER` / `INT`	4 字节	±21 亿	常用整数
`BIGINT`	8 字节	±922 亿亿	大整数，推荐主键使用
`NUMERIC(p,s)`	可变	任意精度	精确小数，适合金融计算
`DECIMAL(p,s)`	可变	任意精度	NUMERIC 的别名
`REAL`	4 字节	6位有效数字	浮点数，有精度损失
`DOUBLE PRECISION`	8 字节	15位有效数字	双精度浮点
`SERIAL`	4 字节	1 ~ 2,147,483,647	自增整数（已有 GENERATED）
`BIGSERIAL`	8 字节	1 ~ 9,223,372,036,854,775,807	自增大整数

-- 推荐：现代 PostgreSQL 使用 GENERATED ALWAYS AS IDENTITY
CREATE TABLE example (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- 等同于 BIGSERIAL，但更符合 SQL 标准

    price       NUMERIC(12, 2),    -- 最多 12 位，2 位小数（金融场景必用）
    temperature DOUBLE PRECISION,  -- 科学计算
    age         SMALLINT           -- 节省空间
);

-- 金融计算注意事项
SELECT 0.1 + 0.2;                -- 0.30000000000000004（浮点误差！）
SELECT 0.1::NUMERIC + 0.2::NUMERIC;  -- 0.3（精确）

字符串类型

类型	说明
`CHAR(n)`	定长字符串，不足补空格，几乎不推荐使用
`VARCHAR(n)`	变长字符串，有最大长度限制
`TEXT`	无限长度变长字符串（PostgreSQL 推荐）

-- PostgreSQL 中 TEXT 与 VARCHAR 性能几乎相同，推荐使用 TEXT
CREATE TABLE articles (
    title       VARCHAR(200),   -- 需要限制长度时使用
    content     TEXT,           -- 长文本，无限制
    summary     TEXT
);

-- 字符串函数
SELECT LENGTH('Hello, 世界');              -- 字符数：9
SELECT OCTET_LENGTH('Hello, 世界');        -- 字节数：13（UTF-8）
SELECT UPPER('hello');                     -- HELLO
SELECT LOWER('HELLO');                     -- hello
SELECT TRIM('  hello  ');                  -- hello
SELECT SUBSTRING('PostgreSQL', 1, 4);     -- Post
SELECT CONCAT('Hello', ', ', 'World');    -- Hello, World
SELECT 'Hello' || ', ' || 'World';        -- Hello, World（推荐）
SELECT REGEXP_REPLACE('abc123', '\d+', 'NUM'); -- abcNUM

日期时间类型

类型	存储	说明
`DATE`	4 字节	日期（年月日）
`TIME`	8 字节	时间（时分秒，无时区）
`TIMETZ`	12 字节	带时区时间
`TIMESTAMP`	8 字节	日期时间（无时区）
`TIMESTAMPTZ`	8 字节	带时区日期时间（推荐）
`INTERVAL`	16 字节	时间间隔

-- TIMESTAMPTZ 是生产环境的最佳实践
CREATE TABLE events (
    id          BIGSERIAL PRIMARY KEY,
    event_name  TEXT NOT NULL,
    start_time  TIMESTAMPTZ NOT NULL,
    duration    INTERVAL
);

INSERT INTO events (event_name, start_time, duration)
VALUES ('Meeting', '2024-03-15 14:00:00+08', '1 hour 30 minutes');

-- 日期时间函数
SELECT NOW();                           -- 当前时间（带时区）
SELECT CURRENT_TIMESTAMP;              -- 同 NOW()
SELECT CURRENT_DATE;                   -- 当前日期
SELECT DATE_TRUNC('month', NOW());     -- 截断到月份
SELECT EXTRACT(YEAR FROM NOW());       -- 提取年份
SELECT NOW() + INTERVAL '7 days';     -- 7 天后
SELECT AGE('2024-03-15', '1990-01-01'); -- 年龄计算

-- 时区处理
SHOW timezone;                                      -- 查看当前时区
SET timezone = 'Asia/Shanghai';                    -- 设置会话时区
SELECT NOW() AT TIME ZONE 'UTC';                   -- 转换时区
SELECT '2024-01-01 00:00:00'::TIMESTAMPTZ AT TIME ZONE 'America/New_York';

布尔类型

-- BOOLEAN 类型
CREATE TABLE settings (
    feature_enabled BOOLEAN DEFAULT FALSE
);

-- PostgreSQL 接受多种输入方式
INSERT INTO settings VALUES (TRUE);   -- 或 true, 'yes', 'on', '1', 't'
INSERT INTO settings VALUES (FALSE);  -- 或 false, 'no', 'off', '0', 'f'

-- NULL 与布尔值的区别
SELECT TRUE AND NULL;   -- NULL（不确定）
SELECT FALSE AND NULL;  -- FALSE
SELECT TRUE OR NULL;    -- TRUE

数组类型

PostgreSQL 原生支持任意类型的数组，这在其他数据库中极为罕见。

-- 数组类型声明
CREATE TABLE students (
    id      BIGSERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    scores  INTEGER[],          -- 整数数组
    tags    TEXT[],             -- 文本数组
    matrix  INTEGER[][]         -- 二维数组
);

-- 插入数组数据
INSERT INTO students (name, scores, tags)
VALUES ('Alice', ARRAY[85, 90, 78, 95], ARRAY['优秀', '积极']);

-- 数组操作
SELECT scores[1] FROM students;                          -- 访问第 1 个元素（下标从 1 开始）
SELECT array_length(scores, 1) FROM students;            -- 数组长度
SELECT 90 = ANY(scores) FROM students;                  -- 检查元素是否存在
SELECT * FROM students WHERE '优秀' = ANY(tags);         -- 数组包含查询
SELECT UNNEST(scores) FROM students;                     -- 展开数组为行
SELECT scores || ARRAY[100] FROM students;               -- 追加元素
SELECT ARRAY_AGG(name ORDER BY name) FROM students;      -- 聚合为数组

JSON/JSONB 类型

这是 PostgreSQL 区别于其他关系型数据库的重要特性之一：

类型	存储	索引支持	查询性能	保留格式
`JSON`	原始文本	有限	较慢（每次解析）	保留（空格、键顺序）
`JSONB`	二进制	GIN 索引	快	不保留空格，键去重排序

生产环境推荐使用 JSONB。

-- JSONB 示例
CREATE TABLE user_profiles (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    metadata    JSONB
);

INSERT INTO user_profiles (user_id, metadata)
VALUES (1, '{"age": 28, "city": "上海", "hobbies": ["读书", "编程"], "address": {"street": "南京路"}}');

-- JSON 查询操作符
SELECT metadata -> 'city'          FROM user_profiles;  -- 获取 JSON 值（返回 JSON 类型）
SELECT metadata ->> 'city'         FROM user_profiles;  -- 获取文本值（返回 TEXT）
SELECT metadata -> 'address' ->> 'street' FROM user_profiles;  -- 嵌套访问
SELECT metadata #> '{address,street}'     FROM user_profiles;  -- 路径访问

-- 条件查询
SELECT * FROM user_profiles WHERE metadata @> '{"city": "上海"}';  -- 包含查询
SELECT * FROM user_profiles WHERE metadata ? 'age';                 -- 键存在检查
SELECT * FROM user_profiles WHERE (metadata ->> 'age')::INT > 25;

-- 修改 JSONB
UPDATE user_profiles
SET metadata = metadata || '{"level": "VIP"}'              -- 合并/更新键
WHERE user_id = 1;

UPDATE user_profiles
SET metadata = metadata - 'age'                             -- 删除键
WHERE user_id = 1;

-- 创建 GIN 索引加速 JSONB 查询
CREATE INDEX idx_profiles_metadata ON user_profiles USING GIN (metadata);

其他特殊类型

PostgreSQL 还提供了许多其他数据库没有的特殊类型：

-- UUID 类型（需要 uuid-ossp 扩展）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE sessions (
    id      UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    data    TEXT
);

-- 网络地址类型
CREATE TABLE servers (
    ip_addr  INET,       -- IPv4/IPv6 地址（如 192.168.1.1）
    network  CIDR,       -- 网络地址（如 192.168.1.0/24）
    mac_addr MACADDR     -- MAC 地址
);
SELECT '192.168.1.1'::INET << '192.168.1.0/24'::CIDR;  -- 检查是否在子网内

-- 范围类型
CREATE TABLE bookings (
    room_id     INTEGER,
    during      TSTZRANGE   -- 时间戳范围
);
INSERT INTO bookings VALUES (101, '[2024-03-01 09:00, 2024-03-01 18:00)');
-- 查询时间有冲突的预订
SELECT * FROM bookings WHERE during && '[2024-03-01 10:00, 2024-03-01 12:00)';

-- 货币类型（不推荐，受 locale 影响）
-- 建议使用 NUMERIC(12,2) 代替 MONEY

-- 全文搜索类型
CREATE TABLE documents (
    content     TEXT,
    content_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('chinese', content)) STORED
);
CREATE INDEX idx_documents_tsv ON documents USING GIN (content_tsv);
SELECT * FROM documents WHERE content_tsv @@ to_tsquery('postgresql & 入门');

-- 枚举类型
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE feedback (
    id      BIGSERIAL PRIMARY KEY,
    user_mood mood
);

2.7 基本对象管理

表的创建与修改

-- 完整建表示例
CREATE TABLE orders (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no        VARCHAR(50)     NOT NULL,
    user_id         BIGINT          NOT NULL,
    status          SMALLINT        NOT NULL DEFAULT 0,
    total_amount    NUMERIC(12, 2)  NOT NULL CHECK (total_amount >= 0),
    shipping_addr   TEXT,
    note            TEXT,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ     -- 软删除标记
);

-- 继承（PostgreSQL 特有）
CREATE TABLE premium_orders () INHERITS (orders);

-- 表修改操作汇总
ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(50);          -- 增加列
ALTER TABLE orders DROP COLUMN IF EXISTS note;                   -- 删除列
ALTER TABLE orders RENAME COLUMN shipping_addr TO ship_address; -- 重命名列
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 1;           -- 修改默认值
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;            -- 添加非空约束
ALTER TABLE orders ALTER COLUMN order_no TYPE TEXT;             -- 修改数据类型
ALTER TABLE orders RENAME TO purchase_orders;                   -- 重命名表
ALTER TABLE orders SET SCHEMA archive;                          -- 移动到其他模式
ALTER TABLE orders OWNER TO new_owner;                          -- 更改所有者

索引的概念与创建

索引是加速查询的关键，但不当使用反而会影响写入性能。

PostgreSQL 支持的索引类型：

索引类型	适用场景
B-Tree	默认类型，等值、范围查询，排序
Hash	仅等值查询，比 B-Tree 快
GIN	数组、JSONB、全文搜索
GiST	几何、全文搜索、范围类型
BRIN	超大表，按物理顺序存储的数据（如时序数据）
SP-GiST	特殊数据结构（IP 地址、地理坐标）

-- B-Tree 索引（最常用）
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- 唯一索引
CREATE UNIQUE INDEX idx_orders_order_no ON orders (order_no);

-- 部分索引（只索引满足条件的行，节省空间）
CREATE INDEX idx_orders_active ON orders (created_at) WHERE deleted_at IS NULL;

-- 表达式索引（索引表达式结果）
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
-- 查询时必须使用相同表达式才能命中
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- 覆盖索引（INCLUDE，PostgreSQL 11+）
CREATE INDEX idx_orders_user_covering ON orders (user_id) INCLUDE (status, total_amount);

-- GIN 索引（用于 JSONB）
CREATE INDEX idx_profiles_meta ON user_profiles USING GIN (metadata);

-- 并发创建（不锁表，适合生产环境）
CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);

-- 查看索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- 查看未被使用的索引
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

约束的类型与使用

-- 1. PRIMARY KEY（主键约束，唯一 + 非空 + B-Tree 索引）
CREATE TABLE t1 (id BIGSERIAL PRIMARY KEY);

-- 复合主键
CREATE TABLE t2 (
    a INTEGER,
    b INTEGER,
    PRIMARY KEY (a, b)
);

-- 2. UNIQUE（唯一约束）
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- 3. NOT NULL（非空约束）
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- 4. CHECK（检查约束）
ALTER TABLE products ADD CONSTRAINT chk_price_positive CHECK (price > 0);
ALTER TABLE users ADD CONSTRAINT chk_age_range CHECK (age >= 0 AND age <= 150);

-- 5. FOREIGN KEY（外键约束）
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE RESTRICT     -- 禁止删除有订单的用户
    ON UPDATE CASCADE;     -- 级联更新

-- ON DELETE / ON UPDATE 选项：
-- RESTRICT  : 有依赖时拒绝操作（默认）
-- CASCADE   : 级联删除/更新
-- SET NULL  : 设置外键列为 NULL
-- SET DEFAULT: 设置外键列为默认值

-- 6. EXCLUSION（排除约束，防止范围重叠）
CREATE EXTENSION btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap
    EXCLUDE USING GIST (room_id WITH =, during WITH &&);
-- 防止同一房间的时间段重叠

-- 查看所有约束
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'orders'::REGCLASS;

视图的创建与应用

-- 简单视图
CREATE OR REPLACE VIEW v_active_orders AS
SELECT
    o.id,
    o.order_no,
    u.username,
    u.email,
    o.total_amount,
    o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.deleted_at IS NULL
ORDER BY o.created_at DESC;

-- 可更新视图（满足条件时支持 DML）
CREATE VIEW v_active_users AS
SELECT * FROM users WHERE is_active = TRUE WITH CHECK OPTION;
-- WITH CHECK OPTION 确保通过视图的修改仍满足过滤条件

-- 物化视图（缓存查询结果）
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    DATE_TRUNC('day', created_at) AS sale_date,
    COUNT(*)                       AS order_count,
    SUM(total_amount)              AS total_revenue
FROM orders
WHERE deleted_at IS NULL
GROUP BY 1
ORDER BY 1;

-- 创建索引加速物化视图查询
CREATE INDEX ON mv_daily_sales (sale_date);

-- 定期刷新（CONCURRENTLY 不锁表，需要唯一索引）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

序列的使用

-- 显式创建序列
CREATE SEQUENCE order_seq
    START WITH 1000000        -- 从 100 万开始
    INCREMENT BY 1
    MINVALUE 1000000
    MAXVALUE 9999999999
    CYCLE                     -- 达到最大值后循环（谨慎使用）
    CACHE 100;                -- 预缓存 100 个值（提高性能）

-- 使用序列
SELECT nextval('order_seq');          -- 获取下一个值
SELECT currval('order_seq');          -- 本会话当前值
SELECT setval('order_seq', 2000000); -- 重置序列值
SELECT lastval();                     -- 本会话最后获取的序列值

-- 将序列绑定到列
ALTER TABLE orders ALTER COLUMN id SET DEFAULT nextval('order_seq');

-- 查看所有序列
\ds
-- 或
SELECT schemaname, sequencename, last_value, increment_by, max_value
FROM pg_sequences;

函数与存储过程初探

-- ===== 函数（Function）=====
-- 函数必须有返回值，可以在 SQL 中调用

-- 简单函数
CREATE OR REPLACE FUNCTION get_user_age(birth_date DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN DATE_PART('year', AGE(birth_date));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用
SELECT get_user_age('1990-05-15');

-- 返回表的函数（Table Function）
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT)
RETURNS TABLE (
    order_id    BIGINT,
    order_no    VARCHAR(50),
    amount      NUMERIC(12,2),
    created_at  TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT o.id, o.order_no, o.total_amount, o.created_at
    FROM orders o
    WHERE o.user_id = p_user_id
    ORDER BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql STABLE;

-- 调用
SELECT * FROM get_user_orders(1);

-- ===== 存储过程（Procedure，PostgreSQL 11+）=====
-- 存储过程无返回值，可以提交/回滚事务

CREATE OR REPLACE PROCEDURE transfer_amount(
    p_from_user BIGINT,
    p_to_user   BIGINT,
    p_amount    NUMERIC
) AS $$
BEGIN
    UPDATE accounts SET balance = balance - p_amount WHERE user_id = p_from_user;
    UPDATE accounts SET balance = balance + p_amount WHERE user_id = p_to_user;
    COMMIT;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程
CALL transfer_amount(1, 2, 100.00);

2.8 用户与权限

用户和角色的概念

PostgreSQL 使用统一的**角色（Role）**系统来管理用户和权限组：

角色（Role）：权限的集合，可以代表一个用户或一组权限
用户（User）：具有 LOGIN 权限的角色
角色可以包含其他角色（权限继承）
集群级别的对象（不属于某个数据库）

角色层次结构示例：
                    admin（超级管理员）
                   /
     dev_role（开发角色）── readonly（只读角色）
         |
     alice、bob（实际登录用户）

创建用户和角色

-- 创建角色（无登录权限）
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE app_role;

-- 创建用户（有登录权限）
CREATE USER alice
    WITH PASSWORD 'StrongPass123!'
    CONNECTION LIMIT 10         -- 最大连接数
    VALID UNTIL '2025-12-31';   -- 账户过期时间

CREATE USER bob WITH PASSWORD 'BobPass456!';
CREATE USER app_user WITH PASSWORD 'AppPass789!';

-- 修改用户属性
ALTER USER alice WITH PASSWORD 'NewPass321!';
ALTER USER alice CONNECTION LIMIT 20;
ALTER USER alice VALID UNTIL 'infinity';  -- 永不过期
ALTER USER alice RENAME TO alice_admin;

-- 授予角色成员关系
GRANT readonly TO alice;
GRANT readwrite TO bob;
GRANT app_role TO app_user;

-- 查看角色关系
\du
SELECT r.rolname, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid;

权限体系概述

PostgreSQL 的权限体系分为两个层面：

1. 角色属性（数据库级别）：

属性	说明
`SUPERUSER`	超级用户，绕过所有权限检查
`CREATEDB`	可以创建数据库
`CREATEROLE`	可以创建角色
`LOGIN`	可以登录
`REPLICATION`	可以进行流复制
`BYPASSRLS`	跳过行级安全策略

2. 对象权限（对象级别）：

对象类型	可授予的权限
表/视图	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
列	SELECT, INSERT, UPDATE, REFERENCES
序列	USAGE, SELECT, UPDATE
模式	USAGE, CREATE
数据库	CONNECT, CREATE, TEMPORARY
函数	EXECUTE
表空间	CREATE

GRANT 和 REVOKE

-- ===== GRANT =====

-- 授予表权限
GRANT SELECT ON users TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO readwrite;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_role;

-- 授予列级权限
GRANT SELECT (id, username, email) ON users TO readonly;

-- 授予模式权限（先授予 USAGE，再授予对象权限）
GRANT USAGE ON SCHEMA public TO readonly;
GRANT USAGE ON SCHEMA public TO readwrite;

-- 授予序列权限
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_role;

-- 授予数据库权限
GRANT CONNECT ON DATABASE myapp TO alice;

-- 带 WITH GRANT OPTION（允许转授）
GRANT SELECT ON users TO alice WITH GRANT OPTION;

-- ===== REVOKE =====
REVOKE DELETE ON orders FROM readwrite;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readonly;
REVOKE CONNECT ON DATABASE myapp FROM bob;

-- 查看对象权限
\dp users    -- 查看表权限
\dp          -- 查看所有表权限

默认权限设置

-- 设置默认权限（对未来创建的对象生效）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO readwrite;

ALTER DEFAULT PRIVILEGES FOR ROLE alice IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- 查看默认权限
SELECT * FROM pg_default_acl;

-- public 模式权限收紧（安全最佳实践，PostgreSQL 15 已默认）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE myapp FROM PUBLIC;

pg_hba.conf 认证配置

pg_hba.conf（Host-Based Authentication）是 PostgreSQL 的客户端认证配置文件，控制谁可以从哪里以何种方式连接到哪个数据库。

配置格式：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
host    myapp           app_user        192.168.1.0/24          scram-sha-256
host    all             all             0.0.0.0/0               reject

TYPE 选项：

local：Unix 域套接字连接
host：TCP/IP 连接（含 SSL 和非 SSL）
hostssl：仅 SSL 连接
hostnossl：仅非 SSL 连接

METHOD 选项：

方法	说明
`trust`	无需密码，危险，仅限本地开发
`reject`	拒绝连接
`md5`	MD5 密码（已过时，安全性低）
`scram-sha-256`	SCRAM-SHA-256（推荐，PostgreSQL 10+）
`peer`	使用操作系统用户名（仅限 local）
`ident`	通过 ident 服务器验证
`ldap`	LDAP 目录服务
`cert`	SSL 客户端证书

# 修改后需要重新加载配置（无需重启）
pg_ctl reload
# 或
SELECT pg_reload_conf();

生产环境安全建议：

不允许超级用户从远程登录
使用 scram-sha-256 而非 md5
为不同应用使用不同数据库用户
使用 SSL 加密连接
限制允许连接的 IP 段

2.9 实战案例：构建一个简单的博客系统

需求分析

我们要构建一个基础的博客系统，支持以下功能：

用户注册和管理
文章（帖子）的发布与管理
文章分类和标签
评论系统
简单的统计功能

业务规则：

一个用户可以发表多篇文章
文章可以属于一个分类
文章可以有多个标签
用户可以对文章发表评论
支持文章草稿和发布状态

数据库设计

实体关系：

users (1) ──< articles (N)
categories (1) ──< articles (N)
articles (N) >──< tags (M)  [通过 article_tags 关联表]
articles (1) ──< comments (N)
users (1) ──< comments (N)

表结构创建

-- 创建数据库
CREATE DATABASE blog
    ENCODING = 'UTF8'
    LC_COLLATE = 'C.UTF-8'
    LC_CTYPE = 'C.UTF-8'
    TEMPLATE = template0;

\c blog

-- 1. 用户表
CREATE TABLE users (
    id              BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username        VARCHAR(50)     NOT NULL,
    email           TEXT            NOT NULL,
    password_hash   TEXT            NOT NULL,
    display_name    VARCHAR(100),
    bio             TEXT,
    avatar_url      TEXT,
    is_active       BOOLEAN         NOT NULL DEFAULT TRUE,
    is_admin        BOOLEAN         NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email    UNIQUE (email)
);

-- 2. 文章分类表
CREATE TABLE categories (
    id          BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(100)    NOT NULL,
    slug        VARCHAR(100)    NOT NULL,    -- URL 友好的标识符
    description TEXT,
    parent_id   BIGINT          REFERENCES categories(id) ON DELETE SET NULL,
    sort_order  INTEGER         NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_categories_slug UNIQUE (slug)
);

-- 3. 标签表
CREATE TABLE tags (
    id          BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(50)     NOT NULL,
    slug        VARCHAR(50)     NOT NULL,
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_tags_slug UNIQUE (slug)
);

-- 4. 文章表
CREATE TYPE article_status AS ENUM ('draft', 'published', 'archived');

CREATE TABLE articles (
    id              BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title           TEXT            NOT NULL,
    slug            VARCHAR(200)    NOT NULL,
    content         TEXT            NOT NULL DEFAULT '',
    summary         TEXT,
    cover_url       TEXT,
    author_id       BIGINT          NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    category_id     BIGINT          REFERENCES categories(id) ON DELETE SET NULL,
    status          article_status  NOT NULL DEFAULT 'draft',
    view_count      INTEGER         NOT NULL DEFAULT 0,
    like_count      INTEGER         NOT NULL DEFAULT 0,
    comment_count   INTEGER         NOT NULL DEFAULT 0,
    published_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ,
    CONSTRAINT uq_articles_slug UNIQUE (slug)
);

-- 5. 文章-标签关联表
CREATE TABLE article_tags (
    article_id  BIGINT  NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id      BIGINT  NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

-- 6. 评论表
CREATE TABLE comments (
    id          BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    article_id  BIGINT      NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    user_id     BIGINT      NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    parent_id   BIGINT      REFERENCES comments(id) ON DELETE CASCADE,  -- 支持回复
    content     TEXT        NOT NULL,
    is_approved BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at  TIMESTAMPTZ
);

-- 创建索引
CREATE INDEX idx_articles_author     ON articles (author_id);
CREATE INDEX idx_articles_category   ON articles (category_id);
CREATE INDEX idx_articles_status     ON articles (status) WHERE deleted_at IS NULL;
CREATE INDEX idx_articles_published  ON articles (published_at DESC) WHERE status = 'published';
CREATE INDEX idx_comments_article    ON comments (article_id);
CREATE INDEX idx_comments_user       ON comments (user_id);
CREATE INDEX idx_comments_parent     ON comments (parent_id) WHERE parent_id IS NOT NULL;

-- 全文搜索索引
CREATE INDEX idx_articles_fulltext ON articles
    USING GIN (to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(content, '')));

-- 添加表注释
COMMENT ON TABLE users       IS '博客用户表';
COMMENT ON TABLE articles    IS '博客文章表';
COMMENT ON TABLE categories  IS '文章分类表';
COMMENT ON TABLE tags        IS '标签表';
COMMENT ON TABLE comments    IS '评论表';

基础数据操作

-- 插入测试用户
INSERT INTO users (username, email, password_hash, display_name, bio) VALUES
('alice',   'alice@blog.com',   '$2b$12$xxxhash1', 'Alice Chen',   '全栈开发工程师，专注 PostgreSQL 和云原生'),
('bob',     'bob@blog.com',     '$2b$12$xxxhash2', 'Bob Wang',     'DBA 老司机，10 年数据库经验'),
('charlie', 'charlie@blog.com', '$2b$12$xxxhash3', 'Charlie Li',   '开源爱好者');

-- 插入分类
INSERT INTO categories (name, slug, description) VALUES
('数据库',       'database',       '数据库相关技术文章'),
('PostgreSQL',  'postgresql',     'PostgreSQL 专题'),
('性能优化',     'performance',    '数据库性能调优'),
('云原生',       'cloud-native',   '云原生技术栈');

-- 更新子分类（PostgreSQL 作为数据库的子分类）
UPDATE categories SET parent_id = (SELECT id FROM categories WHERE slug = 'database')
WHERE slug = 'postgresql';

-- 插入标签
INSERT INTO tags (name, slug) VALUES
('PostgreSQL', 'postgresql'),
('SQL',        'sql'),
('性能优化',   'performance'),
('入门',       'beginner'),
('MVCC',       'mvcc'),
('索引',       'index');

-- 发布文章
INSERT INTO articles (title, slug, content, summary, author_id, category_id, status, published_at)
VALUES (
    'PostgreSQL MVCC 原理深度解析',
    'postgresql-mvcc-deep-dive',
    '## 什么是 MVCC？\n\nMVCC（多版本并发控制）是 PostgreSQL 实现高并发的核心机制...',
    'MVCC 允许读写互不阻塞，本文深度分析其实现原理和性能影响。',
    (SELECT id FROM users WHERE username = 'alice'),
    (SELECT id FROM categories WHERE slug = 'postgresql'),
    'published',
    NOW()
);

INSERT INTO articles (title, slug, content, summary, author_id, category_id, status, published_at)
VALUES (
    'PostgreSQL 索引优化实战',
    'postgresql-index-optimization',
    '## 索引的重要性\n\n合理使用索引是提升查询性能的最有效手段...',
    '从实际案例出发，讲解如何分析和优化 PostgreSQL 索引。',
    (SELECT id FROM users WHERE username = 'bob'),
    (SELECT id FROM categories WHERE slug = 'postgresql'),
    'published',
    NOW() - INTERVAL '3 days'
);

-- 给文章打标签
INSERT INTO article_tags (article_id, tag_id)
SELECT a.id, t.id
FROM articles a, tags t
WHERE a.slug = 'postgresql-mvcc-deep-dive'
  AND t.slug IN ('postgresql', 'mvcc', '入门');

-- 发表评论
INSERT INTO comments (article_id, user_id, content)
VALUES (
    (SELECT id FROM articles WHERE slug = 'postgresql-mvcc-deep-dive'),
    (SELECT id FROM users WHERE username = 'bob'),
    '写得很好！MVCC 的版本链机制讲解得非常清晰。'
);

简单查询与统计

-- 1. 查询已发布文章列表（含作者和分类信息）
SELECT
    a.id,
    a.title,
    a.summary,
    u.display_name  AS author,
    c.name          AS category,
    a.view_count,
    a.comment_count,
    a.published_at
FROM articles a
JOIN users u ON a.author_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE a.status = 'published'
  AND a.deleted_at IS NULL
ORDER BY a.published_at DESC
LIMIT 10;

-- 2. 查询文章详情（含标签列表）
SELECT
    a.*,
    u.display_name  AS author_name,
    u.avatar_url    AS author_avatar,
    c.name          AS category_name,
    ARRAY_AGG(t.name ORDER BY t.name) FILTER (WHERE t.id IS NOT NULL) AS tags
FROM articles a
JOIN users u ON a.author_id = u.id
LEFT JOIN categories c ON a.category_id = c.id
LEFT JOIN article_tags at ON a.id = at.article_id
LEFT JOIN tags t ON at.tag_id = t.id
WHERE a.slug = 'postgresql-mvcc-deep-dive'
GROUP BY a.id, u.display_name, u.avatar_url, c.name;

-- 3. 每个分类的文章数量统计
SELECT
    c.name AS category,
    COUNT(a.id) AS article_count,
    SUM(a.view_count) AS total_views,
    MAX(a.published_at) AS last_published
FROM categories c
LEFT JOIN articles a ON c.id = a.category_id
    AND a.status = 'published'
    AND a.deleted_at IS NULL
GROUP BY c.id, c.name
ORDER BY article_count DESC;

-- 4. 最活跃的作者（按文章数和浏览量综合排名）
SELECT
    u.username,
    u.display_name,
    COUNT(a.id)         AS article_count,
    SUM(a.view_count)   AS total_views,
    SUM(a.like_count)   AS total_likes,
    ROUND(AVG(a.view_count), 1) AS avg_views
FROM users u
JOIN articles a ON u.id = a.author_id
WHERE a.status = 'published' AND a.deleted_at IS NULL
GROUP BY u.id, u.username, u.display_name
ORDER BY total_views DESC;

-- 5. 全文搜索
SELECT
    title,
    summary,
    ts_rank(
        to_tsvector('simple', COALESCE(title,'') || ' ' || COALESCE(content,'')),
        to_tsquery('simple', 'MVCC | 并发')
    ) AS relevance
FROM articles
WHERE to_tsvector('simple', COALESCE(title,'') || ' ' || COALESCE(content,''))
    @@ to_tsquery('simple', 'MVCC | 并发')
  AND status = 'published'
ORDER BY relevance DESC;

-- 6. 最近 7 天每天的文章发布数量（含没有文章的日期）
SELECT
    d::DATE AS publish_date,
    COUNT(a.id) AS article_count
FROM generate_series(
    DATE_TRUNC('day', NOW()) - INTERVAL '6 days',
    DATE_TRUNC('day', NOW()),
    '1 day'::INTERVAL
) AS d
LEFT JOIN articles a ON DATE_TRUNC('day', a.published_at) = d
    AND a.status = 'published'
GROUP BY d
ORDER BY d;

性能优化初步

-- 1. 使用 EXPLAIN ANALYZE 分析查询计划
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM articles
WHERE author_id = 1
  AND status = 'published'
ORDER BY published_at DESC
LIMIT 10;

-- 关注关键指标：
-- Seq Scan vs Index Scan（尽量走索引）
-- actual rows vs estimated rows（统计信息准确性）
-- Buffers: hit vs read（命中缓存 vs 读磁盘）

-- 2. 创建合适的复合索引
CREATE INDEX idx_articles_author_status_pub
    ON articles (author_id, status, published_at DESC)
    WHERE deleted_at IS NULL;

-- 3. 更新统计信息
ANALYZE articles;
ANALYZE VERBOSE articles;  -- 显示详细信息

-- 4. 查看慢查询（需要启用 pg_stat_statements）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
    LEFT(query, 100)    AS query_preview,
    calls,
    ROUND(total_exec_time::NUMERIC, 2) AS total_ms,
    ROUND(mean_exec_time::NUMERIC, 2)  AS mean_ms,
    ROUND(stddev_exec_time::NUMERIC, 2) AS stddev_ms,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 5. 连接分析（避免 N+1 查询）
-- 错误方式（N+1）：先查文章，再循环查作者
-- 正确方式：JOIN 一次性获取

-- 6. 物化视图缓存统计数据
CREATE MATERIALIZED VIEW mv_article_stats AS
SELECT
    author_id,
    COUNT(*) FILTER (WHERE status = 'published') AS published_count,
    SUM(view_count) AS total_views,
    SUM(like_count) AS total_likes
FROM articles
WHERE deleted_at IS NULL
GROUP BY author_id;

CREATE UNIQUE INDEX ON mv_article_stats (author_id);

-- 应用查询时使用物化视图
SELECT u.display_name, s.published_count, s.total_views
FROM users u
JOIN mv_article_stats s ON u.id = s.author_id
ORDER BY s.total_views DESC;

2.10 常见问题与陷阱

字符集与编码问题

-- 1. 检查数据库编码
SELECT datname, pg_encoding_to_char(encoding) FROM pg_database;

-- 2. 检查客户端编码（应与数据库一致）
SHOW client_encoding;
SET client_encoding = 'UTF8';

-- 3. 检查字符串编码
SELECT convert_from('\xE4B8AD\xE6968E'::BYTEA, 'UTF8');  -- 解码字节序列

-- 4. 常见错误：invalid byte sequence for encoding "UTF8"
-- 原因：插入了非 UTF-8 字符，或客户端编码设置不匹配
-- 解决：统一使用 UTF8，确保数据源编码一致

-- 5. 中文排序
SELECT * FROM tags ORDER BY name COLLATE "zh_CN.UTF-8";
-- 或使用 ICU 排序（PostgreSQL 10+，需要编译时支持）
CREATE COLLATION zh_cn_icu (provider = icu, locale = 'zh-CN');

NULL 值处理

NULL 是 SQL 中最常见的陷阱之一：

-- 1. NULL 与任何值比较结果都是 NULL（不是 TRUE 也不是 FALSE）
SELECT NULL = NULL;        -- NULL（不是 TRUE！）
SELECT NULL != NULL;       -- NULL
SELECT NULL IS NULL;       -- TRUE（正确的 NULL 检测方式）
SELECT NULL IS NOT NULL;   -- FALSE

-- 2. NULL 在聚合中被忽略
SELECT COUNT(*), COUNT(age), AVG(age) FROM users;
-- COUNT(*) 计算所有行，COUNT(age) 跳过 NULL

-- 3. 排序中的 NULL（默认 NULLS LAST 升序，NULLS FIRST 降序）
SELECT * FROM users ORDER BY age ASC NULLS LAST;   -- NULL 排最后
SELECT * FROM users ORDER BY age DESC NULLS LAST;  -- 非 NULL 降序，NULL 最后

-- 4. COALESCE：返回第一个非 NULL 值
SELECT COALESCE(display_name, username, '匿名用户') FROM users;

-- 5. NULLIF：两值相等时返回 NULL（防除以零）
SELECT 100 / NULLIF(denominator, 0) FROM table1;

-- 6. NULL 与字符串拼接
SELECT 'Hello' || NULL;           -- NULL（注意！）
SELECT CONCAT('Hello', NULL);     -- Hello（CONCAT 忽略 NULL）

事务隔离级别

-- PostgreSQL 支持 4 个隔离级别
-- READ UNCOMMITTED（读未提交）— PostgreSQL 实际按 READ COMMITTED 处理
-- READ COMMITTED（读已提交）— 默认级别，每次读取获取最新快照
-- REPEATABLE READ（可重复读）— 事务内快照固定
-- SERIALIZABLE（串行化）— 最强，模拟串行执行

-- 查看当前隔离级别
SHOW transaction_isolation;

-- 设置隔离级别
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 各级别可能遇到的现象：
-- 脏读（Dirty Read）：PostgreSQL 所有级别均不会出现
-- 不可重复读：READ COMMITTED 可能出现，REPEATABLE READ 以上不会
-- 幻读：READ COMMITTED 和 REPEATABLE READ 可能出现，SERIALIZABLE 不会
-- 序列化异常：仅 SERIALIZABLE 级别避免

-- 示例：使用 REPEATABLE READ 避免报表数据不一致
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    SELECT SUM(amount) FROM orders WHERE created_at >= '2024-01-01';
    SELECT COUNT(*) FROM users WHERE created_at >= '2024-01-01';
COMMIT;

连接数限制

-- 查看连接数配置
SHOW max_connections;  -- 默认 100

-- 查看当前连接数
SELECT COUNT(*) FROM pg_stat_activity;
SELECT state, COUNT(*) FROM pg_stat_activity GROUP BY state;

-- 查看各数据库的最大连接数限制
SELECT datname, datconnlimit FROM pg_database;

-- 常见错误：FATAL: sorry, too many clients already
-- 解决方案：
-- 1. 增大 max_connections（需要重启，增加内存消耗）
-- 2. 使用连接池（推荐：PgBouncer）
-- 3. 关闭空闲连接（设置 idle_in_transaction_session_timeout）

-- 设置超时（避免长时间持有连接）
SET idle_in_transaction_session_timeout = '5min';    -- 空闲事务超时
SET statement_timeout = '30s';                         -- 语句执行超时
SET lock_timeout = '10s';                              -- 锁等待超时

-- 强制终止连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '1 hour'
  AND pid != pg_backend_pid();

常见错误信息解读

错误信息	含义	解决方法
`FATAL: role "xxx" does not exist`	角色不存在	创建对应角色或检查用户名
`FATAL: password authentication failed`	密码错误	检查密码，查看 pg_hba.conf
`ERROR: duplicate key value violates unique constraint`	唯一约束冲突	数据重复，使用 ON CONFLICT 处理
`ERROR: deadlock detected`	死锁	检查事务顺序，添加显式锁
`ERROR: could not serialize access due to concurrent update`	序列化冲突	降低隔离级别或重试
`ERROR: relation "xxx" does not exist`	表不存在	检查表名、模式、search_path
`ERROR: permission denied for table xxx`	无权限	使用 GRANT 授权
`FATAL: too many connections`	连接数超限	使用连接池
`ERROR: out of shared memory`	共享内存不足	增大 max_locks_per_transaction
`ERROR: value too long for type character varying(n)`	字符串超长	增大列长度或使用 TEXT

动手实验

实验 1：安装后的第一次探索

-- 1. 查看版本信息
SELECT version();
SHOW server_version;
SELECT current_setting('server_version_num')::INT;  -- 数字版本号，便于条件判断

-- 2. 列出所有数据库
\l
SELECT datname, datdba::regrole, pg_encoding_to_char(encoding),
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 3. 查看系统表（pg_catalog 模式下）
\dt pg_catalog.*         -- 列出所有系统表
\dv pg_catalog.*         -- 列出所有系统视图

-- 常用系统视图
SELECT * FROM pg_stat_activity;        -- 当前活动连接
SELECT * FROM pg_stat_user_tables;     -- 用户表统计
SELECT * FROM pg_stat_user_indexes;    -- 索引统计
SELECT * FROM pg_locks;                -- 当前锁信息
SELECT * FROM pg_settings LIMIT 20;   -- 配置参数

-- 查看所有配置参数
SELECT name, setting, unit, context, short_desc
FROM pg_settings
WHERE name LIKE '%memory%' OR name LIKE '%buffer%'
ORDER BY name;

-- 4. 使用帮助
\?              -- psql 元命令帮助
\h CREATE TABLE -- SQL 语法帮助
\h ALTER TABLE
\h GRANT

实验 2：创建一个图书管理系统

-- 创建数据库
CREATE DATABASE library
    ENCODING = 'UTF8'
    LC_COLLATE = 'C.UTF-8'
    LC_CTYPE = 'C.UTF-8'
    TEMPLATE = template0;

\c library

-- 设计并创建表结构
CREATE TABLE authors (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        TEXT NOT NULL,
    nationality TEXT,
    birth_year  SMALLINT,
    biography   TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE publishers (
    id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    TEXT NOT NULL,
    country TEXT,
    website TEXT
);

CREATE TABLE books (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn            VARCHAR(20) UNIQUE,
    title           TEXT NOT NULL,
    author_id       BIGINT NOT NULL REFERENCES authors(id),
    publisher_id    BIGINT REFERENCES publishers(id),
    publish_year    SMALLINT,
    pages           SMALLINT,
    price           NUMERIC(8,2),
    category        TEXT,
    description     TEXT,
    stock           INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE members (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    member_no   VARCHAR(20) NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE,
    phone       VARCHAR(20),
    join_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    expire_date DATE,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE borrows (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id         BIGINT NOT NULL REFERENCES books(id),
    member_id       BIGINT NOT NULL REFERENCES members(id),
    borrow_date     DATE NOT NULL DEFAULT CURRENT_DATE,
    due_date        DATE NOT NULL DEFAULT CURRENT_DATE + INTERVAL '30 days',
    return_date     DATE,
    late_fee        NUMERIC(6,2) DEFAULT 0
);

-- 创建索引
CREATE INDEX idx_books_author    ON books (author_id);
CREATE INDEX idx_books_category  ON books (category);
CREATE INDEX idx_books_title     ON books USING GIN (to_tsvector('simple', title));
CREATE INDEX idx_borrows_member  ON borrows (member_id);
CREATE INDEX idx_borrows_due     ON borrows (due_date) WHERE return_date IS NULL;

-- 插入示例数据
INSERT INTO authors (name, nationality, birth_year) VALUES
('金庸',         '中国', 1924),
('余华',         '中国', 1960),
('Gabriel García Márquez', '哥伦比亚', 1927),
('George Orwell',          '英国',     1903);

INSERT INTO publishers (name, country) VALUES
('人民文学出版社', '中国'),
('上海文艺出版社', '中国'),
('Harper Collins', '美国');

INSERT INTO books (isbn, title, author_id, publisher_id, publish_year, price, category, stock) VALUES
('9787020014705', '射雕英雄传',   1, 1, 2003, 88.00,  '武侠小说', 10),
('9787020028672', '天龙八部',     1, 1, 2006, 128.00, '武侠小说', 5),
('9787532130931', '活着',         2, 2, 2012, 35.00,  '现代小说', 20),
('9787532175208', '许三观卖血记', 2, 2, 2018, 39.50,  '现代小说', 15),
('9780060929589', '1984',         4, 3, 1949, 45.00,  '政治小说', 8);

INSERT INTO members (member_no, name, email, phone) VALUES
('M001', '张伟', 'zhangwei@mail.com', '13800138001'),
('M002', '李娜', 'lina@mail.com',     '13800138002'),
('M003', '王强', 'wangqiang@mail.com','13800138003');

INSERT INTO borrows (book_id, member_id, borrow_date, due_date, return_date) VALUES
(1, 1, '2024-01-10', '2024-02-10', '2024-02-05'),  -- 已还
(3, 2, '2024-02-01', '2024-03-03', NULL),            -- 未还
(5, 1, '2024-02-15', '2024-03-17', NULL);            -- 未还

-- 执行各类查询

-- 查询所有未还书的借阅记录
SELECT
    b.title,
    m.name AS member_name,
    br.borrow_date,
    br.due_date,
    CASE
        WHEN br.due_date < CURRENT_DATE THEN '已逾期 ' || (CURRENT_DATE - br.due_date) || ' 天'
        ELSE '还有 ' || (br.due_date - CURRENT_DATE) || ' 天到期'
    END AS status
FROM borrows br
JOIN books b ON br.book_id = b.id
JOIN members m ON br.member_id = m.id
WHERE br.return_date IS NULL
ORDER BY br.due_date;

-- 每位作者的书籍数量和库存
SELECT
    a.name AS author,
    COUNT(b.id) AS book_count,
    SUM(b.stock) AS total_stock,
    MIN(b.price) AS min_price,
    MAX(b.price) AS max_price
FROM authors a
LEFT JOIN books b ON a.id = b.author_id
GROUP BY a.id, a.name
ORDER BY book_count DESC;

-- 创建索引并对比查询性能
EXPLAIN ANALYZE SELECT * FROM books WHERE to_tsvector('simple', title) @@ to_tsquery('simple', '英雄');

实验 3：用户权限管理实践

\c library

-- 1. 创建不同权限的角色
CREATE ROLE lib_readonly;
CREATE ROLE lib_staff;
CREATE ROLE lib_admin;

-- 授予模式访问权限
GRANT USAGE ON SCHEMA public TO lib_readonly, lib_staff, lib_admin;

-- 只读角色：只能查询
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lib_readonly;

-- 馆员角色：可以管理借阅记录
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lib_staff;
GRANT INSERT, UPDATE ON borrows TO lib_staff;
GRANT UPDATE (stock) ON books TO lib_staff;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO lib_staff;

-- 管理员角色：完全控制
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO lib_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO lib_admin;

-- 2. 创建对应用户
CREATE USER lib_reader WITH PASSWORD 'Reader123!';
CREATE USER lib_worker WITH PASSWORD 'Worker456!';

GRANT lib_readonly TO lib_reader;
GRANT lib_staff    TO lib_worker;

-- 3. 测试权限控制
-- 以 lib_reader 身份连接测试
\c library lib_reader
SELECT * FROM books LIMIT 3;            -- 应该成功
INSERT INTO books (title, author_id) VALUES ('测试', 1);  -- 应该失败

-- 以 lib_worker 身份连接测试
\c library lib_worker
UPDATE books SET stock = stock - 1 WHERE id = 1;  -- 应该成功
DELETE FROM books WHERE id = 1;                    -- 应该失败

-- 4. 实现行级安全策略（RLS）
\c library postgres

-- 假设我们只想让馆员查看自己负责区域的书
ALTER TABLE books ENABLE ROW LEVEL SECURITY;

-- 创建策略：基于 category 的访问控制
CREATE POLICY books_category_policy ON books
    FOR SELECT
    USING (
        category = current_setting('app.user_category', TRUE)
        OR current_user = 'postgres'
        OR pg_has_role(current_user, 'lib_admin', 'member')
    );

-- 测试 RLS
\c library lib_reader
SET app.user_category = '武侠小说';
SELECT title, category FROM books;   -- 只能看武侠小说

SET app.user_category = '现代小说';
SELECT title, category FROM books;   -- 只能看现代小说

-- 禁用 RLS（管理员）
\c library postgres
ALTER TABLE books DISABLE ROW LEVEL SECURITY;

初学者常见问题

1. PostgreSQL 和 MySQL 有什么主要区别？

核心差异：

SQL 标准支持：PostgreSQL 对 SQL 标准的支持更全面，窗口函数、CTE、递归查询等高级特性早于 MySQL 实现。
数据类型：PostgreSQL 提供更丰富的类型（JSONB、数组、范围、几何等），MySQL 相对简单。
并发模型：两者均使用 MVCC，但 PostgreSQL 的 MVCC 实现更彻底，读写冲突更少。
存储引擎：MySQL 有多存储引擎（InnoDB/MyISAM），PostgreSQL 只有一种存储引擎（更统一稳定）。
AUTO_INCREMENT vs SERIAL：MySQL 用 AUTO_INCREMENT，PostgreSQL 用 SERIAL/GENERATED ALWAYS AS IDENTITY。
字符串处理：PostgreSQL 严格区分 ''（空字符串）和 NULL；MySQL 可以将 '' 当作某种形式的 NULL。
性能特点：MySQL 在简单 OLTP（点查询）上略快；PostgreSQL 在复杂查询、JOIN、分析场景下更优秀。

2. 什么时候应该使用 PostgreSQL？

推荐使用 PostgreSQL 的场景：

需要复杂查询（JOIN、子查询、窗口函数）
数据模型包含 JSON、数组等半结构化数据
需要地理空间功能（PostGIS）
需要全文检索
金融或需要严格 ACID 的场景
需要高级数据类型（范围、枚举、UUID）
正在从 Oracle 迁移
需要向量检索（AI 场景，pgvector）

MySQL 更合适的场景：

极简单的 CRUD 应用，团队 MySQL 经验丰富
需要 GROUP BY 的低版本 MySQL 特有行为
与 WordPress、Drupal 等默认使用 MySQL 的 CMS 配合

3. 如何选择合适的数据类型？

黄金原则：

需求	推荐类型
整数主键	`BIGINT GENERATED ALWAYS AS IDENTITY`
金钱/精确小数	`NUMERIC(15, 2)`
浮点科学数据	`DOUBLE PRECISION`
短字符串（需限长）	`VARCHAR(n)`
长文本/任意文本	`TEXT`
日期时间（有时区）	`TIMESTAMPTZ`
纯日期	`DATE`
布尔	`BOOLEAN`
唯一标识符	`UUID`
半结构化数据	`JSONB`
标签/多值	`TEXT[]`（数组）
有限枚举	`ENUM` 类型或 `SMALLINT`（更灵活）

4. 什么是 MVCC，它如何影响我的应用？

MVCC 的实际影响：

好的方面：

读操作不需要锁，不会被写操作阻塞
长时间的只读查询（报表）不影响 OLTP 写入
事务隔离性好

需要注意的方面：

表膨胀：频繁 UPDATE/DELETE 会产生大量死元组，表文件会膨胀
需要 VACUUM：必须定期执行 VACUUM 清理死元组（通常由 autovacuum 自动完成）
事务 ID 回卷（XID wraparound）：极端情况下需要特殊处理，监控好 pg_database.datfrozenxid

应用层建议：

不要开启事务后长时间不操作（设置 idle_in_transaction_session_timeout）
批量删除/更新时分批处理，给 autovacuum 时间工作
监控 n_dead_tup 指标，超过阈值时手动 VACUUM

5. 如何处理中文字符和时区问题？

字符集最佳实践：

-- 创建支持中文的数据库
CREATE DATABASE mydb
    ENCODING = 'UTF8'
    LC_COLLATE = 'C.UTF-8'   -- 推荐 C.UTF-8，性能好，支持中文存储
    LC_CTYPE = 'C.UTF-8'
    TEMPLATE = template0;

-- 确保客户端连接也使用 UTF-8
SET client_encoding = 'UTF8';

-- postgresql.conf 中设置默认值
-- client_encoding = 'UTF8'

时区最佳实践：

-- 数据库层面（postgresql.conf）
-- timezone = 'UTC'           -- 推荐服务器统一使用 UTC

-- 所有时间字段使用 TIMESTAMPTZ（自动存储 UTC，按会话时区展示）
CREATE TABLE events (
    event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 应用连接时设置用户时区
SET timezone = 'Asia/Shanghai';

-- 存储时自动转 UTC
INSERT INTO events VALUES (NOW());  -- 存储为 UTC

-- 查询时自动转为 Asia/Shanghai 展示
SELECT event_time FROM events;

6. 连接池是什么，为什么需要它？

PostgreSQL 采用每连接一个进程的模型，建立新连接的开销较大（约 1-10ms，消耗约 5-10MB 内存）。

连接池的作用：

维护一定数量的持久连接，应用请求时复用
减少连接建立开销
控制数据库的实际连接数

主流连接池工具：

工具	特点
PgBouncer	轻量级，C 实现，最流行，支持 transaction/session/statement 模式
Pgpool-II	功能丰富，支持读写分离和负载均衡
应用内连接池	HikariCP（Java）、SQLAlchemy Pool（Python）等

# PgBouncer 快速配置示例（pgbouncer.ini）
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction        # 事务级别池化（推荐）
max_client_conn = 1000         # 最大客户端连接
default_pool_size = 20         # 每个数据库的连接池大小

7. 如何备份和恢复数据库？

# 逻辑备份（pg_dump）
# 备份单个数据库（推荐 custom 格式）
pg_dump -U postgres -Fc myapp > myapp_$(date +%Y%m%d).dump

# 备份为 SQL 文本（便于查看）
pg_dump -U postgres -Fp myapp > myapp_$(date +%Y%m%d).sql

# 备份所有数据库（含角色、表空间）
pg_dumpall -U postgres > cluster_$(date +%Y%m%d).sql

# 恢复
pg_restore -U postgres -d myapp_restore -Fc myapp_backup.dump

# 并行备份/恢复（加速大库操作）
pg_dump -U postgres -Fd -j 8 -f /backup/myapp_dir myapp
pg_restore -U postgres -d myapp_restore -Fd -j 8 /backup/myapp_dir

# 物理备份（pg_basebackup，用于 PITR）
pg_basebackup -U replication -h localhost -D /backup/base -Ft -z -Xs -P

8. 如何监控数据库性能？

-- 实时监控视图

-- 1. 当前活跃查询
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > INTERVAL '5 seconds'
  AND state != 'idle';

-- 2. 锁等待
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid    AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
    AND blocking_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- 3. 缓冲区命中率
SELECT
    schemaname, relname,
    heap_blks_read, heap_blks_hit,
    round(heap_blks_hit * 100.0 / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC;

-- 4. 表膨胀监控
SELECT relname,
       n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS bloat_ratio,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY bloat_ratio DESC;

监控工具推荐：

pgBadger：日志分析，生成 HTML 报告
pg_activity：类似 top 的实时监控
Prometheus + postgres_exporter：指标采集
Grafana：可视化仪表盘
pganalyze：商业监控平台

9. 什么是 VACUUM，为什么需要它？

由于 MVCC 机制，DELETE 和 UPDATE 操作留下的死元组不会立即被删除，VACUUM 的作用是：

回收死元组占用的空间（供同一表的新数据使用）
更新统计信息（VACUUM ANALYZE，供查询优化器使用）
防止事务 ID 回卷（推进 relfrozenxid）

-- 手动执行 VACUUM
VACUUM users;                    -- 普通 VACUUM（不锁表）
VACUUM ANALYZE users;            -- VACUUM + 更新统计信息
VACUUM FULL users;               -- 完全整理表，回收空间到 OS（会锁表！）
VACUUM VERBOSE users;            -- 显示详细信息

-- 查看 autovacuum 配置
SELECT name, setting FROM pg_settings WHERE name LIKE '%autovacuum%';

-- 查看最近一次 autovacuum 时间
SELECT relname, last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

Autovacuum 最佳实践：

不要关闭 autovacuum（autovacuum = on 保持默认）
对频繁更新的表调整触发阈值

# postgresql.conf 针对高频更新表的调优
autovacuum_vacuum_scale_factor = 0.05   # 默认 0.2，表数据 5% 变化触发（更积极）
autovacuum_analyze_scale_factor = 0.02  # 默认 0.1，2% 变化触发 ANALYZE

10. 如何从其他数据库迁移到 PostgreSQL？

迁移路径：

从 MySQL 迁移：

# 使用 pgloader（推荐，自动转换 MySQL 语法）
pgloader mysql://user:pass@host/dbname postgresql://user:pass@host/dbname

# pgloader 配置文件示例
LOAD DATABASE
    FROM mysql://root:pass@localhost/mydb
    INTO postgresql://postgres:pass@localhost/mydb

    WITH include drop, create tables, create indexes,
         reset sequences, foreign keys

    SET MySQL PARAMETERS
        net_read_timeout  = '120',
        net_write_timeout = '120'
;

从 Oracle 迁移：

使用 Ora2Pg（开源，功能全面）
使用 AWS Schema Conversion Tool（SCT）
主要差异：PL/SQL → PL/pgSQL，ROWNUM → LIMIT，SYSDATE → NOW()

通用迁移步骤：

评估：分析源数据库对象、数据类型、存储过程
Schema 转换：DDL 语句调整（数据类型映射、函数替换）
数据迁移：全量数据迁移（可使用 COPY 命令高速导入）
存储过程迁移：将 PL/SQL 转换为 PL/pgSQL
应用层调整：SQL 方言差异修正
性能验证：查询计划对比，添加缺失索引
并行运行：新旧系统并行验证数据一致性
切换上线：DNS/连接字符串切换

总结

通过本章的学习，你已经完成了 PostgreSQL 入门的关键步骤：

✅ 了解了 PostgreSQL 的历史发展和核心优势
✅ 掌握了集群、数据库、模式、表等核心概念的层次关系
✅ 理解了 PostgreSQL 的进程、内存、存储三层体系结构
✅ 掌握了 WAL 和 MVCC 的基本原理
✅ 完成了第一个数据库的创建和基本 CRUD 操作
✅ 学会了使用 psql 命令行工具和图形化管理工具
✅ 掌握了 SQL 的四大类别（DDL/DML/DCL/TCL）及 PostgreSQL 方言特性
✅ 了解了 PostgreSQL 丰富的数据类型体系
✅ 学会了表、索引、约束、视图、序列、函数等基本对象管理
✅ 掌握了用户权限管理和 pg_hba.conf 认证配置
✅ 完成了博客系统实战案例
✅ 解答了 10 个初学者最常见的问题

下一步学习建议：

深入学习查询优化和执行计划分析
掌握 PostgreSQL 的高可用方案（流复制、逻辑复制）
学习分区表和表分区策略
探索 PostGIS、pgvector 等强大扩展
掌握生产环境的监控、备份和运维实践

参考资料：

PostgreSQL 官方文档

PostgreSQL Wiki

pgExercises 在线练习

PostgreSQL 中文社区

《PostgreSQL：Up and Running》— Regina O. Obe, Leo S. Hsu

《The Art of PostgreSQL》— Dimitri Fontaine

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

终极指南：Flink SQL连接器版本管理从混乱到有序的升级之路

Apache Flink作为流处理领域的佼佼者，其SQL连接器的版本管理一直是开发者面临的核心挑战。本文将系统讲解Flink SQL连接器版本管理的最佳实践，帮助你轻松应对版本兼容性问题，实现从混乱到有序的升级之旅。## 连接器版本管理的常见痛点 😫在Flink应用开发中，连接器版本管理常常让开发者头疼不已。不同版本的连接器可能导致各种兼容性问题，例如API变更、功能差异甚至运行时错误。

腾讯云开发者社区

Elasticsearch复杂数据类型终极指南：从入门到精通

Elasticsearch作为功能强大的搜索引擎，支持多种复杂数据类型，让开发者能够灵活处理各种结构化和非结构化数据。本文将带你全面了解Elasticsearch中的复杂数据类型，从基础概念到实际应用，助你轻松掌握数据建模的核心技巧。## 内部对象：构建层级化数据结构在Elasticsearch中，对象类型（Object）是最基础的复杂数据类型之一，用于表示具有嵌套关系的数据。例如，我们可

腾讯云开发者社区

如何快速搭建Neon无服务器PostgreSQL：面向初学者的完整指南

Neon是一款革命性的无服务器PostgreSQL解决方案，它通过分离存储和计算层，实现了自动扩缩容、类代码式数据库分支以及零级扩展能力。本指南将帮助你从零开始搭建Neon开发环境，体验这款创新数据库的强大功能。## 准备工作：环境要求与依赖项在开始搭建Neon环境前，请确保你的系统满足以下要求：- Linux操作系统（推荐Ubuntu 20.04+或Debian 11+）- Git