1. 为什么我在 Ubuntu 上的默认数据库选型变成了 PostgreSQL说起 Ubuntu 和 PostgreSQL 这对组合很多人的第一反应是“安装很简单一条 apt 命令就搞定”。但实际上真正踩过坑的人都知道安装只是整个链条里最轻松的一环后面还有认证机制、监听地址、远程访问、权限模型、备份策略、参数调优等一连串问题等着你。这篇实录不是我临时拼凑的教程而是我前后折腾过多次环境之后把完整过程重新梳理出来的结果适合正在 Ubuntu 服务器上部署业务库的后端开发、刚接手数据库运维的初级 DBA以及准备把项目从别的数据库迁到 PostgreSQL 上的团队参考。我的业务背景并不复杂一个以订单和用户数据为核心的应用需要保证事务一致性同时要应付不断增长的 JSON 半结构化数据还要做全文检索。在三年前的选型阶段我和大多数人一样默认选择 MySQL。真正让我做出改变的不是网上铺天盖地的对比文章而是一次实打实的线上事故商品表在做一批批量更新时长时间锁表导致用户端查询接口大面积超时。事后排查发现问题出在我们对表结构的设计不够严谨但 MySQL 在高并发读写混合场景下的锁竞争也确实放大了问题。于是我开始认真考虑 PostgreSQL并用了两个月把核心库迁了过去。1.1 业务场景需要 PostgreSQL 的什么能力先说说我在实际业务里最依赖 PostgreSQL 的几项能力这也是你评估选型时可以对照的清单事务与并发控制PostgreSQL 的 MVCC 机制非常成熟读不阻塞写、写不阻塞读。在同样的硬件条件下读写混合场景比我在 MySQL 上的体验稳定很多。特别是大量短事务并发时PostgreSQL 的锁冲突率明显更低。高级数据类型业务里有不少配置类数据结构经常变化强行建表很痛苦。PostgreSQL 的 JSONB 可以让我在保持查询能力的前提下灵活存储嵌套结构还能用 GIN 索引加速 JSON 路径查询。扩展生态我们后来引入了地理空间查询和向量检索PostgreSQL 的 PostGIS 和 pgvector 扩展都提供了官方维护的版本不需要额外搭一套服务。数据完整性PostgreSQL 对约束、外键、检查约束的执行非常严格这对财务相关的数据来说是很重要的底线。当然MySQL 也有很多优势比如生态庞大、运维资料多、云数据库支持完善。我这里不是在说 PostgreSQL 是万能银弹而是想说明在 Ubuntu 这类 Linux 服务器上自己安装维护 PostgreSQL是成本很低、收益很稳定的一件事。1.2 和 MySQL 对比我实测下来的感受我用一张表格整理一下自己在迁移过程中实际感受到的差异方便还没入手的读者做判断对比维度PostgreSQLMySQL我的实测感受并发写入多版本并发控制成熟读写互相干扰小高并发写时锁竞争较明显同样 16 核 64G 的机器PostgreSQL 表现更平滑JSON 支持JSONB 类型原生支持索引有 JSON 类型但功能和索引能力弱一些JSONB 的路径查询和 GIN 索引是真省事扩展机制CREATE EXTENSION 统一管理主要靠插件和存储引擎安装 PostGIS、pgvector 时非常方便全文检索内置全文检索支持中文分词扩展需要额外配置或依赖外部工具内置能力已经能覆盖大部分场景配置复杂度参数多默认值偏保守相对简单PostgreSQL 需要花时间理解参数但可调空间大社区运维资料丰富但中文资料质量参差非常丰富中文资料多关键文档建议直接看官方手册这不是说 MySQL 不好而是对我这种需要数据完整性、扩展能力、复杂查询并存的场景PostgreSQL 在 Ubuntu 上的表现更贴合需求。如果你只是做简单的 CRUD 应用MySQL 依然是很务实的选择。2. 安装前先花五分钟做环境预检版本、源、依赖很多安装失败案例问题都不出在 PostgreSQL 本身而是出在 Ubuntu 环境没有检查清楚。我见过有人拿着一份几个月前的教程在已经升级系统版本的机器上照抄命令结果仓库地址不匹配apt 报错一堆 404。所以在执行安装之前我建议你先完成三个检查。2.1 看清 Ubuntu 版本再动手PostgreSQL 官方仓库是根据 Ubuntu 版本号来提供软件包的版本不匹配就装不上。先执行下面的命令cat /etc/os-release重点看VERSION_CODENAME这一行它会告诉你当前系统的代号。不同 Ubuntu 版本对应的 PostgreSQL 默认版本也完全不同我整理了一张表Ubuntu 版本代号官方源里常见的 PostgreSQL 版本20.04focalPostgreSQL 12/13/14/15/1622.04jammyPostgreSQL 14/15/1624.04noblePostgreSQL 16/17如果你用的是 WSL 里的 Ubuntu或者虚拟机里的系统也同样适用。这里特别提醒一点很多文章只给命令不提版本匹配导致你从网上抄来的软件源地址里的代号和本机不一致apt 更新时会直接报错。所以第一步别省。另外建议顺便把系统包更新一下免得软件源索引太旧安装时遇到依赖解析问题sudo apt update sudo apt upgrade -y2.2 默认 apt 源和 PGDG 官方仓库的区别Ubuntu 默认源里其实有 PostgreSQL直接sudo apt install postgresql就能装。但这里有个容易踩的坑默认源里的版本是随 Ubuntu 发行版绑定的。比如在 Ubuntu 22.04 上默认源大概率给你装的是 PostgreSQL 14而这个版本在官方仓库里可能已经不再提供新版补丁了。如果你的项目依赖某个特定版本默认源很难满足。我推荐使用 PostgreSQL 官方维护的 PGDG 仓库PostgreSQL Global Development Group。这个仓库的好处有三点提供 PostgreSQL 14/15/16/17 等多个主版本可以精确指定安装某个版本补丁更新及时安全修复会更快同步附带 postgis、pgvector、pgagent、pgbouncer 等常用扩展和工具一条命令就能装好。所以下面我的安装步骤都用 PGDG 官方源这也是我在生产环境里一直在用的方式。2.3 安装 PostgreSQL 16 的完整命令以 PostgreSQL 16 为例我建议按下面的步骤操作。第一件事是安装必要的工具然后导入官方仓库的签名密钥sudo apt install -y curl ca-certificates sudo install -d /usr/share/postgresql-common/pgdg sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc接着添加仓库源。注意我这里用了$(lsb_release -cs)自动取系统代号这样换一台机器执行也不会写死版本号sudo sh -c echo deb [signed-by/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main /etc/apt/sources.list.d/pgdg.list更新索引后安装sudo apt update sudo apt install -y postgresql-16 postgresql-client-16安装完成后Debian/Ubuntu 系会自动创建一个默认集群可以用下面的命令确认状态sudo pg_lsclusters sudo systemctl status postgresqlpg_lsclusters是 Ubuntu 系特有的管理工具它会列出集群版本、端口、数据目录、状态等信息。看到online状态就说明基础安装成功了。3. 配置项里那些“看起来默认就行”的坑认证、监听、重载装好之后很多人直接敲sudo -u postgres psql能进控制台就以为一切正常了。但等到你的程序用 TCP 连接数据库或者你试图从另一台机器连过来时问题就开始出现了。这里的核心在pg_hba.conf和postgresql.conf两个文件你得先把它们的逻辑盘清楚。3.1 两个配置文件到底各管什么事很多刚接触 PostgreSQL 的读者会把这两个文件搞混postgresql.conf负责数据库实例的运行时参数比如端口、内存、日志、并发连接数等pg_hba.conf负责客户端认证规则决定某个来源 IP 的某个用户能不能用某种认证方式访问某个数据库。在 Ubuntu 上如果你安装的是 PostgreSQL 16这两个文件位于/etc/postgresql/16/main/目录下。注意这个路径同时带了主版本号和集群名如果你之后又装了一个 15 版本它会出现在/etc/postgresql/15/main/里互不干扰。我先说最重要的结论凡是修改pg_hba.conf不需要重启整个数据库只需要重载配置但是修改postgresql.conf里的某些参数可能必须重启才能真正生效。3.2 peer、scram-sha-256 和 trust认证规则的匹配优先级刚安装完Ubuntu 上默认的pg_hba.conf大致长这样local all postgres peer local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256这四条规则的含义分别是第一条通过 Unix 套接字连接时只要当前 Linux 系统用户是postgres就可以以数据库用户postgres的身份登录不需要密码。这就是为什么你能直接执行sudo -u postgres psql进数据库。第二条通过 Unix 套接字连接时其他任何数据库用户也采用 peer 认证也就是说系统用户名必须与数据库用户名一致。第三条和第四条从本机 IPv4 或 IPv6 地址通过 TCP 连接时使用 SCRAM-SHA-256 密码认证。这里的坑在于很多人第一次写数据库连接程序时用psql -U myuser -d mydb没有加-h参数psql 默认走 Unix 套接字连接于是命中第二条 peer 规则。如果你的 Linux 系统用户名不是myuser就会看到Peer authentication failed for user myuser这样的错误。这不是密码错了而是认证方式不匹配。解决这个问题通常有两种思路一是连接时强制走 TCP指定-h 127.0.0.1或-h localhost二是在pg_hba.conf里调整规则。我个人的习惯是应用连接一律走 TCP 加密码认证Unix 套接字只保留给本机管理员维护使用。3.3 开启远程连接为什么必须同时改两个地方默认情况下PostgreSQL 的postgresql.conf里listen_addresses是localhost意思是只监听本机连接请求。如果你想从局域网或公网访问数据库必须把这个参数改成*或指定网卡地址然后在pg_hba.conf里添加对应的远程访问规则。两个地方缺一个都不行。我遇到过一种典型的排查场景改了listen_addresses *也重启了服务从另一台机器连接时却仍然超时。一看发现pg_hba.conf里压根没有远程网段的规则。所以记住这个判断逻辑客户端能连通数据库 IP 和端口说明listen_addresses和防火墙没问题如果提示 authentication failed说明pg_hba.conf有匹配规则但密码或用户不对如果提示 no pg_hba.conf entry说明pg_hba.conf没有匹配你来源地址的规则。如果你确实需要远程访问可以追加这样一条规则注意要把它放在文件末尾因为 PostgreSQL 是按顺序匹配的前面的规则没有匹配才会继续往后看host all all 0.0.0.0/0 scram-sha-256上面这条允许所有来源 IP 连接仅适合内网测试环境。生产环境建议精确到网段比如host all all 192.168.1.0/24 scram-sha-256同时在 Ubuntu 防火墙里放行 5432 端口sudo ufw allow from 192.168.1.0/24 to any port 5432 proto tcp3.4 reload 和 restart 到底什么时候用这是很多运维新手容易踩的坑。修改pg_hba.conf后用sudo systemctl reload postgresql就生效了不需要重启。修改postgresql.conf时大部分参数也支持重载生效比如shared_buffers、work_mem不用重启就能应用但有两个典型的例外listen_addresses修改后需要重启因为这是启动时绑定的监听地址shared_preload_libraries修改后必须重启比如你之后要启用pg_stat_statements扩展就要在这里配置预加载库。判断方法是修改完执行sudo systemctl reload postgresql后用下面的命令看一下实际值是否变更SHOW shared_buffers; SHOW listen_addresses;如果没变再考虑重启。能不停机就尽量别停机这是运维的基本修养。4. 从 postgres 超级用户切换到业务账号建库建权的完整操作安装后的默认超级用户是postgres但它用的是 peer 认证和 Linux 系统用户绑定不适合让应用直连。正确做法是创建一个专门给应用用的数据库账号并遵循最小权限原则不给它不需要的权限。4.1 别动不动就用 postgres 用户跑应用很多开发者在本地图省事直接在代码里用postgres用户连库这在生产环境是大忌。一旦这个账号泄露攻击者可以删除整个数据库、查看所有业务数据、修改数据库配置。我曾经在客户现场遇到过开发配置里硬编码了 postgres 超级用户密码的情况数据库直接在公网暴露想想都后怕。正确的姿势是给每个应用单独建一个角色密码设置成强密码只授予它关联数据库的必要权限。即便应用被拖库造成的破坏范围也是可控的。进入 psql 控制台最简单的方式是sudo -u postgres psql因为本机套接字连接走 peer 认证你的系统用户是 postgres所以不需要密码就能进入。4.2 创建应用专用账号和数据库的推荐步骤我通常会执行这样一组 SQLCREATE ROLE app_user WITH LOGIN PASSWORD Str0ng!Passw0rd; CREATE DATABASE appdb OWNER app_user ENCODING UTF8;这里有几个细节说明LOGIN允许角色登录不加这个角色只能作为权限组使用OWNER app_user让应用用户成为数据库属主之后它在这个库里拥有相对完整的建表、查询、修改权限但不会自动拥有其他数据库的管理权限ENCODING UTF8显式指定字符集避免连接时出现中文乱码问题。如果你希望更严格地控制不授予 OWNER而是只用普通权限可以这样CREATE ROLE app_user WITH LOGIN PASSWORD Str0ng!Passw0rd; CREATE DATABASE appdb; GRANT CONNECT ON DATABASE appdb TO app_user;不过这种模式在后面建表授权时会多一些操作如果你刚开始学我建议先用 OWNER 方案跑通流程后续再按需收紧。数据库建好后测试连接PGPASSWORDStr0ng!Passw0rd psql -h 127.0.0.1 -U app_user -d appdb能进入说明应用连接配置无误。注意这里为什么要加-h 127.0.0.1因为如果不加psql 默认走 Unix 套接字会触发本地 peer 认证大概率又会出现认证失败。4.3 一个我实际踩过的 owner 坑我曾在迁移数据时做过一个操作先用 postgres 超级用户建好数据库和表然后想当然地执行ALTER DATABASE appdb OWNER TO app_user以为这样业务用户就能操作了。结果应用连接后对已有的表仍然没有读写权限查了很长时间才发现数据库的属主变了但表、序列等对象的属主仍然是 postgres权限继承不会自动覆盖已有对象。正确做法是在迁移已有数据时最好连表和序列的属主一起调整ALTER TABLE public.users OWNER TO app_user; ALTER SEQUENCE public.users_id_seq OWNER TO app_user;如果对象非常多可以用REASSIGN OWNED批量处理REASSIGN OWNED BY postgres TO app_user;不过这个命令需要谨慎使用因为它会修改当前数据库里所有 postgres 拥有的对象属主。我后来总结经验新建项目时一定要用应用自己的账号去建表不要图省事用超级用户代劳。这样从源头就避免了权限错乱。5. 日常运维不能偷懒的三件事备份、日志、参数调优数据库装上只是开始真正考验人的是后续运维。这里分享三件我每次搭建 PostgreSQL 环境之后都会立刻做的事。5.1 用 pg_dump 做可以恢复的备份脚本PostgreSQL 的逻辑备份工具是pg_dump它可以在数据库运行状态下生成一致性备份非常方便。我通常在目标机器上放这样一个脚本#!/bin/bash BACKUP_DIR/var/backups/pg DB_NAMEappdb DB_USERapp_user DATE$(date %F_%H-%M) mkdir -p $BACKUP_DIR pg_dump -h 127.0.0.1 -U $DB_USER -Fc -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump $DB_NAME find $BACKUP_DIR -name *.dump -mtime 7 -delete需要说明的是-Fc表示自定义格式压缩率高恢复时用pg_restore可以灵活选择恢复哪些对象连接时指定-h 127.0.0.1走 TCP避免 peer 认证问题脚本里没有硬编码密码我会在 postgres 用户的~/.pgpass文件中写入密码格式是hostname:port:database:username:password并把文件权限设为 600。然后加一个 cron 任务每天早上 2 点执行crontab -e 0 2 * * * /usr/local/bin/pg_backup.sh /var/log/pg_backup.log 21光有备份还不够我至少一个月做一次恢复演练把 dump 文件恢复到一台临时数据库上确认数据完整可用。很多人辛辛苦苦写了备份脚本结果真出事时发现 dump 文件损坏或者备份内容不对这种情况比没有备份更麻烦。5.2 日志和慢查询怎么开PostgreSQL 的默认日志配置比较保守如果不主动调整你很难排查问题。我通常会在postgresql.conf里调这几个参数log_destination stderr logging_collector on log_directory log log_filename postgresql-%a.log log_truncate_on_rotation on log_rotation_age 1d log_rotation_size 100MB log_min_duration_statement 1000 log_line_prefix %m [%p] %q%u%d 这里log_min_duration_statement 1000的意思是执行时间超过 1000 毫秒的 SQL 会自动记录到日志里。这是定位慢查询最直接的方式。运行一段时间后你可以直接在数据目录下的log文件夹里看到按天轮转的日志文件也可以用 journalctl 查看系统日志里的数据库输出。打开日志后每条 SQL 的执行时间、用户、数据库名都有了配合pg_stat_statements扩展还能统计高频慢 SQL效果更好。启用pg_stat_statements需要两步第一步在postgresql.conf里添加shared_preload_libraries pg_stat_statements由于这是预加载库修改后必须重启数据库。重启后执行CREATE EXTENSION IF NOT EXISTS pg_stat_statements;然后查最耗时的前十名 SQLSELECT query, total_exec_time, calls FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;这个查询能帮你快速定位哪些 SQL 是性能瓶颈非常实用。5.3 值得先调的四个内存参数PostgreSQL 的默认参数偏向保守为的是在任何机器上都能跑起来但这不是最优配置。我调整参数时会参考机器实际内存大小这里给出一台 8GB 内存服务器的示例参数建议值理由shared_buffers2GB官方建议占物理内存 25% 左右这是共享缓冲池effective_cache_size6GB告诉优化器系统可用缓存总量通常设为物理内存的 70% 左右maintenance_work_mem256MB用于建索引、VACUUM 等维护操作提高速度work_mem16MB每个排序操作可用的内存不宜过高避免高并发时内存耗尽修改方法是在postgresql.conf里找到对应项改掉然后重载。其中shared_buffers是最核心的参数但也不是越大越好超过物理内存 1/3 后收益反而递减。work_mem要特别小心这是每个排序操作独立分配的如果并发 100 个连接都在排序每个 1GB内存瞬间就爆了。所以我从不敢把 work_mem 调得太高。调完参数后记得验证实际生效值SHOW shared_buffers; SHOW effective_cache_size;不要只看配置文件里的修改要看数据库实际加载的值。6. 安装和配置中最常见的失败现场端口、认证、Docker最后这一部分我把自己这些年遇到的高频问题整理一下按排查思路一步一步说清楚而不是直接给你一个标准答案。因为排查过程本身才是在 Ubuntu 上维护 PostgreSQL 最值钱的经验。6.1 服务启动失败先看端口再看集群状态有次我同时装了 PostgreSQL 15 和 16 两个版本结果默认端口都是 543216 起来后 15 的集群启动失败。如果你遇到pg_ctl: another server might be running先看看是哪个进程占用了端口sudo ss -tlnp | grep 5432然后查看集群状态sudo pg_lsclusters在 Ubuntu 上多版本并存是很常见的因为 apt 安装不同主版本时会创建各自的集群。如果两个集群抢同一个端口可以把其中一个改到别的端口或者直接停掉不用的集群sudo pg_ctlcluster 15 main stop还有一种看起来很像启动失败的情况systemctl status postgresql显示 active但pg_lsclusters显示某个 cluster 是 down。这是因为 Ubuntu 的 postgresql.service 是个总服务具体集群状态由pg_lsclusters管理。看到 down 时可以手动启动sudo pg_ctlcluster 16 main start启动前建议先看一眼日志日志位置通常在/var/log/postgresql/postgresql-16-main.log。多数启动失败的问题最后都能在日志末尾找到原因比如数据目录权限不对、磁盘空间满、上次异常关机残留了postmaster.pid文件。遇到残留 pid 文件时别急着删先确认没有相关进程在运行再用pg_ctlcluster处理。6.2 认证失败从客户端和服务器两个角度排查认证失败可能是 PostgreSQL 运维里出现频率最高的问题。psql: error: connection to server at 127.0.0.1, port 5432 failed: FATAL: password authentication failed for user ...这种提示通常有三个原因。第一密码确实不对。这个最好查修改密码ALTER USER app_user WITH PASSWORD new_password;第二pg_hba.conf 里的认证方式和你客户端使用的认证方式不匹配。比如 PostgreSQL 16 默认是scram-sha-256如果你的老旧客户端只支持 md5可能连不上这时要么升级客户端驱动要么在 hba 里显式写成scram-sha-256注意 PostgreSQL 14 之前还有可能出现 md5 配置。第三匹配规则没有命中。比如你在 hba 文件里写的网段是192.168.1.0/24但客户端实际来源是192.168.2.10那这条规则就不会生效。排查时可以先在 hba 文件末尾临时加一条host all all 0.0.0.0/0 scram-sha-256如果连接恢复再把来源网段精确化。还有一个非常隐蔽的坑修改 pg_hba.conf 后忘记 reload。曾经有个同事改完文件后告诉我“根本没用”结果一看他压根没执行sudo systemctl reload postgresql。配置文件改了不生效这个问题我见得太多。6.3 Docker 安装 PostgreSQL 的取舍经验除了原生安装Docker 也是 Ubuntu 上非常常见的 PostgreSQL 部署方式。如果你想快速起一个开发环境下面的命令已经很成熟docker run -d --name my-pg \ -e POSTGRES_USERapp_user \ -e POSTGRES_PASSWORDStr0ng!Passw0rd \ -e POSTGRES_DBappdb \ -p 5432:5432 \ -v pgdata:/var/lib/postgresql/data \ postgres:16但如果你准备把 Docker 用在生产环境我建议先想清楚几件事数据卷一定要挂载出去否则容器删掉数据就没了容器里的 PostgreSQL 默认不开启远程日志轮转日志处理要额外接入容器日志方案如果要用 pgvector 或 PostGIS需要选择对应的扩展镜像比如pgvector/pgvector:pg16容器在重启时的自动拉起策略要设置好比如加--restart unless-stopped。我个人的倾向是开发环境用 Docker 很舒服代码仓库里写个 compose 文件一条命令全部起来生产环境则优先原生安装因为处理备份、日志、系统监控、文件系统快照这些事更直接也更容易和现有的运维体系集成。如果你最终选择 Docker也别忘了一样要理解pg_hba.conf和认证机制因为容器里的 PostgreSQL 同样遵循这些规则。环境可以容器化但原理不能跳过。最后再分享一个我个人的习惯无论在哪台 Ubuntu 上装 PostgreSQL装完第一件事永远是确认日志文件位置和集群管理命令而不是急着建库。很多莫名其妙的连接问题、启动问题最后都能在/var/log/postgresql/下找到答案。数据库管理说到底就是“先会看日志再会配权限最后才谈调优”这个顺序千万别搞反。