1. 为什么这份 PostgreSQL 笔记值得你从头读完先说明一下这篇内容不是什么官方文档翻译也不是照着教程敲命令的流水账。我做后端开发和数据库运维有些年头了PostgreSQL 从 9.x 一路用到现在的 15、16期间踩过不少坑、走过不少弯路也积累了一些“文档里不会明说但实际特别有用”的经验。这次借着整理笔记的机会把和 PostgreSQL 相关的安装、日常使用、版本选型、结构对比、常见问题全部梳理一遍希望能帮到正在入门或者已经用了一段时间但总觉得差点意思的同学。很多人在 MySQL 和 PostgreSQL 之间纠结。我刚工作时用的也是 MySQL后来因为项目需要接触了 PostgreSQL才意识到这套数据库远比想象中强大。它不仅仅是“开源的关系型数据库”这么简单在 JSON 处理、全文检索、地理信息、复杂查询、数据完整性约束这些方面PostgreSQL 都有非常扎实的设计。如果你还在犹豫要不要用或者已经在用了但想更深入地掌握它这份笔记应该能给你一个比较完整的视角。我要讲的不是那种“复制粘贴就能跑”的速成教程而是把每个关键操作背后的逻辑说清楚让你知道为什么这么配置、为什么用这个工具、为什么踩了坑之后要这样排查。这样才能做到真正理解 PostgreSQL而不是只会背命令。接下来我会按照一条从零开始的实际使用路径来展开从安装部署讲起到日常操作、版本对比、结构同步最后梳理高频问题的排查思路。2. PostgreSQL 与 MySQL、SQLite 的核心差异以及选型思路2.1 三者的定位不同决定了你该怎么选很多初学者会把 PostgreSQL、MySQL、SQLite 放在一起比较然后问“哪个更好”。这个问题的前提就有问题因为这三者的定位本身就不太一样。SQLite 是嵌入式关系型数据库它以库文件的形式存在不需要独立的服务进程适合移动端、桌面端、嵌入式设备或者原型验证阶段。优点就是零配置、轻量、部署简单缺点是并发写入能力弱不适合多用户高并发的在线业务也没有完整的用户权限体系。MySQL 是经典的关系型数据库生态成熟、使用人数多尤其在国内互联网行业有着非常深厚的基础。它在读多写少、水平扩展方面有成熟的方案运维资料也极其丰富。如果是传统的 Web 业务MySQL 仍然是可靠的选择。PostgreSQL 在定位上更接近“功能完整的企业级数据库”。它在数据类型、约束机制、事务能力、扩展性方面做得非常扎实对 SQL 标准的遵循程度也更高。比如复杂查询的优化器、递归查询、窗口函数、CTE这些在 PostgreSQL 里用起来非常顺手而且性能相当稳定。对于数据完整性要求高、查询逻辑复杂、需要使用 JSON 或地理位置这类特殊数据类型的场景PostgreSQL 的优势会非常明显。如果你做的是中小规模业务团队对数据库没有强烈的历史依赖而且需要应对越来越复杂的查询需求那我个人认为 PostgreSQL 是更值得投入的方向。它不会让你在业务复杂度上去之后面临“功能不够用”的窘境。2.2 PostgreSQL 相比 MySQL 的几个关键优势从使用体验上来说我认为最有感知差异的点集中在以下几个方面。第一是约束与数据完整性。PostgreSQL 的表可以定义非常细致的检查约束、外键约束、唯一约束同时对通过约束的数据校验执行得一丝不苟。MySQL 在某些存储引擎如 MyISAM下根本不支持外键InnoDB 支持外键但默认不强制使用。简单说PostgreSQL 是默认帮你守住数据底线的而 MySQL 更多时候靠开发者自觉。第二是 JSON 支持。PostgreSQL 的 JSONB 类型是二进制存储的可以直接在 JSON 字段上建索引、做条件查询这对很多需要存储半结构化数据的场景非常友好。MySQL 的 JSON 类型虽然也能用但灵活性和性能表现都还有差距。第三是索引类型丰富。除了常规的 B-Tree 索引PostgreSQL 还支持 GIN、GiST、BRIN、SP-GiST 等索引类型。比如 GIN 索引适合全文检索和数组类型BRIN 索引适合超大表上的时间序列数据。写 SQL 时可以针对数据特征选择更合适的索引这是 MySQL 很难做到的。第四是可扩展性。PostgreSQL 允许你定义自定义数据类型、自定义函数支持 PL/pgSQL、Python、C 等、自定义聚合函数甚至可以把自己写的索引方法挂进去。对于业务有特殊需求的开发者来说这种扩展能力非常珍贵。2.3 不要忽略 PostgreSQL 的“学习曲线”当然PostgreSQL 也不是没有门槛。它比 MySQL 更严格这个“严格”对新手来说就是学习成本。举个例子你在 PostgreSQL 里写一个带 GROUP BY 的查询如果 SELECT 的字段没有被聚合它会直接报错而 MySQL 在默认设置下可能就直接给你返回一行不明不白的数据。刚开始用的时候可能会觉得它在找麻烦但用久了你会明白这是它在帮你防呆。另外 PostgreSQL 的生态工具没有 MySQL 那么多花花绿绿的第三方管理面板虽然 pgAdmin 和 DBeaver 都能用但很多操作还是免不了要写 SQL。这其实不是坏事多写 SQL 会强迫你理解数据库的本质而不是依赖图形界面点来点去。综合来看PostgreSQL 适合那些希望“一次性把数据层做扎实”的团队。短期的学习成本换来的长期稳定性和灵活性性价比是相当高的。3. 安装部署全记录Windows、Linux 与 Docker Compose 三种方式3.1 Windows 环境下安装 PostgreSQL 的完整步骤我经常看到有人问“PostgreSQL 在 Windows 上能用吗”这里明确回答当然能用而且 Windows 版本完全足够用于开发、测试以及中小规模的生产部署。PostgreSQL 官方提供了原生的 Windows 安装包不是兼容层模拟是真正的原生服务。下载安装的路径很简单打开 PostgreSQL 官网选择 Downloads再选 Windows官方推荐的是 EDB Installer。这个安装包会一并把 pgAdmin、Stack Builder 都装上比较省心。整个过程其实就是向导式操作但我有几个细节要提醒。安装时选安装目录默认是 C 盘的 Program Files如果你不想把数据库装在系统盘可以改成 D 盘但记得目录不要带中文和空格。接下来会让你设置超级管理员的密码这个密码要设置成一个你能记住但不容易被猜到的强密码。注意 PostgreSQL 默认超级用户叫 postgres这个用户是不可删除的。端口默认是 5432如果没有特殊需求就不要改因为很多工具和连接串默认都是这个端口改了之后每次连接都要额外指定容易给自己添麻烦。安装完成之后在开始菜单里找到 pgAdmin 4首次打开会让你设置一个主密码这是 pgAdmin 保存数据库连接信息的本地密码和 PostgreSQL 的密码不是一回事千万别弄混。安装完建议立刻验证一下服务状态。按 WinR输入 services.msc在服务列表里找到 postgresql-x64-版本号这个服务确认状态是“正在运行”。如果没运行右键手动启动然后把启动类型改成“自动”。这样 Windows 开机之后数据库就会自动拉起不用每次手动操作。还有一个非常实用的小技巧把 PostgreSQL 的 bin 目录加到系统环境变量 PATH 里。比如装在 D:\PostgreSQL\16\bin那么把这个路径加进去之后你就可以在 CMD 或 PowerShell 里直接敲 psql 命令非常方便。3.2 Linux 安装 PostgreSQL以及国内服务器的坑Linux 上安装 PostgreSQL 的推荐方式是使用官方仓库而不是直接用系统自带的源。很多发行版自带的 PostgreSQL 版本偏旧比如 CentOS 7 自带的还是 9.2老到很多新特性都不支持而且官方早已停止维护。这里以 CentOS/RHEL 系列为例先安装官方的 RPM 仓库sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm装完仓库之后禁用系统自带的模块然后安装指定版本的 PostgreSQL这里以 15 为例sudo yum -y update sudo yum -y install postgresql15-server postgresql15-contrib初始化数据库并启动服务sudo /usr/pgsql-15/bin/postgresql-15-setup initdb sudo systemctl start postgresql-15 sudo systemctl enable postgresql-15初始化这一步非常关键。很多人安装完直接启动结果发现服务起不来日志里提示 data directory 不存在或者没有初始化。原因就在于 postgresql-setup initdb 这个步骤会创建数据库的目录结构、生成初始配置文件、创建系统数据库相当于“建房子打地基”。地基不打房子自然立不起来。Ubuntu 系如 Debian、Ubuntu 20.04的安装方式略有不同这里简单说一下。Ubuntu 官方源里的 PostgreSQL 版本通常比较新比如 Ubuntu 22.04 自带的就是 14可以直接 apt 安装sudo apt update sudo apt install postgresql postgresql-contrib安装完成后Ubuntu 会自动初始化数据库并启动服务。检查状态用sudo systemctl status postgresql国内服务器有一个值得注意的点PostgreSQL 官方仓库的源在国外国内服务器下载速度可能比较慢。解决办法是手动画部把官方镜像源替换成国内镜像比如阿里云、清华大学的 PostgreSQL 镜像源。具体方法就是编辑 RPM 仓库配置文件或者 apt 的 sources.list把仓库地址改成对应镜像的地址。这样安装速度会有质的提升。再说一个 Linux 安装的常见坑防火墙。如果你的服务器有公网 IP 且需要远程访问 PostgreSQL光在 PostgreSQL 的配置里改 listen_addresses 是不够的还得检查系统的防火墙规则。CentOS 上用 firewalldUbuntu 上用 ufw。如果防火墙没放行 5432 端口外部连接会一直超时但本机连数据库又是正常的。这种问题排查起来很迷惑建议在起初配置时就把防火墙规则一并设置好。3.3 Docker Compose 部署 PostgreSQL开发环境最优解对于开发环境我目前最推荐的方式是用 Docker Compose 跑 PostgreSQL。这和我平时几个人协作开发的场景特别契合每个人拉一下仓库、docker-compose up -d数据库环境就起来了不用各自在自己电脑上折腾安装包。一个完整的 Docker Compose 配置大致长这样version: 3.8 services: postgres: image: postgres:15-alpine container_name: my-postgres restart: always environment: POSTGRES_USER: myuser POSTGRES_PASSWORD: mypassword POSTGRES_DB: mydb ports: - 5432:5432 volumes: - pgdata:/var/lib/postgresql/data - ./init-scripts:/docker-entrypoint-initdb.d:ro healthcheck: test: [CMD-SHELL, pg_isready -U myuser -d mydb] interval: 10s timeout: 5s retries: 5 volumes: pgdata:有几个细节值得展开说说。第一个是镜像版本的选择。官方 postgres 镜像的 alpine 版本体积更小适合开发环境。生产环境我建议用标准版本或者带具体次版本的镜像比如 postgres:15.4尽量避免用 floating tag比如 latest因为不可控的版本变动很容易导致环境不可复现。第二个是初始化的技巧。容器首次启动时如果数据目录是空的它会执行 /docker-entrypoint-initdb.d/ 目录下的所有 .sql 和 .sh 文件。这个机制对初始化表结构、插入种子数据、创建额外用户都非常有用。但要注意只有首次初始化时才会执行也就是说如果数据卷已经存在且里面有数据了这个目录不会被重新执行。第三个是数据持久化。volume 挂载到 /var/lib/postgresql/data 是 PostgreSQL 官方的数据目录。不要图方便把它挂到宿主机的一个普通目录因为权限问题很容易导致 PostgreSQL 无法启动。用 Docker Volume 能够避免权限不一致的问题也更干净。启动命令很简单docker-compose up -d查看日志docker-compose logs -f postgres用 Docker Compose 还有一个好处是不需要手动去设置 Linux 的 systemd 服务、开机启动之类的事情。restart: always 已经帮你处理了容器崩溃后的自动重启非常省心。我现在自己的项目中只要是个 PostgreSQL 开发环境基本都是用这套配置起底效率很高。4. 日常使用核心要点从连接数据库到备份恢复4.1 用 psql 连接和管理数据库的基础操作psql 是 PostgreSQL 自带的命令行客户端功能非常强大。很多初学者习惯打开 pgAdmin 或者 DBeaver 用图形界面操作但我觉得 psql 是必须掌握的尤其是在排查问题或者写脚本自动化运维的时候命令行工具的效率远高于鼠标点击。连接本地数据库的命令psql -h localhost -p 5432 -U postgres -d mydb-h 指定主机-p 指定端口-U 指定用户名-d 指定数据库名。如果不指定 -d默认连接和用户名同名的数据库。如果你直接用 postgres 用户连接默认会尝试连接到 postgres 数据库。连接成功之后你会看到类似这样的提示符postgres#这个提示符表示你已经进入了 SQL 命令模式。常用内部命令以反斜杠开头比如\l 列出所有数据库。\d 查看当前数据库中所有表、视图、序列。\d table_name 查看指定表的结构包括字段、类型、约束、索引。\du 列出所有用户和角色。\df 列出所有函数。\dt 只列出表。\dn 列出所有 schema。\q 退出 psql。\c dbname 切换数据库。这些命令在初学阶段一定要熟练掌握。尤其是 \d table_name它展示的信息非常完整包括列的默认值、是否允许为空、约束、关联的外键一眼就能看清表的设计。我在排查表结构问题时第一件事就是拿 \d 去看。在 psql 里执行 SQL 语句每条语句同样以分号结尾。如果忘了写分号回车后会看到一个“续行提示符”看起来像是在等你继续输入很多人第一次遇到以为卡住了。其实不用慌打个分号回车就执行了。这个特性也允许你把一条长 SQL 拆成多行来写很符合人类阅读习惯。4.2 用户、角色与权限体系别上来就只用 postgresPostgreSQL 的权限体系在开源数据库里算是比较严密的核心概念是角色Role。角色既可以当作一个用户来用也可以当作一组权限的集合。你可以把权限授给角色然后把角色授给用户实现权限分组管理。创建一个专用账号避免业务代码直接使用 postgres 超级用户CREATE USER myapp WITH PASSWORD strong_password;创建数据库并指定属主CREATE DATABASE mydb OWNER myapp;然后把所有权限授予这个用户GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;注意这个 GRANT 只是数据库级别的权限。如果你想让 myapp 用户能对表做增删改查还要在对应的 schema 上授权GRANT ALL ON SCHEMA public TO myapp; GRANT ALL ON ALL TABLES IN SCHEMA public TO myapp; GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO myapp;这个点很容易被忽略。很多人在创建了用户、授予了数据库权限后还是连不上或者查不了表原因就是 schema 级权限没给。角色还有继承关系。比如创建一个只读角色然后把多个用户都挂到这个角色下CREATE ROLE readonly_role; GRANT CONNECT ON DATABASE mydb TO readonly_role; GRANT USAGE ON SCHEMA public TO readonly_role; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role; GRANT readonly_role TO zhangsan; GRANT readonly_role TO lisi;这个做法的好处是以后要调整权限只需要改角色的权限所有继承该角色的用户自动生效。对于需要频繁变更权限的团队用角色来管理比逐个用户授权要高效得多。4.3 备份与恢复pg_dump、pg_restore 和逻辑复制备份的重要性不需要多强调。PostgreSQL 提供了一组官方备份工具最常用的是 pg_dump 和 pg_restore。pg_dump 用于逻辑备份它把数据库的内容导出成 SQL 文件或者自定义格式文件。最简单的方式pg_dump -h localhost -U myuser -d mydb backup.sql这种是纯 SQL 文本格式可以用 psql 直接恢复psql -h localhost -U myuser -d mydb backup.sql如果要恢复到新建的数据库记得先 create database 再执行导入。如果数据库比较大文本格式的文件恢复速度会让人崩溃。所以 pg_dump 支持自定义格式-Fc这种格式支持并行恢复效率高很多pg_dump -h localhost -U myuser -d mydb -Fc -f mydb.dump恢复时用 pg_restorepg_restore -h localhost -U myuser -d mydb --jobs4 mydb.dump--jobs 参数指定并行度可以显著提升恢复速度。我的实践经验是在普通服务器上4 到 8 个并行任务对性能提升比较明显再多可能就会受磁盘 I/O 限制收益反而下降。如果你只需要备份单张表pg_dump 也可以只导一张表pg_dump -h localhost -U myuser -d mydb -t public.users -Fc -f users.dump恢复单表也很灵活pg_restore -h localhost -U myuser -d mydb --tablepublic.users --jobs4 users.dump这是我在做部分数据修复时非常常用的操作。有一点要提醒逻辑备份属于“某个时间点的快照”如果你的业务要求高可用和实时容灾就需要考虑流复制或逻辑复制方案而不是靠定时 pg_dump。后面讲到版本升级和迁移时还会提到。4.4 如何用解释计划分析慢查询EXPLAIN ANALYZE 的正确姿势PostgreSQL 的性能调优是一个非常大的话题但我会从最有实操价值的一点切入学会读执行计划。任何 SQL 性能问题最终都要落到执行计划上。在 SQL 前面加 EXPLAIN ANALYZE就能看到真实执行的信息EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id 1;这里我建议加上 BUFFERS 选项它会把缓存命中信息也显示出来帮助你判断是否因为缺索引导致大量磁盘读取。执行计划里最常见的几个概念Seq Scan顺序扫描和 Index Scan索引扫描。顺序扫描就是一张表从头扫到尾小表问题不大大表如果频繁顺序扫描加索引几乎是必然选择。但要注意如果查询返回的行数占整张表的比例很高比如超过 20% 到 30%优化器可能会主动放弃索引改成顺序扫描因为这时候顺序扫描的 I/O 成本更低。这是正常行为不一定是索引没用。Cost 是 SQL 执行的成本估算值可以理解为“代价单位”。在看执行计划时重点关注代价最高的节点。比如在 Hash Join 中如果 Hash 很慢通常是驱动表太大或者内存不足导致 PostgreSQL 用了临时文件需要检查 work_mem 配置。work_mem 这个参数是每个排序、哈希操作可用的内存默认只有 4MB。如果你的排序操作涉及的数据量超过了 work_memPostgreSQL 会把中间结果写到磁盘临时文件里慢得让人抓狂。调大的方法SET work_mem 64MB;这个设置只对当前会话生效适合在调试时临时调整用来验证确实是 work_mem 太小的问题。生产环境调优应该修改配置文件或使用 ALTER SYSTEM 语句并考虑所有会话的并发数量。4.5 窗口函数与 CTE让复杂查询变得简洁高效PostgreSQL 对 SQL 标准的支持程度很高窗口函数和 CTE 是数据分析场景的利器。窗口函数可以在不改变行数的情况下对每一行附加上一个窗口范围内的聚合结果。比如“查询每个用户最近的订单并按金额排名”这种典型需求用窗口函数一行就能写出来SELECT user_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders;然后在外层查询过滤 rn 1就是每个用户金额最大的那一单。如果用子查询或者临时表来写代码会啰嗦很多而且效率不一定好。CTECommon Table Expression就是 WITH 子句可以把复杂的嵌套查询拆成一段一段的打代码像拼积木一样清晰。比如WITH monthly_orders AS ( SELECT DATE_TRUNC(month, created_at) AS month, COUNT(*) AS order_count FROM orders WHERE created_at 2024-01-01 GROUP BY DATE_TRUNC(month, created_at) ) SELECT * FROM monthly_orders ORDER BY month;CTE 还有个递归模式对处理树形结构非常方便。比如查某个部门下的所有子部门、查 BOM 层级结构、评论的楼中楼关系用递归 CTE 就可以很方便地实现。WITH RECURSIVE sub_departments AS ( SELECT id, name, parent_id FROM departments WHERE id 1 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN sub_departments sd ON d.parent_id sd.id ) SELECT * FROM sub_departments;这类查询放在应用层用递归代码写性能差而且逻辑繁琐数据库里一条 SQL 就搞定了这也是我非常推荐用 PostgreSQL 的原因之一。5. 版本差异深度解析12、15、19 这些版本号到底怎么选5.1 从 12 到 15值得关注的变化PostgreSQL 官方每年发一个大版本版本号的规则是“主版本.次版本”。不过大家日常说的“12”“15”“16”通常指的是主版本号比如 PostgreSQL 15 指的是 15.x 系列。次版本号比如 15.3、15.4主要是安全修复和 bug 修复小版本升级不会带来功能上的大变化。PostgreSQL 12 是一个比较经典的版本它引入了不少重要改进比如 CTE 可以被优化器内联、SQL/JSON 路径查询、B 树索引的某些优化。很多老系统还跑在 12 上但 PostgreSQL 12 已经进入维护末期官方建议尽快升级到更新的版本。PostgreSQL 13 的最大亮点是增量排序和并行 vacuum对大数据量操作有明显优化。PostgreSQL 14 在连接管理、并行查询、B 树索引更新的方面做了大量性能改进尤其是大内存环境下 B 树索引的更新性能提升非常显著。PostgreSQL 15 是我目前最推荐的“生产稳定版”。它引入了 MERGE 语句类似 SQL 标准里的 UPSERT、更完善的权限体系如 public schema 默认只允许属主访问、以及逻辑复制的一些改进。在真实项目的测试中15 版的查询优化器对各种复杂查询的处理都更聪明执行计划的走向更合理。5.2 PostgreSQL 16 和 17/19 带来了什么新东西PostgreSQL 16 在 2023 年发布它的并行查询能力进一步提升特别是在全表扫描和聚合操作的场景性能提升比较明显。同时它对逻辑复制做了重要增强可以在订阅端进行并行复制非常适合用于数据仓库等需要大数据量同步的场景。PostgreSQL 17 在 2024 年发布重点改进包括 VACUUM 性能增强、wal 日志处理优化、COPY 命令性能提升等。而 19 这个版本准确说应该是 PostgreSQL 19按 PostgreSQL 官方计划18 之后的下一个大版本是 19目前还处于开发阶段不建议在生产环境中使用。对于版本选择我的建议是新项目直接用 15 或 16如果团队需要最新特性可以上 16求稳就用 15。存量项目不要盲目追求最新版本先看业务中的核心功能在目标版本上是否有行为变化。官方支持的策略是每个大版本发布后维护 5 年左右不要等大版本完全停止维护了才考虑升级那时候可能已经积累了大量兼容性问题。5.3 PostgreSQL 版本升级的两个安全路径版本升级有两种常见方式。第一种是 pg_dump/pg_restore 的逻辑升级。原理就是把旧版本的数据导出再导入到新版本的空白数据库里。这种方式兼容性最好无论跨多少个大版本都能用因为它是通过 SQL 语句搬数据的。缺点是数据量大的时候耗时较长而且在升级过程中业务基本需要停机。pg_dump -h old_host -U postgres -d mydb -Fc -f mydb.dump pg_restore -h new_host -U postgres -d mydb --jobs8 mydb.dump第二种是 pg_upgrade 的原生升级。这种方式直接操作数据文件的内部格式速度非常快。官方支持从一个大版本直接升级到下一个大版本比如从 14 升到 15。它的原理是物理上转换数据文件因此不能用它跨多个大版本直接升级如果需要跨多个版本需要先逐步升级到中间版本或者选择逻辑升级。/usr/pgsql-15/bin/pg_upgrade \ --old-datadir/var/lib/pgsql/14/data \ --new-datadir/var/lib/pgsql/15/data \ --old-bindir/usr/pgsql-14/bin \ --new-bindir/usr/pgsql-15/bin \ --old-config/var/lib/pgsql/14/data/postgresql.conf \ --new-config/var/lib/pgsql/15/data/postgresql.conf无论是哪种方式升级前一定要先做一次完整的备份。我一个习惯是升级前备份、升级后马上验证备份文件保留至少一个月。不要问为什么等你在没有备份的情况下升级失败过一次就什么都明白了。6. 数据库结构同步与迁移migra 工具的使用经验6.1 为什么你需要一个结构对比工具实际开发中结构同步是绕不开的痛点。开发环境改了表结构测试环境要跟着改生产环境也要通过审核后变更。如果全靠人工写 ALTER TABLE不仅累而且很容易漏改或者写错。我之前一直用手动对比的方式先把两张表的建表语句导出来然后肉眼对比。表少的时候还能将就表一多尤其是字段顺序、默认值、注释略有不同的时候排查起来简直是折磨。后来我了解到 migra 这个工具它完全是为“比较两个 PostgreSQL 数据库结构差异并生成迁移 SQL”而设计的。migra 是一个用 Python 写的开源工具官方地址在 GitHub 上。它做的事非常纯粹连上两个数据库计算两者的结构差异然后输出一套让旧库变成新库的 SQL 脚本。这个思路和 Rails 的 migration、Django 的 makemigrations 有点类似但它是独立于框架的任何 PostgreSQL 项目都能用。6.2 安装与使用 migra 的正确姿势在 Windows、Linux、macOS 上安装 migra 都建议先装一个 Python 虚拟环境避免污染全局 Python 环境。安装方式其实非常简单用 pip 直接装就可以pip install migra也可以使用 pipxPython 生态中专门用来安装命令行工具的方式pipx install migra连接两个数据库时需要提供两个连接串。连接串格式是标准的 PostgreSQL URI 格式postgresql://user:passwordhost:port/database对比开发库和测试库的差异migra postgresql://dev_user:dev_passlocalhost:5432/devdb postgresql://test_user:test_passlocalhost:5432/testdb默认情况下migra 会把从第一个库变成第二个库需要的 SQL 全部输出到标准输出。这里要注意输出的是“让第一个库匹配第二个库”的脚本也就是说源和目标别搞反了。如果你希望把差异直接应用到一个库上可以加 --unsafe 参数migra --unsafe postgresql://dev_user:dev_passlocalhost:5432/devdb postgresql://test_user:test_passlocalhost:5432/testdb这会直接执行让第一个库变成第二个库的 SQL。但因为生产环境变更必须走审批流程我通常不建议直接执行更推荐的做法是用一个脚本文件把 SQL 保存下来人工 review 之后再执行。migra postgresql://... postgresql://... migration.sql然后检查 migration.sql 内容确认无误后在其他环境执行psql -h target_host -U user -d targetdb -f migration.sql6.3 迁移脚本遇到权限、序列与注释时的注意点migra 能生成的 SQL 覆盖非常全面包括表结构、字段属性、约束、索引、视图、函数、触发器等。但在实际使用中有几个点容易出问题我把遇到过的情况列出来。权限方面如果对比的两个数据库使用了不同的用户连接可能因为权限不足而无法读取某些对象的元数据导致差异不完整。解决方法是尽量使用超级用户或者具备读取系统目录权限的账号来跑 migra。序列Sequence和默认值相关的问题。如果一张表的某个字段类型是 serial 或者 identity迁移时如果两边的主键序列值不同步migra 生成的 SQL 可能会包含重置序列的语句。这些语句看似简单但在生产环境执行时如果被跳过后续插入数据就很容易出现主键冲突。注释。migra 支持比较字段注释和表注释。如果两个库的注释写的比较随意每次对比都会产生一堆“注释差异”脚本这会干扰你识别真实的结构变更。建议在跑对比之前先确认是不是真的要同步注释如果是测试数据库可能注释本来就没人维护忽略掉更省心。6.4 用 migra 做 CI 检查的思路migra 还有一个很实用的用法就是接进 CI 里做数据库结构漂移检测。我以前在团队里这样搞过写一个 GitHub Actions 或 GitLab CI 的 Job每次代码合并之后自动对比测试库和一个基准库的结构如果有差异就输出 warning 并附带迁移 SQL。这样等于给数据库结构上了一道自动化的“体检”。示例大概长这样- name: Check schema drift run: | pip install migra migra postgresql://base_user:passlocalhost:5432/base_db \ postgresql://test_user:passlocalhost:5432/test_db migration.sql if [ -s migration.sql ]; then echo Schema drift detected: cat migration.sql exit 1 fi这样可以保证任何合并到主干的代码都能正确反映到测试库的结构上大大减少“环境不一致”的扯皮问题。7. 常见问题速查表与我的排查技巧7.1 连接失败类问题PostgreSQL 使用中最常见的故障就是连不上。我把这类问题整理成一张速查表方便大家直接对照排查。现象可能原因排查思路本机连不上报 Connection refused服务没启动检查服务状态Windows 看 services.mscLinux 执行 systemctl status postgresql远程连接超时防火墙未放行 5432 端口检查防火墙规则放行 TCP 5432远程连接报 no pg_hba.conf entrypg_hba.conf 未允许远程地址编辑 pg_hba.conf增加 host all all 0.0.0.0/0 md5然后 reload身份认证失败密码错误或认证方式不对检查密码确认 pg_hba.conf 中认证方法如 scram-sha-256 或 md5psql 报 database 不存在连接串指定的数据库不存在先连接 postgres 库再 \l 查看已有数据库这里重点说一下 pg_hba.conf 这个文件。它的全称是 PostgreSQL Host-Based Authentication控制哪些 IP 可以通过哪种方式认证。很多远程连接失败的问题其实都是这个文件默认配置只允许本地连接导致的。默认配置长这样# TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 scram-sha-256如果要允许内网网段访问追加一行host all all 192.168.1.0/24 scram-sha-256修改完成之后不需要重启数据库reload 即可pg_ctl reload -D /var/lib/pgsql/data或者登录到 psql 里执行SELECT pg_reload_conf();7.2 数据写入与性能问题“写入很慢”是我被问得最多的问题之一。碰到这种情况先别急着调 work_mem先看两个细节。第一个是检查是否开启了强制 fsync以及 wal 相关参数是否合理。PostgreSQL 为了保证事务可靠性每次提交都需要把 WAL 日志刷到磁盘这个 fsync 代价在高并发写入的时候非常明显。生产环境不要关闭 fsync这会导致系统故障时数据损坏。但可以考虑用更快的磁盘NVMe SSD或者调整 commit_delay、synchronous_commit 等参数来平衡性能和安全。第二个是检查表有没有膨胀。PostgreSQL 的 MVCC 机制决定了一行被更新后会留下旧版本这些旧版本需要 VACUUM 清理。如果 VACUUM 跟不上更新速度表会越来越大扫描会越来越慢。定期执行VACUUM (ANALYZE, VERBOSE) your_table;这个命令会清理无效数据并更新统计信息。日常可以配置 autovacuum 自动运行大多数情况下默认配置够用但如果你的表更新非常频繁可以考虑调高 autovacuum_vacuum_scale_factor 的相关参数。7.3 死锁与锁等待问题死锁在多人同时操作数据库的时候很常见。PostgreSQL 提供了一些视图可以实时查看锁等待状态SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state idle;如果发现大量查询处于锁等待状态可以通过 pg_locks 视图看具体是什么锁在阻塞SELECT a.pid, a.query, b.mode, b.granted FROM pg_stat_activity a JOIN pg_locks b ON a.pid b.pid;定位到问题 PID 之后如果不是关键事务可以直接终止这个会话SELECT pg_terminate_backend(pid);这个操作相当于把这个会话强制杀掉。在测试环境可以随便用生产环境要非常谨慎最好先和相关团队沟通清楚。7.4 忘记密码怎么办这个场景很多人经历过很慌但其实处理起来不算难。思路是先以本机信任的方式进入数据库然后修改密码。第一步修改 pg_hba.conf把本机连接方式临时改成 trusthost all all 127.0.0.1/32 trust然后 reload 配置pg_ctl reload -D /var/lib/pgsql/data第二步用 psql 直接连进去不需要密码psql -h localhost -U postgres第三步修改密码ALTER USER postgres WITH PASSWORD new_password;第四步把 pg_hba.conf 改回原来的认证方式比如 scram-sha-256再次 reload。注意这个操作只适用于你有服务器操作系统权限的情况。如果是在云数据库上忘了密码一般云平台有控制台重置密码的入口直接走云平台操作即可。7.5 如何彻底卸载 PostgreSQLWindows 上卸载不干净是很常见的困扰。如果只是卸载软件注册表服务、数据目录、环境变量可能还会留着。我的建议是先通过 Windows 的“卸载程序”卸载然后手动删除服务。以管理员身份打开 CMDsc delete postgresql-x64-16然后删除数据目录这个目录默认在 C:\Program Files\PostgreSQL\16\data如果当初改过安装路径就在对应位置。还可以清理注册表WinR 输入 regedit搜索 PostgreSQL 相关的注册表项主要是 HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL 这个目录确认没有其他程序引用之后可以删除。Linux 上卸载相对简单sudo yum remove postgresql15-server或者 Ubuntusudo apt purge postgresql postgresql-*但数据目录默认在 /var/lib/pgsql 或 /etc/postgresql卸载后通常不会自动删除需要手动清理。8. 写在最后的几条心得这份笔记与其说是教程不如说是我这些年“折腾” PostgreSQL 的一个沉淀。从最开始在 Windows 上装好然后兴奋地建表到后来在 Linux 服务器上配置流复制、在大数据量场景下优化查询、用 migra 把结构变更做成自动化检查每一步都踩过不少坑也因此积累了这些经验。根据我的实际使用体会PostgreSQL 最迷人的地方在于它的“严谨”。它不会放过你在 SQL 里写下的任何一处模糊表达也会在你试图破坏数据完整性时坚决说不。这种严谨一开始可能让人适应不了但当你真正依赖它来承载核心业务数据时就会觉得非常有安全感。如果你刚接触 PostgreSQL我的建议是先别急着去研究各种高级特性先把安装、用户权限、备份恢复这几件基础事情做扎实。这三块就像是房子的地基地基稳了后面无论是做版本升级、结构迁移还是性能调优都有底气。如果你已经用了不短的时间回头看看自己的 pg_hba.conf 和 autovacuum 配置也许会有新的发现。最后再分享一个小技巧在 psql 里执行 \timingpsql 会在每条 SQL 执行完显示耗时。这个开关我几乎一直开着时间久了你对各种操作的成本会形成非常直观的体感无论是调优还是排查问题都会快人一步。 PostgreSQL 实战笔记：安装部署、版本升级与迁移调优全解析
1. 为什么这份 PostgreSQL 笔记值得你从头读完先说明一下这篇内容不是什么官方文档翻译也不是照着教程敲命令的流水账。我做后端开发和数据库运维有些年头了PostgreSQL 从 9.x 一路用到现在的 15、16期间踩过不少坑、走过不少弯路也积累了一些“文档里不会明说但实际特别有用”的经验。这次借着整理笔记的机会把和 PostgreSQL 相关的安装、日常使用、版本选型、结构对比、常见问题全部梳理一遍希望能帮到正在入门或者已经用了一段时间但总觉得差点意思的同学。很多人在 MySQL 和 PostgreSQL 之间纠结。我刚工作时用的也是 MySQL后来因为项目需要接触了 PostgreSQL才意识到这套数据库远比想象中强大。它不仅仅是“开源的关系型数据库”这么简单在 JSON 处理、全文检索、地理信息、复杂查询、数据完整性约束这些方面PostgreSQL 都有非常扎实的设计。如果你还在犹豫要不要用或者已经在用了但想更深入地掌握它这份笔记应该能给你一个比较完整的视角。我要讲的不是那种“复制粘贴就能跑”的速成教程而是把每个关键操作背后的逻辑说清楚让你知道为什么这么配置、为什么用这个工具、为什么踩了坑之后要这样排查。这样才能做到真正理解 PostgreSQL而不是只会背命令。接下来我会按照一条从零开始的实际使用路径来展开从安装部署讲起到日常操作、版本对比、结构同步最后梳理高频问题的排查思路。2. PostgreSQL 与 MySQL、SQLite 的核心差异以及选型思路2.1 三者的定位不同决定了你该怎么选很多初学者会把 PostgreSQL、MySQL、SQLite 放在一起比较然后问“哪个更好”。这个问题的前提就有问题因为这三者的定位本身就不太一样。SQLite 是嵌入式关系型数据库它以库文件的形式存在不需要独立的服务进程适合移动端、桌面端、嵌入式设备或者原型验证阶段。优点就是零配置、轻量、部署简单缺点是并发写入能力弱不适合多用户高并发的在线业务也没有完整的用户权限体系。MySQL 是经典的关系型数据库生态成熟、使用人数多尤其在国内互联网行业有着非常深厚的基础。它在读多写少、水平扩展方面有成熟的方案运维资料也极其丰富。如果是传统的 Web 业务MySQL 仍然是可靠的选择。PostgreSQL 在定位上更接近“功能完整的企业级数据库”。它在数据类型、约束机制、事务能力、扩展性方面做得非常扎实对 SQL 标准的遵循程度也更高。比如复杂查询的优化器、递归查询、窗口函数、CTE这些在 PostgreSQL 里用起来非常顺手而且性能相当稳定。对于数据完整性要求高、查询逻辑复杂、需要使用 JSON 或地理位置这类特殊数据类型的场景PostgreSQL 的优势会非常明显。如果你做的是中小规模业务团队对数据库没有强烈的历史依赖而且需要应对越来越复杂的查询需求那我个人认为 PostgreSQL 是更值得投入的方向。它不会让你在业务复杂度上去之后面临“功能不够用”的窘境。2.2 PostgreSQL 相比 MySQL 的几个关键优势从使用体验上来说我认为最有感知差异的点集中在以下几个方面。第一是约束与数据完整性。PostgreSQL 的表可以定义非常细致的检查约束、外键约束、唯一约束同时对通过约束的数据校验执行得一丝不苟。MySQL 在某些存储引擎如 MyISAM下根本不支持外键InnoDB 支持外键但默认不强制使用。简单说PostgreSQL 是默认帮你守住数据底线的而 MySQL 更多时候靠开发者自觉。第二是 JSON 支持。PostgreSQL 的 JSONB 类型是二进制存储的可以直接在 JSON 字段上建索引、做条件查询这对很多需要存储半结构化数据的场景非常友好。MySQL 的 JSON 类型虽然也能用但灵活性和性能表现都还有差距。第三是索引类型丰富。除了常规的 B-Tree 索引PostgreSQL 还支持 GIN、GiST、BRIN、SP-GiST 等索引类型。比如 GIN 索引适合全文检索和数组类型BRIN 索引适合超大表上的时间序列数据。写 SQL 时可以针对数据特征选择更合适的索引这是 MySQL 很难做到的。第四是可扩展性。PostgreSQL 允许你定义自定义数据类型、自定义函数支持 PL/pgSQL、Python、C 等、自定义聚合函数甚至可以把自己写的索引方法挂进去。对于业务有特殊需求的开发者来说这种扩展能力非常珍贵。2.3 不要忽略 PostgreSQL 的“学习曲线”当然PostgreSQL 也不是没有门槛。它比 MySQL 更严格这个“严格”对新手来说就是学习成本。举个例子你在 PostgreSQL 里写一个带 GROUP BY 的查询如果 SELECT 的字段没有被聚合它会直接报错而 MySQL 在默认设置下可能就直接给你返回一行不明不白的数据。刚开始用的时候可能会觉得它在找麻烦但用久了你会明白这是它在帮你防呆。另外 PostgreSQL 的生态工具没有 MySQL 那么多花花绿绿的第三方管理面板虽然 pgAdmin 和 DBeaver 都能用但很多操作还是免不了要写 SQL。这其实不是坏事多写 SQL 会强迫你理解数据库的本质而不是依赖图形界面点来点去。综合来看PostgreSQL 适合那些希望“一次性把数据层做扎实”的团队。短期的学习成本换来的长期稳定性和灵活性性价比是相当高的。3. 安装部署全记录Windows、Linux 与 Docker Compose 三种方式3.1 Windows 环境下安装 PostgreSQL 的完整步骤我经常看到有人问“PostgreSQL 在 Windows 上能用吗”这里明确回答当然能用而且 Windows 版本完全足够用于开发、测试以及中小规模的生产部署。PostgreSQL 官方提供了原生的 Windows 安装包不是兼容层模拟是真正的原生服务。下载安装的路径很简单打开 PostgreSQL 官网选择 Downloads再选 Windows官方推荐的是 EDB Installer。这个安装包会一并把 pgAdmin、Stack Builder 都装上比较省心。整个过程其实就是向导式操作但我有几个细节要提醒。安装时选安装目录默认是 C 盘的 Program Files如果你不想把数据库装在系统盘可以改成 D 盘但记得目录不要带中文和空格。接下来会让你设置超级管理员的密码这个密码要设置成一个你能记住但不容易被猜到的强密码。注意 PostgreSQL 默认超级用户叫 postgres这个用户是不可删除的。端口默认是 5432如果没有特殊需求就不要改因为很多工具和连接串默认都是这个端口改了之后每次连接都要额外指定容易给自己添麻烦。安装完成之后在开始菜单里找到 pgAdmin 4首次打开会让你设置一个主密码这是 pgAdmin 保存数据库连接信息的本地密码和 PostgreSQL 的密码不是一回事千万别弄混。安装完建议立刻验证一下服务状态。按 WinR输入 services.msc在服务列表里找到 postgresql-x64-版本号这个服务确认状态是“正在运行”。如果没运行右键手动启动然后把启动类型改成“自动”。这样 Windows 开机之后数据库就会自动拉起不用每次手动操作。还有一个非常实用的小技巧把 PostgreSQL 的 bin 目录加到系统环境变量 PATH 里。比如装在 D:\PostgreSQL\16\bin那么把这个路径加进去之后你就可以在 CMD 或 PowerShell 里直接敲 psql 命令非常方便。3.2 Linux 安装 PostgreSQL以及国内服务器的坑Linux 上安装 PostgreSQL 的推荐方式是使用官方仓库而不是直接用系统自带的源。很多发行版自带的 PostgreSQL 版本偏旧比如 CentOS 7 自带的还是 9.2老到很多新特性都不支持而且官方早已停止维护。这里以 CentOS/RHEL 系列为例先安装官方的 RPM 仓库sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm装完仓库之后禁用系统自带的模块然后安装指定版本的 PostgreSQL这里以 15 为例sudo yum -y update sudo yum -y install postgresql15-server postgresql15-contrib初始化数据库并启动服务sudo /usr/pgsql-15/bin/postgresql-15-setup initdb sudo systemctl start postgresql-15 sudo systemctl enable postgresql-15初始化这一步非常关键。很多人安装完直接启动结果发现服务起不来日志里提示 data directory 不存在或者没有初始化。原因就在于 postgresql-setup initdb 这个步骤会创建数据库的目录结构、生成初始配置文件、创建系统数据库相当于“建房子打地基”。地基不打房子自然立不起来。Ubuntu 系如 Debian、Ubuntu 20.04的安装方式略有不同这里简单说一下。Ubuntu 官方源里的 PostgreSQL 版本通常比较新比如 Ubuntu 22.04 自带的就是 14可以直接 apt 安装sudo apt update sudo apt install postgresql postgresql-contrib安装完成后Ubuntu 会自动初始化数据库并启动服务。检查状态用sudo systemctl status postgresql国内服务器有一个值得注意的点PostgreSQL 官方仓库的源在国外国内服务器下载速度可能比较慢。解决办法是手动画部把官方镜像源替换成国内镜像比如阿里云、清华大学的 PostgreSQL 镜像源。具体方法就是编辑 RPM 仓库配置文件或者 apt 的 sources.list把仓库地址改成对应镜像的地址。这样安装速度会有质的提升。再说一个 Linux 安装的常见坑防火墙。如果你的服务器有公网 IP 且需要远程访问 PostgreSQL光在 PostgreSQL 的配置里改 listen_addresses 是不够的还得检查系统的防火墙规则。CentOS 上用 firewalldUbuntu 上用 ufw。如果防火墙没放行 5432 端口外部连接会一直超时但本机连数据库又是正常的。这种问题排查起来很迷惑建议在起初配置时就把防火墙规则一并设置好。3.3 Docker Compose 部署 PostgreSQL开发环境最优解对于开发环境我目前最推荐的方式是用 Docker Compose 跑 PostgreSQL。这和我平时几个人协作开发的场景特别契合每个人拉一下仓库、docker-compose up -d数据库环境就起来了不用各自在自己电脑上折腾安装包。一个完整的 Docker Compose 配置大致长这样version: 3.8 services: postgres: image: postgres:15-alpine container_name: my-postgres restart: always environment: POSTGRES_USER: myuser POSTGRES_PASSWORD: mypassword POSTGRES_DB: mydb ports: - 5432:5432 volumes: - pgdata:/var/lib/postgresql/data - ./init-scripts:/docker-entrypoint-initdb.d:ro healthcheck: test: [CMD-SHELL, pg_isready -U myuser -d mydb] interval: 10s timeout: 5s retries: 5 volumes: pgdata:有几个细节值得展开说说。第一个是镜像版本的选择。官方 postgres 镜像的 alpine 版本体积更小适合开发环境。生产环境我建议用标准版本或者带具体次版本的镜像比如 postgres:15.4尽量避免用 floating tag比如 latest因为不可控的版本变动很容易导致环境不可复现。第二个是初始化的技巧。容器首次启动时如果数据目录是空的它会执行 /docker-entrypoint-initdb.d/ 目录下的所有 .sql 和 .sh 文件。这个机制对初始化表结构、插入种子数据、创建额外用户都非常有用。但要注意只有首次初始化时才会执行也就是说如果数据卷已经存在且里面有数据了这个目录不会被重新执行。第三个是数据持久化。volume 挂载到 /var/lib/postgresql/data 是 PostgreSQL 官方的数据目录。不要图方便把它挂到宿主机的一个普通目录因为权限问题很容易导致 PostgreSQL 无法启动。用 Docker Volume 能够避免权限不一致的问题也更干净。启动命令很简单docker-compose up -d查看日志docker-compose logs -f postgres用 Docker Compose 还有一个好处是不需要手动去设置 Linux 的 systemd 服务、开机启动之类的事情。restart: always 已经帮你处理了容器崩溃后的自动重启非常省心。我现在自己的项目中只要是个 PostgreSQL 开发环境基本都是用这套配置起底效率很高。4. 日常使用核心要点从连接数据库到备份恢复4.1 用 psql 连接和管理数据库的基础操作psql 是 PostgreSQL 自带的命令行客户端功能非常强大。很多初学者习惯打开 pgAdmin 或者 DBeaver 用图形界面操作但我觉得 psql 是必须掌握的尤其是在排查问题或者写脚本自动化运维的时候命令行工具的效率远高于鼠标点击。连接本地数据库的命令psql -h localhost -p 5432 -U postgres -d mydb-h 指定主机-p 指定端口-U 指定用户名-d 指定数据库名。如果不指定 -d默认连接和用户名同名的数据库。如果你直接用 postgres 用户连接默认会尝试连接到 postgres 数据库。连接成功之后你会看到类似这样的提示符postgres#这个提示符表示你已经进入了 SQL 命令模式。常用内部命令以反斜杠开头比如\l 列出所有数据库。\d 查看当前数据库中所有表、视图、序列。\d table_name 查看指定表的结构包括字段、类型、约束、索引。\du 列出所有用户和角色。\df 列出所有函数。\dt 只列出表。\dn 列出所有 schema。\q 退出 psql。\c dbname 切换数据库。这些命令在初学阶段一定要熟练掌握。尤其是 \d table_name它展示的信息非常完整包括列的默认值、是否允许为空、约束、关联的外键一眼就能看清表的设计。我在排查表结构问题时第一件事就是拿 \d 去看。在 psql 里执行 SQL 语句每条语句同样以分号结尾。如果忘了写分号回车后会看到一个“续行提示符”看起来像是在等你继续输入很多人第一次遇到以为卡住了。其实不用慌打个分号回车就执行了。这个特性也允许你把一条长 SQL 拆成多行来写很符合人类阅读习惯。4.2 用户、角色与权限体系别上来就只用 postgresPostgreSQL 的权限体系在开源数据库里算是比较严密的核心概念是角色Role。角色既可以当作一个用户来用也可以当作一组权限的集合。你可以把权限授给角色然后把角色授给用户实现权限分组管理。创建一个专用账号避免业务代码直接使用 postgres 超级用户CREATE USER myapp WITH PASSWORD strong_password;创建数据库并指定属主CREATE DATABASE mydb OWNER myapp;然后把所有权限授予这个用户GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;注意这个 GRANT 只是数据库级别的权限。如果你想让 myapp 用户能对表做增删改查还要在对应的 schema 上授权GRANT ALL ON SCHEMA public TO myapp; GRANT ALL ON ALL TABLES IN SCHEMA public TO myapp; GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO myapp;这个点很容易被忽略。很多人在创建了用户、授予了数据库权限后还是连不上或者查不了表原因就是 schema 级权限没给。角色还有继承关系。比如创建一个只读角色然后把多个用户都挂到这个角色下CREATE ROLE readonly_role; GRANT CONNECT ON DATABASE mydb TO readonly_role; GRANT USAGE ON SCHEMA public TO readonly_role; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role; GRANT readonly_role TO zhangsan; GRANT readonly_role TO lisi;这个做法的好处是以后要调整权限只需要改角色的权限所有继承该角色的用户自动生效。对于需要频繁变更权限的团队用角色来管理比逐个用户授权要高效得多。4.3 备份与恢复pg_dump、pg_restore 和逻辑复制备份的重要性不需要多强调。PostgreSQL 提供了一组官方备份工具最常用的是 pg_dump 和 pg_restore。pg_dump 用于逻辑备份它把数据库的内容导出成 SQL 文件或者自定义格式文件。最简单的方式pg_dump -h localhost -U myuser -d mydb backup.sql这种是纯 SQL 文本格式可以用 psql 直接恢复psql -h localhost -U myuser -d mydb backup.sql如果要恢复到新建的数据库记得先 create database 再执行导入。如果数据库比较大文本格式的文件恢复速度会让人崩溃。所以 pg_dump 支持自定义格式-Fc这种格式支持并行恢复效率高很多pg_dump -h localhost -U myuser -d mydb -Fc -f mydb.dump恢复时用 pg_restorepg_restore -h localhost -U myuser -d mydb --jobs4 mydb.dump--jobs 参数指定并行度可以显著提升恢复速度。我的实践经验是在普通服务器上4 到 8 个并行任务对性能提升比较明显再多可能就会受磁盘 I/O 限制收益反而下降。如果你只需要备份单张表pg_dump 也可以只导一张表pg_dump -h localhost -U myuser -d mydb -t public.users -Fc -f users.dump恢复单表也很灵活pg_restore -h localhost -U myuser -d mydb --tablepublic.users --jobs4 users.dump这是我在做部分数据修复时非常常用的操作。有一点要提醒逻辑备份属于“某个时间点的快照”如果你的业务要求高可用和实时容灾就需要考虑流复制或逻辑复制方案而不是靠定时 pg_dump。后面讲到版本升级和迁移时还会提到。4.4 如何用解释计划分析慢查询EXPLAIN ANALYZE 的正确姿势PostgreSQL 的性能调优是一个非常大的话题但我会从最有实操价值的一点切入学会读执行计划。任何 SQL 性能问题最终都要落到执行计划上。在 SQL 前面加 EXPLAIN ANALYZE就能看到真实执行的信息EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id 1;这里我建议加上 BUFFERS 选项它会把缓存命中信息也显示出来帮助你判断是否因为缺索引导致大量磁盘读取。执行计划里最常见的几个概念Seq Scan顺序扫描和 Index Scan索引扫描。顺序扫描就是一张表从头扫到尾小表问题不大大表如果频繁顺序扫描加索引几乎是必然选择。但要注意如果查询返回的行数占整张表的比例很高比如超过 20% 到 30%优化器可能会主动放弃索引改成顺序扫描因为这时候顺序扫描的 I/O 成本更低。这是正常行为不一定是索引没用。Cost 是 SQL 执行的成本估算值可以理解为“代价单位”。在看执行计划时重点关注代价最高的节点。比如在 Hash Join 中如果 Hash 很慢通常是驱动表太大或者内存不足导致 PostgreSQL 用了临时文件需要检查 work_mem 配置。work_mem 这个参数是每个排序、哈希操作可用的内存默认只有 4MB。如果你的排序操作涉及的数据量超过了 work_memPostgreSQL 会把中间结果写到磁盘临时文件里慢得让人抓狂。调大的方法SET work_mem 64MB;这个设置只对当前会话生效适合在调试时临时调整用来验证确实是 work_mem 太小的问题。生产环境调优应该修改配置文件或使用 ALTER SYSTEM 语句并考虑所有会话的并发数量。4.5 窗口函数与 CTE让复杂查询变得简洁高效PostgreSQL 对 SQL 标准的支持程度很高窗口函数和 CTE 是数据分析场景的利器。窗口函数可以在不改变行数的情况下对每一行附加上一个窗口范围内的聚合结果。比如“查询每个用户最近的订单并按金额排名”这种典型需求用窗口函数一行就能写出来SELECT user_id, order_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders;然后在外层查询过滤 rn 1就是每个用户金额最大的那一单。如果用子查询或者临时表来写代码会啰嗦很多而且效率不一定好。CTECommon Table Expression就是 WITH 子句可以把复杂的嵌套查询拆成一段一段的打代码像拼积木一样清晰。比如WITH monthly_orders AS ( SELECT DATE_TRUNC(month, created_at) AS month, COUNT(*) AS order_count FROM orders WHERE created_at 2024-01-01 GROUP BY DATE_TRUNC(month, created_at) ) SELECT * FROM monthly_orders ORDER BY month;CTE 还有个递归模式对处理树形结构非常方便。比如查某个部门下的所有子部门、查 BOM 层级结构、评论的楼中楼关系用递归 CTE 就可以很方便地实现。WITH RECURSIVE sub_departments AS ( SELECT id, name, parent_id FROM departments WHERE id 1 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d INNER JOIN sub_departments sd ON d.parent_id sd.id ) SELECT * FROM sub_departments;这类查询放在应用层用递归代码写性能差而且逻辑繁琐数据库里一条 SQL 就搞定了这也是我非常推荐用 PostgreSQL 的原因之一。5. 版本差异深度解析12、15、19 这些版本号到底怎么选5.1 从 12 到 15值得关注的变化PostgreSQL 官方每年发一个大版本版本号的规则是“主版本.次版本”。不过大家日常说的“12”“15”“16”通常指的是主版本号比如 PostgreSQL 15 指的是 15.x 系列。次版本号比如 15.3、15.4主要是安全修复和 bug 修复小版本升级不会带来功能上的大变化。PostgreSQL 12 是一个比较经典的版本它引入了不少重要改进比如 CTE 可以被优化器内联、SQL/JSON 路径查询、B 树索引的某些优化。很多老系统还跑在 12 上但 PostgreSQL 12 已经进入维护末期官方建议尽快升级到更新的版本。PostgreSQL 13 的最大亮点是增量排序和并行 vacuum对大数据量操作有明显优化。PostgreSQL 14 在连接管理、并行查询、B 树索引更新的方面做了大量性能改进尤其是大内存环境下 B 树索引的更新性能提升非常显著。PostgreSQL 15 是我目前最推荐的“生产稳定版”。它引入了 MERGE 语句类似 SQL 标准里的 UPSERT、更完善的权限体系如 public schema 默认只允许属主访问、以及逻辑复制的一些改进。在真实项目的测试中15 版的查询优化器对各种复杂查询的处理都更聪明执行计划的走向更合理。5.2 PostgreSQL 16 和 17/19 带来了什么新东西PostgreSQL 16 在 2023 年发布它的并行查询能力进一步提升特别是在全表扫描和聚合操作的场景性能提升比较明显。同时它对逻辑复制做了重要增强可以在订阅端进行并行复制非常适合用于数据仓库等需要大数据量同步的场景。PostgreSQL 17 在 2024 年发布重点改进包括 VACUUM 性能增强、wal 日志处理优化、COPY 命令性能提升等。而 19 这个版本准确说应该是 PostgreSQL 19按 PostgreSQL 官方计划18 之后的下一个大版本是 19目前还处于开发阶段不建议在生产环境中使用。对于版本选择我的建议是新项目直接用 15 或 16如果团队需要最新特性可以上 16求稳就用 15。存量项目不要盲目追求最新版本先看业务中的核心功能在目标版本上是否有行为变化。官方支持的策略是每个大版本发布后维护 5 年左右不要等大版本完全停止维护了才考虑升级那时候可能已经积累了大量兼容性问题。5.3 PostgreSQL 版本升级的两个安全路径版本升级有两种常见方式。第一种是 pg_dump/pg_restore 的逻辑升级。原理就是把旧版本的数据导出再导入到新版本的空白数据库里。这种方式兼容性最好无论跨多少个大版本都能用因为它是通过 SQL 语句搬数据的。缺点是数据量大的时候耗时较长而且在升级过程中业务基本需要停机。pg_dump -h old_host -U postgres -d mydb -Fc -f mydb.dump pg_restore -h new_host -U postgres -d mydb --jobs8 mydb.dump第二种是 pg_upgrade 的原生升级。这种方式直接操作数据文件的内部格式速度非常快。官方支持从一个大版本直接升级到下一个大版本比如从 14 升到 15。它的原理是物理上转换数据文件因此不能用它跨多个大版本直接升级如果需要跨多个版本需要先逐步升级到中间版本或者选择逻辑升级。/usr/pgsql-15/bin/pg_upgrade \ --old-datadir/var/lib/pgsql/14/data \ --new-datadir/var/lib/pgsql/15/data \ --old-bindir/usr/pgsql-14/bin \ --new-bindir/usr/pgsql-15/bin \ --old-config/var/lib/pgsql/14/data/postgresql.conf \ --new-config/var/lib/pgsql/15/data/postgresql.conf无论是哪种方式升级前一定要先做一次完整的备份。我一个习惯是升级前备份、升级后马上验证备份文件保留至少一个月。不要问为什么等你在没有备份的情况下升级失败过一次就什么都明白了。6. 数据库结构同步与迁移migra 工具的使用经验6.1 为什么你需要一个结构对比工具实际开发中结构同步是绕不开的痛点。开发环境改了表结构测试环境要跟着改生产环境也要通过审核后变更。如果全靠人工写 ALTER TABLE不仅累而且很容易漏改或者写错。我之前一直用手动对比的方式先把两张表的建表语句导出来然后肉眼对比。表少的时候还能将就表一多尤其是字段顺序、默认值、注释略有不同的时候排查起来简直是折磨。后来我了解到 migra 这个工具它完全是为“比较两个 PostgreSQL 数据库结构差异并生成迁移 SQL”而设计的。migra 是一个用 Python 写的开源工具官方地址在 GitHub 上。它做的事非常纯粹连上两个数据库计算两者的结构差异然后输出一套让旧库变成新库的 SQL 脚本。这个思路和 Rails 的 migration、Django 的 makemigrations 有点类似但它是独立于框架的任何 PostgreSQL 项目都能用。6.2 安装与使用 migra 的正确姿势在 Windows、Linux、macOS 上安装 migra 都建议先装一个 Python 虚拟环境避免污染全局 Python 环境。安装方式其实非常简单用 pip 直接装就可以pip install migra也可以使用 pipxPython 生态中专门用来安装命令行工具的方式pipx install migra连接两个数据库时需要提供两个连接串。连接串格式是标准的 PostgreSQL URI 格式postgresql://user:passwordhost:port/database对比开发库和测试库的差异migra postgresql://dev_user:dev_passlocalhost:5432/devdb postgresql://test_user:test_passlocalhost:5432/testdb默认情况下migra 会把从第一个库变成第二个库需要的 SQL 全部输出到标准输出。这里要注意输出的是“让第一个库匹配第二个库”的脚本也就是说源和目标别搞反了。如果你希望把差异直接应用到一个库上可以加 --unsafe 参数migra --unsafe postgresql://dev_user:dev_passlocalhost:5432/devdb postgresql://test_user:test_passlocalhost:5432/testdb这会直接执行让第一个库变成第二个库的 SQL。但因为生产环境变更必须走审批流程我通常不建议直接执行更推荐的做法是用一个脚本文件把 SQL 保存下来人工 review 之后再执行。migra postgresql://... postgresql://... migration.sql然后检查 migration.sql 内容确认无误后在其他环境执行psql -h target_host -U user -d targetdb -f migration.sql6.3 迁移脚本遇到权限、序列与注释时的注意点migra 能生成的 SQL 覆盖非常全面包括表结构、字段属性、约束、索引、视图、函数、触发器等。但在实际使用中有几个点容易出问题我把遇到过的情况列出来。权限方面如果对比的两个数据库使用了不同的用户连接可能因为权限不足而无法读取某些对象的元数据导致差异不完整。解决方法是尽量使用超级用户或者具备读取系统目录权限的账号来跑 migra。序列Sequence和默认值相关的问题。如果一张表的某个字段类型是 serial 或者 identity迁移时如果两边的主键序列值不同步migra 生成的 SQL 可能会包含重置序列的语句。这些语句看似简单但在生产环境执行时如果被跳过后续插入数据就很容易出现主键冲突。注释。migra 支持比较字段注释和表注释。如果两个库的注释写的比较随意每次对比都会产生一堆“注释差异”脚本这会干扰你识别真实的结构变更。建议在跑对比之前先确认是不是真的要同步注释如果是测试数据库可能注释本来就没人维护忽略掉更省心。6.4 用 migra 做 CI 检查的思路migra 还有一个很实用的用法就是接进 CI 里做数据库结构漂移检测。我以前在团队里这样搞过写一个 GitHub Actions 或 GitLab CI 的 Job每次代码合并之后自动对比测试库和一个基准库的结构如果有差异就输出 warning 并附带迁移 SQL。这样等于给数据库结构上了一道自动化的“体检”。示例大概长这样- name: Check schema drift run: | pip install migra migra postgresql://base_user:passlocalhost:5432/base_db \ postgresql://test_user:passlocalhost:5432/test_db migration.sql if [ -s migration.sql ]; then echo Schema drift detected: cat migration.sql exit 1 fi这样可以保证任何合并到主干的代码都能正确反映到测试库的结构上大大减少“环境不一致”的扯皮问题。7. 常见问题速查表与我的排查技巧7.1 连接失败类问题PostgreSQL 使用中最常见的故障就是连不上。我把这类问题整理成一张速查表方便大家直接对照排查。现象可能原因排查思路本机连不上报 Connection refused服务没启动检查服务状态Windows 看 services.mscLinux 执行 systemctl status postgresql远程连接超时防火墙未放行 5432 端口检查防火墙规则放行 TCP 5432远程连接报 no pg_hba.conf entrypg_hba.conf 未允许远程地址编辑 pg_hba.conf增加 host all all 0.0.0.0/0 md5然后 reload身份认证失败密码错误或认证方式不对检查密码确认 pg_hba.conf 中认证方法如 scram-sha-256 或 md5psql 报 database 不存在连接串指定的数据库不存在先连接 postgres 库再 \l 查看已有数据库这里重点说一下 pg_hba.conf 这个文件。它的全称是 PostgreSQL Host-Based Authentication控制哪些 IP 可以通过哪种方式认证。很多远程连接失败的问题其实都是这个文件默认配置只允许本地连接导致的。默认配置长这样# TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 scram-sha-256如果要允许内网网段访问追加一行host all all 192.168.1.0/24 scram-sha-256修改完成之后不需要重启数据库reload 即可pg_ctl reload -D /var/lib/pgsql/data或者登录到 psql 里执行SELECT pg_reload_conf();7.2 数据写入与性能问题“写入很慢”是我被问得最多的问题之一。碰到这种情况先别急着调 work_mem先看两个细节。第一个是检查是否开启了强制 fsync以及 wal 相关参数是否合理。PostgreSQL 为了保证事务可靠性每次提交都需要把 WAL 日志刷到磁盘这个 fsync 代价在高并发写入的时候非常明显。生产环境不要关闭 fsync这会导致系统故障时数据损坏。但可以考虑用更快的磁盘NVMe SSD或者调整 commit_delay、synchronous_commit 等参数来平衡性能和安全。第二个是检查表有没有膨胀。PostgreSQL 的 MVCC 机制决定了一行被更新后会留下旧版本这些旧版本需要 VACUUM 清理。如果 VACUUM 跟不上更新速度表会越来越大扫描会越来越慢。定期执行VACUUM (ANALYZE, VERBOSE) your_table;这个命令会清理无效数据并更新统计信息。日常可以配置 autovacuum 自动运行大多数情况下默认配置够用但如果你的表更新非常频繁可以考虑调高 autovacuum_vacuum_scale_factor 的相关参数。7.3 死锁与锁等待问题死锁在多人同时操作数据库的时候很常见。PostgreSQL 提供了一些视图可以实时查看锁等待状态SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state idle;如果发现大量查询处于锁等待状态可以通过 pg_locks 视图看具体是什么锁在阻塞SELECT a.pid, a.query, b.mode, b.granted FROM pg_stat_activity a JOIN pg_locks b ON a.pid b.pid;定位到问题 PID 之后如果不是关键事务可以直接终止这个会话SELECT pg_terminate_backend(pid);这个操作相当于把这个会话强制杀掉。在测试环境可以随便用生产环境要非常谨慎最好先和相关团队沟通清楚。7.4 忘记密码怎么办这个场景很多人经历过很慌但其实处理起来不算难。思路是先以本机信任的方式进入数据库然后修改密码。第一步修改 pg_hba.conf把本机连接方式临时改成 trusthost all all 127.0.0.1/32 trust然后 reload 配置pg_ctl reload -D /var/lib/pgsql/data第二步用 psql 直接连进去不需要密码psql -h localhost -U postgres第三步修改密码ALTER USER postgres WITH PASSWORD new_password;第四步把 pg_hba.conf 改回原来的认证方式比如 scram-sha-256再次 reload。注意这个操作只适用于你有服务器操作系统权限的情况。如果是在云数据库上忘了密码一般云平台有控制台重置密码的入口直接走云平台操作即可。7.5 如何彻底卸载 PostgreSQLWindows 上卸载不干净是很常见的困扰。如果只是卸载软件注册表服务、数据目录、环境变量可能还会留着。我的建议是先通过 Windows 的“卸载程序”卸载然后手动删除服务。以管理员身份打开 CMDsc delete postgresql-x64-16然后删除数据目录这个目录默认在 C:\Program Files\PostgreSQL\16\data如果当初改过安装路径就在对应位置。还可以清理注册表WinR 输入 regedit搜索 PostgreSQL 相关的注册表项主要是 HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL 这个目录确认没有其他程序引用之后可以删除。Linux 上卸载相对简单sudo yum remove postgresql15-server或者 Ubuntusudo apt purge postgresql postgresql-*但数据目录默认在 /var/lib/pgsql 或 /etc/postgresql卸载后通常不会自动删除需要手动清理。8. 写在最后的几条心得这份笔记与其说是教程不如说是我这些年“折腾” PostgreSQL 的一个沉淀。从最开始在 Windows 上装好然后兴奋地建表到后来在 Linux 服务器上配置流复制、在大数据量场景下优化查询、用 migra 把结构变更做成自动化检查每一步都踩过不少坑也因此积累了这些经验。根据我的实际使用体会PostgreSQL 最迷人的地方在于它的“严谨”。它不会放过你在 SQL 里写下的任何一处模糊表达也会在你试图破坏数据完整性时坚决说不。这种严谨一开始可能让人适应不了但当你真正依赖它来承载核心业务数据时就会觉得非常有安全感。如果你刚接触 PostgreSQL我的建议是先别急着去研究各种高级特性先把安装、用户权限、备份恢复这几件基础事情做扎实。这三块就像是房子的地基地基稳了后面无论是做版本升级、结构迁移还是性能调优都有底气。如果你已经用了不短的时间回头看看自己的 pg_hba.conf 和 autovacuum 配置也许会有新的发现。最后再分享一个小技巧在 psql 里执行 \timingpsql 会在每条 SQL 执行完显示耗时。这个开关我几乎一直开着时间久了你对各种操作的成本会形成非常直观的体感无论是调优还是排查问题都会快人一步。