PostgreSQL 从零到精通学习大纲

class PostgreSQL,数据库

第一阶段：入门基础 (1-2周)

1. PostgreSQL 简介与安装

  • 1.1 PostgreSQL 概述

    • PostgreSQL 历史与发展
    • 特性与优势（ACID、扩展性、开源协议）
    • 与其他数据库对比（MySQL、Oracle、SQL Server）

  • 1.2 安装部署

    • Windows 安装
    • Linux (Ubuntu/CentOS) 安装
    • macOS 安装
    • Docker 容器化部署
    • 源码编译安装

  • 1.3 客户端工具

    • psql 命令行工具
    • pgAdmin 图形界面
    • DBeaver、DataGrip 等第三方工具

2. 基础架构与配置

  • 2.1 目录结构

    • 数据目录（PGDATA）
    • 配置文件位置
    • 表空间目录

  • 2.2 核心配置文件

    • postgresql.conf（主配置）
    • pg_hba.conf（客户端认证）
    • pg_ident.conf（用户映射）

  • 2.3 服务管理

    • 启动/停止/重启
    • 服务状态查看
    • 日志管理

第二阶段：SQL 语言精通 (3-4周)

3. SQL 基础

  • 3.1 数据定义语言（DDL）

    • CREATE/DROP DATABASE
    • CREATE/ALTER/DROP TABLE
    • CREATE/ALTER/DROP INDEX
    • CREATE/ALTER/DROP VIEW
    • 约束管理（PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL）

  • 3.2 数据操作语言（DML）

    • SELECT 查询基础
      • WHERE 子句
      • ORDER BY 排序
      • LIMIT/OFFSET 分页
      • DISTINCT 去重
    • INSERT 插入数据
    • UPDATE 更新数据
    • DELETE 删除数据
    • MERGE/UPSERT (INSERT ON CONFLICT)

  • 3.3 数据查询语言（DQL）高级

    • 聚合函数（COUNT、SUM、AVG、MAX、MIN）
    • GROUP BY 分组
    • HAVING 过滤分组
    • 子查询（标量、行、表子查询）
    • 联合查询（UNION、INTERSECT、EXCEPT）

4. 高级 SQL 特性

  • 4.1 连接查询

    • INNER JOIN
    • LEFT/RIGHT/FULL OUTER JOIN
    • CROSS JOIN
    • SELF JOIN
    • NATURAL JOIN

  • 4.2 窗口函数

    • ROW_NUMBER、RANK、DENSE_RANK
    • LAG、LEAD、FIRST_VALUE、LAST_VALUE
    • SUM/AVG OVER(PARTITION BY)
    • 滑动窗口

  • 4.3 公共表表达式（CTE）

    • 普通 CTE
    • 递归 CTE（树形结构查询）
    • 物化 CTE

  • 4.4 高级查询技术

    • 条件逻辑（CASE、COALESCE、NULLIF）
    • 批量操作（批量插入、更新、删除）
    • 返回子句（RETURNING）

第三阶段：数据类型与函数 (2-3周)

5. 数据类型详解

  • 5.1 数值类型

    • 整数类型（SMALLINT、INTEGER、BIGINT）
    • 浮点类型（REAL、DOUBLE PRECISION）
    • 精确数值（NUMERIC、DECIMAL）
    • 序列类型（SMALLSERIAL、SERIAL、BIGSERIAL）

  • 5.2 字符类型

    • CHAR(n)、VARCHAR(n)、TEXT
    • 字符编码与排序规则

  • 5.3 日期时间类型

    • DATE、TIME、TIMESTAMP
    • INTERVAL（时间间隔）
    • 时区处理（TIMESTAMPTZ）

  • 5.4 布尔类型

    • TRUE、FALSE、NULL

  • 5.5 二进制类型

    • BYTEA
    • 大对象（Large Objects）

  • 5.6 网络地址类型

    • INET、CIDR、MACADDR

  • 5.7 JSON/JSONB 类型

    • JSON 操作符（->、->>、#>、#>>）
    • JSONB 索引
    • JSON 函数与操作

  • 5.8 数组类型

    • 一维/多维数组
    • 数组函数与操作符

  • 5.9 范围类型

    • INT4RANGE、INT8RANGE、NUMRANGE
    • TSRANGE、TSTZRANGE、DATERANGE

  • 5.10 几何类型

    • POINT、LINE、LSEG、BOX、PATH、POLYGON、CIRCLE

  • 5.11 复合类型

    • 自定义复合类型
    • 复合类型的使用

6. 函数与操作符

  • 6.1 数学函数
  • 6.2 字符串函数
  • 6.3 日期时间函数
  • 6.4 类型转换函数
  • 6.5 条件函数
  • 6.6 聚合函数
  • 6.7 窗口函数
  • 6.8 自定义函数

第四阶段：数据库设计 (2-3周)

7. 数据库设计理论

  • 7.1 设计原则

    • 需求分析
    • 概念设计（ER 图）
    • 逻辑设计（关系模式）
    • 物理设计（存储结构）

  • 7.2 规范化理论

    • 第一范式（1NF）
    • 第二范式（2NF）
    • 第三范式（3NF）
    • BC范式（BCNF）
    • 第四/第五范式
    • 反规范化设计

  • 7.3 表设计最佳实践

    • 主键设计策略（自然键 vs 代理键）
    • 外键约束使用
    • 检查约束应用
    • 默认值设置
    • NULL 值处理策略

8. 索引设计

  • 8.1 索引类型

    • B-Tree 索引（默认）
    • Hash 索引
    • GiST 索引
    • SP-GiST 索引
    • GIN 索引（全文搜索、JSONB）
    • BRIN 索引（大表）
    • 部分索引
    • 表达式索引

  • 8.2 索引策略

    • 单列索引
    • 复合索引（多列索引）
    • 唯一索引
    • 覆盖索引
    • 索引与排序

  • 8.3 索引维护

    • 索引创建（CONCURRENTLY）
    • 索引重建
    • 索引统计信息
    • 索引使用情况分析

9. 视图与物化视图

  • 9.1 普通视图
  • 9.2 物化视图
    • 创建与刷新策略
    • 并发刷新
    • 物化视图索引

10. 数据库架构设计

  • 10.1 模式（Schema）管理

    • 公共模式
    • 私有模式
    • 跨模式访问

  • 10.2 分区表

    • 范围分区
    • 列表分区
    • 哈希分区
    • 分区维护与管理

  • 10.3 继承表

第五阶段：权限管理 (1-2周)

11. 用户与角色管理

  • 11.1 角色概念

    • 登录角色 vs 组角色
    • 角色属性（LOGIN、SUPERUSER、CREATEDB、CREATEROLE）

  • 11.2 角色管理

    • CREATE/ALTER/DROP ROLE
    • 角色继承
    • 默认角色

12. 权限体系

  • 12.1 权限类型

    • 系统权限（CREATE、CONNECT、TEMPORARY）
    • 对象权限（SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER）
    • 模式权限（USAGE、CREATE）
    • 数据库权限（CONNECT、CREATE、TEMPORARY）

  • 12.2 权限管理命令

    • GRANT（授予权限）
    • REVOKE（回收权限）
    • 权限查看（\dp、\z）

  • 12.3 行级安全策略（RLS）

    • 策略创建与启用
    • USING 与 WITH CHECK 表达式
    • 策略类型（PERMISSIVE、RESTRICTIVE）

13. 连接安全

  • 13.1 pg_hba.conf 配置

    • 认证方法（trust、reject、md5、scram-sha-256）
    • 连接规则（local、host、hostssl、hostnossl）

  • 13.2 SSL/TLS 配置

    • 证书生成
    • 强制加密连接

第六阶段：性能优化 (3-4周)

14. 查询优化

  • 14.1 执行计划分析

    • EXPLAIN 与 EXPLAIN ANALYZE
    • 执行计划解读（Seq Scan、Index Scan、Bitmap Scan）
    • 代价估算（启动成本、总成本、行数、宽度）

  • 14.2 查询重写技巧

    • 避免隐式类型转换
    • 优化 JOIN 顺序
    • 子查询优化（EXISTS vs IN）
    • 避免 SELECT *
    • 使用 LIMIT 减少结果集

  • 14.3 统计信息

    • ANALYZE 命令
    • 统计信息查看（pg_stats）
    • 统计信息设置（default_statistics_target）

15. 服务器配置优化

  • 15.1 内存配置

    • shared_buffers（共享缓冲区）
    • effective_cache_size（有效缓存大小）
    • work_mem（工作内存）
    • maintenance_work_mem（维护工作内存）
    • huge_pages（大页内存）

  • 15.2 磁盘 I/O 优化

    • random_page_cost（随机页成本）
    • effective_io_concurrency（有效 I/O 并发）
    • 表空间分布

  • 15.3 写入优化

    • wal_buffers（WAL 缓冲区）
    • checkpoint 配置
    • fsync 与 full_page_writes

  • 15.4 连接管理

    • max_connections（最大连接数）
    • 连接池配置（PgBouncer）

16. 事务与并发控制

  • 16.1 MVCC 机制

    • 事务ID（XID）
    • 元组可见性规则
    • 快照隔离级别

  • 16.2 事务隔离级别

    • READ UNCOMMITTED
    • READ COMMITTED（默认）
    • REPEATABLE READ
    • SERIALIZABLE

  • 16.3 锁机制

    • 表级锁（ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE）
    • 行级锁
    • 死锁检测与处理
    • advisory locks

17. 数据库维护

  • 17.1 VACUUM 与清理

    • VACUUM 工作原理
    • 死元组处理
    • VACUUM vs VACUUM FULL
    • AUTOVACUUM 配置

  • 17.2 日志管理

    • WAL 日志归档
    • 日志参数配置（log_destination、log_directory）
    • 慢查询日志

第七阶段：高级特性 (2-3周)

18. 存储过程与触发器

  • 18.1 PL/pgSQL

    • 语法结构
    • 变量与常量
    • 控制结构（IF、CASE、LOOP、WHILE、FOR）
    • 异常处理

  • 18.2 函数与存储过程

    • 函数创建与调用
    • 存储过程创建与调用
    • 返回值类型
    • 事务控制

  • 18.3 触发器

    • 触发器类型（BEFORE、AFTER、INSTEAD OF）
    • 触发器事件（INSERT、UPDATE、DELETE、TRUNCATE）
    • 行级与语句级触发器
    • 条件触发器

  • 18.4 事件触发器

    • DDL 事件捕获

19. 全文搜索

  • 19.1 全文搜索基础

    • 文本解析（tsvector、tsquery）
    • 搜索配置

  • 19.2 搜索功能

    • @@ 操作符
    • 排名函数（ts_rank、ts_rank_cd）
    • 高亮显示（ts_headline）
    • 字典与同义词

20. 扩展模块

  • 20.1 常用扩展

    • pg_stat_statements（查询统计）
    • pg_trgm（模糊匹配）
    • uuid-ossp（UUID 生成）
    • PostGIS（地理空间）
    • hstore（键值存储）
    • pgcrypto（加密函数）

  • 20.2 扩展管理

    • CREATE EXTENSION
    • 扩展更新与删除

第八阶段：备份恢复与高可用 (2-3周)

21. 备份与恢复

  • 21.1 逻辑备份

    • pg_dump（单数据库）
    • pg_dumpall（全部数据库）
    • 自定义格式备份
    • 并行备份

  • 21.2 物理备份

    • 冷备份
    • 热备份（pg_basebackup）
    • 文件系统快照

  • 21.3 时间点恢复（PITR）

    • WAL 归档配置
    • 基础备份 + WAL 恢复
    • 恢复到指定时间点

  • 21.4 恢复策略

    • 完全恢复
    • 部分恢复
    • 灾难恢复演练

22. 高可用与集群

  • 22.1 流复制

    • 主从复制原理
    • 异步复制
    • 同步复制
    • 级联复制

  • 22.2 高可用方案

    • Patroni + etcd/Consul
    • Repmgr
    • PGPool-II
    • 自动故障转移

  • 22.3 读写分离

    • 连接池配置
    • 负载均衡策略

  • 22.4 分布式方案

    • Citus（水平分片）
    • Postgres-XL

第九阶段：监控与运维 (1-2周)

23. 监控体系

  • 23.1 系统视图与统计信息

    • pg_stat_database（数据库统计）
    • pg_stat_tables（表统计）
    • pg_stat_activity（会话监控）
    • pg_stat_replication（复制监控）
    • pg_locks（锁监控）

  • 23.2 监控工具

    • Prometheus + Grafana
    • Zabbix
    • Nagios
    • pg_stat_statements 分析

  • 23.3 性能指标

    • QPS/TPS
    • 缓存命中率
    • 连接数使用率
    • 事务回滚率
    • 死锁检测

24. 日常运维

  • 24.1 容量规划

    • 数据库大小监控
    • 表膨胀检测
    • 索引大小管理

  • 24.2 版本升级

    • 大版本升级（pg_upgrade）
    • 小版本升级
    • 升级策略与回滚方案

  • 24.3 问题排查

    • 慢查询分析
    • 连接数耗尽处理
    • 事务 ID 回卷
    • 表膨胀处理

第十阶段：项目实战 (3-4周)

25. 实战项目

  • 25.1 电商系统数据库设计

    • 用户、商品、订单、库存设计
    • 购物车实现
    • 促销活动设计

  • 25.2 博客/内容管理系统

    • 文章、评论、标签设计
    • 全文搜索实现
    • 分类与归档

  • 25.3 实时数据分析系统

    • 时序数据存储
    • 物化视图应用
    • 窗口函数分析

  • 25.4 地理信息系统

    • PostGIS 应用
    • 空间查询与计算

26. 性能调优实战

  • 26.1 基准测试

    • pgbench 使用
    • 自定义压测脚本

  • 26.2 生产环境调优案例

    • 慢查询优化实例
    • 索引优化实战
    • 配置参数调优

27. 生产环境部署

  • 27.1 部署清单

    • 操作系统优化
    • 数据库配置检查
    • 安全加固

  • 27.2 自动化运维

    • Ansible 部署脚本
    • Docker/K8s 容器化部署
    • 备份自动化

学习资源推荐

官方文档

  • PostgreSQL 官方文档
  • PostgreSQL Wiki

推荐书籍

  • 《PostgreSQL 修炼之道》
  • 《PostgreSQL 即学即用》
  • 《PostgreSQL 高可用实战》

在线资源

  • PostgreSQL 中文社区
  • Stack Overflow
  • GitHub 优秀项目

实践环境

  • 本地虚拟机
  • Docker 容器
  • 云数据库服务（AWS RDS、阿里云 RDS）

学习路径建议

  1. 第1-2月：重点学习 SQL 基础和数据类型
  2. 第3-4月：深入数据库设计和索引优化
  3. 第5-6月：掌握性能调优和高级特性
  4. 第7-8月：学习高可用和运维监控
  5. 第9-10月：实战项目 + 性能调优

技能等级评估

  • 初级：掌握 SQL 基础、表设计、基本查询
  • 中级：掌握索引优化、存储过程、备份恢复
  • 高级：掌握性能调优、高可用架构、分布式部署
  • 专家：源码分析、扩展开发、社区贡献
评论区
评论列表
menu