企业官网建设流程全解析

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 的密码不是一回事，千万别弄混。

安装完建议立刻验证一下服务状态。按 Win+R，输入 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 上用 firewalld，Ubuntu 上用 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 用户、角色与权限体系：别上来就只用 postgres

PostgreSQL 的权限体系在开源数据库里算是比较严密的，核心概念是角色（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_restore：

pg_restore -h localhost -U myuser -d mydb --jobs=4 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 --table=public.users --jobs=4 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_mem，PostgreSQL 会把中间结果写到磁盘临时文件里，慢得让人抓狂。调大的方法：

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，就是每个用户金额最大的那一单。如果用子查询或者临时表来写，代码会啰嗦很多，而且效率不一定好。

CTE（Common 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 --jobs=8 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

也可以使用 pipx，Python 生态中专门用来安装命令行工具的方式：

pipx install migra

连接两个数据库时，需要提供两个连接串。连接串格式是标准的 PostgreSQL URI 格式：

postgresql://user:password@host:port/database

对比开发库和测试库的差异：

migra postgresql://dev_user:dev_pass@localhost:5432/devdb postgresql://test_user:test_pass@localhost:5432/testdb

默认情况下，migra 会把从第一个库变成第二个库需要的 SQL 全部输出到标准输出。这里要注意，输出的是“让第一个库匹配第二个库”的脚本，也就是说源和目标别搞反了。

如果你希望把差异直接应用到一个库上，可以加 --unsafe 参数：

migra --unsafe postgresql://dev_user:dev_pass@localhost:5432/devdb postgresql://test_user:test_pass@localhost:5432/testdb

这会直接执行让第一个库变成第二个库的 SQL。但因为生产环境变更必须走审批流程，我通常不建议直接执行，更推荐的做法是用一个脚本文件把 SQL 保存下来，人工 review 之后再执行。

migra postgresql://... postgresql://... > migration.sql

然后检查 migration.sql 内容，确认无误后在其他环境执行：

psql -h target_host -U user -d targetdb -f migration.sql

6.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:pass@localhost:5432/base_db \ postgresql://test_user:pass@localhost:5432/test_db > migration.sql if [ -s migration.sql ]; then echo "Schema drift detected:" cat migration.sql exit 1 fi

这样可以保证任何合并到主干的代码都能正确反映到测试库的结构上，大大减少“环境不一致”的扯皮问题。

7. 常见问题速查表与我的排查技巧

7.1 连接失败类问题

PostgreSQL 使用中最常见的故障就是连不上。我把这类问题整理成一张速查表，方便大家直接对照排查。

这里重点说一下 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，把本机连接方式临时改成 trust：

host 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 如何彻底卸载 PostgreSQL

Windows 上卸载不干净是很常见的困扰。如果只是卸载软件，注册表服务、数据目录、环境变量可能还会留着。我的建议是：先通过 Windows 的“卸载程序”卸载，然后手动删除服务。

以管理员身份打开 CMD：

sc delete postgresql-x64-16

然后删除数据目录，这个目录默认在 C:\Program Files\PostgreSQL\16\data，如果当初改过安装路径就在对应位置。

还可以清理注册表：Win+R 输入 regedit，搜索 PostgreSQL 相关的注册表项，主要是 HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL 这个目录，确认没有其他程序引用之后可以删除。

Linux 上卸载相对简单：

sudo yum remove postgresql15-server

或者 Ubuntu：

sudo apt purge postgresql postgresql-*

但数据目录默认在 /var/lib/pgsql 或 /etc/postgresql，卸载后通常不会自动删除，需要手动清理。

8. 写在最后的几条心得

这份笔记与其说是教程，不如说是我这些年“折腾” PostgreSQL 的一个沉淀。从最开始在 Windows 上装好然后兴奋地建表，到后来在 Linux 服务器上配置流复制、在大数据量场景下优化查询、用 migra 把结构变更做成自动化检查，每一步都踩过不少坑，也因此积累了这些经验。

根据我的实际使用体会，PostgreSQL 最迷人的地方在于它的“严谨”。它不会放过你在 SQL 里写下的任何一处模糊表达，也会在你试图破坏数据完整性时坚决说不。这种严谨一开始可能让人适应不了，但当你真正依赖它来承载核心业务数据时，就会觉得非常有安全感。

如果你刚接触 PostgreSQL，我的建议是先别急着去研究各种高级特性，先把安装、用户权限、备份恢复这几件基础事情做扎实。这三块就像是房子的地基，地基稳了，后面无论是做版本升级、结构迁移还是性能调优，都有底气。如果你已经用了不短的时间，回头看看自己的 pg_hba.conf 和 autovacuum 配置，也许会有新的发现。

最后再分享一个小技巧：在 psql 里执行 \timing，psql 会在每条 SQL 执行完显示耗时。这个开关我几乎一直开着，时间久了你对各种操作的成本会形成非常直观的体感，无论是调优还是排查问题，都会快人一步。