很多人在 Ubuntu 上装 PostgreSQL 都是奔着“能跑起来”去的装完发现连不上、密码不对、远程访问不了一堆玄学问题全砸过来。这篇文章就是把我实际踩过的坑、试过的方式、最后稳定运行的方案从零开始完整捋一遍。无论你是刚接触 Linux 的小白还是被 pg_hba.conf 搞到头疼的老手看完都能直接用。1. 动手前先想清楚安装方案与版本选择1.1 为什么推荐 apt 直接装Ubuntu 上装 PostgreSQL 有好几种路子用 Ubuntu 官方源里的 apt 包、从 PostgreSQL 官方仓库装、直接下载编译源码、用 Docker 跑容器。我推荐大部分场景用 apt 直接装核心就一句话它能把系统级的依赖和启动脚本全部处理好。你不需要手动去解决 libpq、openssl 这些底层库的依赖关系装完系统还会自动帮你把服务注册成 systemd unit开机自启、日常 stop/start 都有现成命令。相比之下源码编译看着很“硬核”但每次升级都要重新编译一遍还容易和系统自带的 libpq 版本打架除非你有定制编译参数的需求否则真没必要折腾。至于 Docker 方式适合搞开发环境或者想快速试验新版本的人生产环境直接用 apt 省心得多。还有一点值得注意Ubuntu 官方源的 PostgreSQL 版本一般不是最新的但它经过了 Ubuntu 的稳定性测试对于大多数业务来说反而更稳。如果你确实需要尝鲜最新版后面我会讲怎么引入 PostgreSQL 官方仓库二选一即可。1.2 版本选型与系统准备选版本这事很多人不重视上来就装最新版结果后期某个依赖库不兼容或者某个插件还没有对应版本的预编译包自己折腾半天。我的建议很朴素用当下稳定版往前退一个版本。比如现在稳定版是 16那可以选 15 或 16既不会太老也不会踩最新版刚发布时的坑。如果你只是在学基础 SQL、练练 case when版本差异不大随便哪个顺手就行。但如果你是给现有项目配库先确认项目需要的 PostgreSQL 大版本再用apt policy postgresql看当前源里有哪些可选版本。系统层面我建议装之前先跑一遍sudo apt update sudo apt upgrade把系统基础包更新到位。这一步很多人跳过后面积压一堆依赖问题。另外确认一下磁盘空间PostgreSQL 本身也就几百 MB但后续数据目录、WAL 日志、备份文件都是吃磁盘的大户至少留出 5GB 以上的余量免得用着用着磁盘爆掉。内存方面2GB 起步能玩4GB 以上跑生产才舒服后面优化参数也会提到这点。2. 一步一步完成 Ubuntu 上的 PostgreSQL 安装2.1 从官方源安装两条路线任选先讲最省事的 Ubuntu 官方源方案。打开终端依次执行sudo apt update sudo apt install postgresql postgresql-contribpostgresql-contrib是我强烈建议一起装上的它包含很多实用扩展比如pg_stat_statements性能监控用的、postgres_fdw跨库查询用的这些后面你大概率会用到省得之后再单独补装。装完可以验证一下systemctl status postgresql正常情况下会显示active (running)。如果没有执行sudo systemctl start postgresql手动拉起来。再看版本psql --version注意这里的psql是客户端工具而服务端程序是postgres这个区别后面会反复提到。如果你想要官方仓库里的最新版本就得先添加 PostgreSQL 官方 APT 仓库。这个流程稍微绕一点但一次配好后长期受益。以 Ubuntu 22.04 (jammy) 为例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 sudo sh -c echo deb [signed-by/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt jammy-pgdg main /etc/apt/sources.list.d/pgdg.list sudo apt update sudo apt install postgresql-16这里有个细节官方仓库的命名和 Ubuntu 官方源不同比如postgresql-16表示具体的 16.x 版本而 Ubuntu 官方源则是postgresql这个元包。两者不能混着装否则会有两个实例抢端口给自己找麻烦。装了官方仓库的版本后psql是/usr/lib/postgresql/16/bin/psql可能不在默认 PATH 里可以加个软链sudo ln -s /usr/lib/postgresql/16/bin/psql /usr/local/bin/psql2.2 安装完先别看花哨功能验证连接才是第一件事安装完成顺手做一遍连通性检查这是判断环境是否正常的最快路径。PostgreSQL 安装后默认会创建一个名为postgres的系统用户同时也会创建一个同名的数据库超级用户。用下面这条命令切换到postgres用户然后进入 psql 交互界面sudo -u postgres psql能进入类似postgres#的提示符就说明服务端正常起来且监听在本地 socket 上了。注意此时是没有密码的身份认证靠的是 Linux 系统用户的 peer 认证——说白了就是“你当前是 postgres 这个系统用户就用 postgres 数据库用户登录”。这也是很多新手卡住的第一关明明没设密码怎么不用密码就能进不要慌这是正常的。在这个交互界面里可以用\l查看数据库列表用\conninfo查看当前连接信息用SELECT version();确认你实际跑的 PostgreSQL 版本。这三个命令建议装完立刻跑一遍确认自己当前面对的环境是什么状态。3. 初始化环境从“能启动”到“用得稳”3.1 设置密码和修改身份认证方式安装完成后默认只有本地 peer 认证你想用客户端工具比如 Navicat、DBeaver连上去就会吃闭门羹。第一步就是给postgres用户设一个密码ALTER USER postgres WITH PASSWORD 强密码建议别用这种;注意我这就是示意实际密码请组合大小写字母、数字和特殊字符至少 12 位。设完密码只是第一步真正的认证规则在pg_hba.conf里管着。很多教程到这里就断了导致你设完密码还是连不上因为配置文件压根没允许远程方式和密码方式访问。找到配置文件sudo find /etc/postgresql -name pg_hba.conf通常路径是/etc/postgresql/16/main/pg_hba.conf。打开后你会看到一堆local、host开头的规则从上往下匹配第一条命中的规则生效。默认配置里本地 socket 连接用的是peer认证而 TCP/IP 的host规则基本都是scram-sha-256或md5。我建议把文件底部那几行关于 IPv4 和 IPv6 的规则打开并确认host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256这样本地客户端通过 TCP 连接时会要求输入密码并校验安全性比trust好太多。trust意味着“只要网络能到我我不问你是谁直接放行”除非你在完全隔离的内网做测试否则别碰它。改完文件记得重启或重载配置sudo systemctl reload postgresql用 reload 就够了改认证规则不需要重启服务在线生效。3.2 默认端口与监听配置默认端口是 5432除非你要跑多个实例一般不用改。但listen_addresses这个参数值得一看。默认值经常是localhost意思是 Postgres 只会在本机回环地址上监听外部机器根本连不进来。如果你需要允许远程连接找到postgresql.conf里的这行listen_addresses localhost改成listen_addresses *或者指定只监听某个内网 IP比如192.168.1.100。改完同样要 reload。不少人改完监听发现远程还是连不上这时要检查第一云服务商的安全组是否放行了 5432 端口第二本地有没有启 ufw 防火墙执行sudo ufw status看看如果 enabled 了要加一条sudo ufw allow 5432/tcp。这属于运维层面的经典组合拳少一步都不行。顺便提一嘴从 PostgreSQL 15 开始默认的认证方式从trust改成了scram-sha-256所以如果你网上搜到的老教程让你直接改trust然后“免密登录”请无视那是非常危险的操作。3.3 创建专属数据库和用户很多人的习惯是“反正有 postgres 超级用户我就直接用”。这话在个人学习环境里没毛病但在公司、团队或生产环境坚决不推荐用超级用户跑业务。正确姿势是给每个项目建一个专属用户和专属库权限最小化。下面这套是我验证过多次的标准流程-- 1. 创建登录用户设置密码 CREATE USER myuser WITH PASSWORD myuser_password; -- 2. 创建数据库指定 owner CREATE DATABASE mydb OWNER myuser; -- 3. 切换到 myuser 验证权限 \c mydb myuser注意用CREATE DATABASE时如果不指定 owner新库的 owner 是当前执行命令的超级用户 postgres导致业务用户在这个库里没有完整权限后面做 DDL 时会各种报错。所以OWNER myuser这个参数一定不能省。同理通用习惯是先建用户再建库这样建库时就可以顺手指定 owner。如果你需求更细比如只给只读权限可以用GRANT CONNECT ON DATABASE mydb TO readonly_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;4. 日常运维必会的几个操作4.1 备份、恢复和导入导出手法备份是运维的底线必须形成肌肉记忆。PostgreSQL 提供了两套工具pg_dump用于单库逻辑备份pg_basebackup用于整个实例的物理备份。平时用得最多的是pg_dump用它做定时备份的常见姿势sudo -u postgres pg_dump -F c -b -v -f /backup/mydb.dump mydb这里-F c表示自定义压缩格式-b表示包含大对象-v输出详细信息。恢复的时候用pg_restoresudo -u postgres pg_restore -d mydb -C --no-owner /backup/mydb.dump-C会自动创建数据库--no-owner避免在目标机上因用户不存在而报错。如果只是想导出成纯 SQL 文件用sudo -u postgres pg_dump -f /backup/mydb.sql -U myuser mydb配合 crontab 就能做每日自动备份。这里我想强调备份脚本最好先在测试环境完整演练一遍恢复流程不要备份完就丢在那里不管。我见过太多人备份文件很大但从未验证过能不能恢复等到真要恢复时才发现备份是坏的那一刻的心情你肯定不想体验。4.2 日志、状态和性能排查入门数据量一大慢查询就开始冒出来了。此时第一件事不是去调参数而是打开查询日志。在 postgresql.conf 里这样设置logging_collector on log_directory log log_statement none log_min_duration_statement 1000最后一行表示“超过 1000 毫秒的 SQL 都记录下来”这是定位慢查询最直接的方式。日志文件默认在/var/log/postgresql/下按天滚动。想看到实时活动直接在 psql 里查视图SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state active;这个视图是排查并发问题的入口。看到某个进程长期处于idle in transaction状态说明有事务没提交或没回滚锁会越积越多最终拖垮整个库。处理它SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state idle in transaction AND age(now(), state_change) interval 30 minutes;这些命令看起来简单但在关键时刻能救你一命。5. 进阶实用case when 写法和系统表查询技巧5.1 case when 的三种常用姿势与盲区PostgreSQL 的CASE WHEN是日常写 SQL 最高频的表达式之一但用得溜的人并不多。基本语法有两种简单表达式和搜索表达式。简单表达式这样写SELECT name, CASE level WHEN 1 THEN 初级 WHEN 2 THEN 中级 WHEN 3 THEN 高级 ELSE 未知 END AS level_name FROM users;搜索表达式则支持条件判断更灵活SELECT product_name, price, CASE WHEN price 100 THEN 便宜 WHEN price 100 AND price 500 THEN 中等 WHEN price 500 AND price 1000 THEN 偏贵 ELSE 高端 END AS price_label FROM products;这里有几个新手容易踩的坑。第一个坑CASE的每个分支返回的类型必须一致如果你一个分支返回字符串、另一个返回数字PostgreSQL 会直接报错“cannot cast type”。第二个坑ELSE分支可以省略省略时默认返回NULL很多人没写 ELSE 导致结果里莫名出现一堆 NULL排查半天才发现是自己没加。第三个坑是性能相关的在 WHERE 条件里用CASE时如果条件分支很多且字段有索引可能让优化器放弃索引扫描。实测下来能用简单等值条件就尽量不堆CASE写起来花哨跑起来不一定快。还有一招比较少见但很实用CASE和聚合函数配合实现条件计数。比如统计一张订单表里有多少单是线上支付、多少单是货到付款SELECT COUNT(CASE WHEN pay_type online THEN 1 END) AS online_cnt, COUNT(CASE WHEN pay_type cod THEN 1 END) AS cod_cnt FROM orders;这种写法比多次查询数据库效率高得多。还有一个更优雅的替代方案用FILTER子句。PostgreSQL 支持COUNT(*) FILTER (WHERE condition)语义更清晰性能也和CASE版本差不多。两者都值得记下来碰到具体场景按习惯选一个。5.2 系统表pg_catalog 里真的有宝贝PostgreSQL 把元数据存在一组系统表里最常用的几个一定要眼熟。pg_stat_activity前面已经用到过另外还有几个日常排查离不开想查看某个数据库下所有表的大小按大小排序找罪魁祸首SELECT tablename AS table_name, pg_size_pretty(pg_total_relation_size(public. || tablename)) AS total_size FROM pg_tables WHERE schemaname public ORDER BY pg_total_relation_size(public. || tablename) DESC;想查看某个表的索引有没有被实际使用可以查pg_stat_user_indexes。其中idx_scan字段如果长期为 0说明这个索引可能根本没被用到要么删除要么重写查询让它能命中。这个技巧在索引优化中非常管用。想知道哪些用户可以连哪些库查pg_roles和pg_databaseSELECT r.rolname AS role_name, d.datname AS db_name, has_database_privilege(r.rolname, d.datname, CONNECT) AS can_connect FROM pg_roles r CROSS JOIN pg_database d WHERE r.rolname NOT LIKE pg_% ORDER BY r.rolname;关于系统表我想提个安全提示系统表是该看的看但不能乱写。新手最容易犯的错是想改pg_database里的某个字段来调整库属性直接UPDATE pg_database SET ...然后整个库出问题。所有数据库属性都有正规的命令行工具或 SQL 命令去改比如改库名用ALTER DATABASE系统表只是让你“看”的。6. 常见问题与排查实录6.1 连接问题peer、port、listen 的三重门本地连不上、远程也连不上、密码明明对却被拒绝这三种情况占了新手问题的八成。我把常见现象、原因和解决方案整理成一张速查表报错现象常见原因解决方案FATAL: Peer authentication failed for user postgres当前 Linux 用户名与数据库用户名不一致用sudo -u postgres psql或在 pg_hba.conf 中调整认证方式could not connect to server: Connection refusedPostgreSQL 没启动或端口不对systemctl status postgresql检查状态确认 5432 端口是否监听password authentication failed for user xxx密码错误或 pg_hba.conf 用了 peer/trust先用sudo -u postgres psql重置密码再检查认证规则no pg_hba.conf entry for host远程连接被配置拒绝在 pg_hba.conf 中添加允许访问的 IP 段并 reloadIs the server running on that host and accepting TCP/IP connections?listen_addresses 没改确认 postgresql.conf 中listen_addresses不为 localhost重启后运行 ss -tunlp有一个排查顺序可以救命先从本机 psql 连起本机能通说明数据库没问题再从局域网另一台机器试能通说明监听没问题最后才是从公网机器连此时检查安全组和防火墙。一层层排除不要一上来就怀疑数据库有问题大多数时候问题出在中间链路。6.2 误操作恢复删除数据能救回来吗很多人遇到过手滑把表删了或者说DROP DATABASE一激动敲错了。PostgreSQL 没有像 Oracle 那样的 flashback 机制所以恢复数据基本全靠备份。但我可以分享一个实用的“后悔药”流程多了一层保障前提是你开启了 WAL 归档或至少保留了足够多的 WAL 文件。如果只是误删了几行数据且你知道大致时间点可以用pg_waldump查看 WAL 内容或使用pageinspect扩展恢复部分数据。但这些手段对普通用户来说门槛偏高而且没有 100% 的保证。所以真正的建议是重要的库绝对不要裸奔。每天做全量备份再配合pg_basebackup做物理备份。前者用于快速恢复单表后者用于完整恢复整个实例。相信我等到出了事故再研究怎么恢复眼泪会告诉你备份有多重要。我自己早期就是没做备份结果开发环境的一个库因为测试脚本写错被清空花了整整两个晚上重构表结构。自此以后所有环境一律开启定时备份任务推荐用pg_dump加cron五分钟就能配好。6.3 卸载干净不乱残留删除 PostgreSQL 也是一门技术活。很多人直接sudo apt remove postgresql完事结果发现dpkg重新安装时报错说版本冲突或者老的配置文件还在干扰新实例。想彻底卸载sudo apt purge --remove postgresql-16 sudo apt autoremove如果没有用官方仓库那还要把/etc/postgresql/、/var/lib/postgresql/这是数据目录、/var/log/postgresql/这三处残留清理干净。数据目录是整个实例的命根子删除前必须确认里面的数据已经备份或者真的不要了。我碰到过同事不小心删掉整个/var/lib/postgresql/16/main的场景那种无力的感觉至今记得。所以在出现rm -rf的地方请先把输入法切到英文然后深呼吸再敲一遍命令确认。写在最后的一点私货在我实际使用 PostgreSQL 的这几年里最大的体会是这东西安装不难难的是理解它背后的设计逻辑。比如 peer 认证、监听地址、认证规则每一层单独拿出来都很简单但组合在一起就让新手发懵。很多时候你连不上库不是因为你命令敲错了而是因为你脑子里的模型少了其中一环。只要你理解了“数据库装在机器上、默认只信本机用户、远程访问需要两步网络通 认证过”这个核心链路绝大多数问题都能自己推出来。还有一个受用至今的习惯每次改完postgresql.conf和pg_hba.conf先用sudo -u postgres psql -c SELECT 1验证一下连接再跑systemctl reload postgresql千万不要直接 restart否则在改错参数的情况下服务可能直接起不来到时候连补救进去都困难。如果不小心改坏了导致服务起不来可以在postgresql.conf里临时调低log_min_messages到 debug 查看日志或者利用pg_ctl单独启动试错。最想说的是PostgreSQL 是一个越用越有回报的数据库它的文档质量在开源界属于顶级碰到问题翻官方手册永远是第一选择。装好它只是起点后面还有很多有趣的东西可以玩。动手装一遍踩一遍坑你会记住得比看十篇文章都深。