1. 安装前的准备工作别急着敲命令先想清楚三件事先说个题外话标题里写的“unubtu”大概率就是 Ubuntu 的笔误这种拼写错误在我们日常搜资料时太常见了Linux 相关关键词本来就容易打错不影响理解就行。这次我也顺手整理了一份完整的 Ubuntu 部署 PostgreSQL 流程把能踩的坑都提前替你们踩了一遍。PostgreSQL圈里一般都叫 pgsql在 Ubuntu 上装它没有想象中那么复杂但绝对也不是一条apt install postgresql就能高枕无忧的事。很多新手装上之后发现连不上、密码不对、字符集乱掉、性能拉胯这些问题大多不是安装本身造成的而是准备工作没做到位。在动手之前我认为有三件事必须搞清楚版本选哪个、装到哪里去、数据放哪里。这三个问题直接影响后续所有配置流程也决定了你是能用得舒服还是三天两头来一次“救火”。1.1 版本选择apt 默认源就够用吗Ubuntu 官方仓库里确实带了 PostgreSQL版本根据系统发行版不同从 14 到 16 都有。如果只是本地测试、学习 SQL或者跑一个小型内部应用直接用系统源里的版本完全够了因为它经过了 Ubuntu 团队的测试和打包依赖关系处理得干净卸载也省心。但如果你要跑生产环境或者业务上明确要求某个大版本比如 17 的新特性那我建议直接添加 PostgreSQL 官方 apt 仓库。理由很简单官方仓库的版本更新及时安全补丁同步快而且能够精确控制你要装的大版本。尤其遇到那种“开发环境用的 16、生产环境可能上 17”的情况你不可能指望系统源给你同时提供这么细的选择。实操上添加官方仓库的方式也不复杂核心步骤是导入 GPG 密钥、写源列表、然后apt update。我个人一直习惯用官方脚本setup script它会把密钥和源一次性配好。不过这里要留意一点官方源脚本默认只支持当前系统版本对应的发行版代号如果你的 Ubuntu 是 LTS 版本那一般都没问题但如果是非 LTS 的中间版本可能就得手动改 source list 里的代号了。1.2 磁盘规划数据目录不要留在系统盘这个点是我最想强调的。很多人装完 PostgreSQL 后稀里糊涂地把数据默认放在/var/lib/postgresql如果是虚拟机或者云主机系统盘通常不大一旦业务数据涨起来整台机器都可能被拖死。我见过最典型的案例是某个项目把 pgsql 装在 40G 系统盘的云服务器上跑了两个月后磁盘直接打满数据库只读全组人加班。所以在安装之前先规划好数据盘。如果是物理机或虚拟机建议单独分一个数据分区挂载到/data或者/var/lib/postgresql的独立挂载点如果是云主机先把数据盘挂载好再装数据库。这一步不是 PostgreSQL 特有的要求但 pgsql 对磁盘 IO 的敏感度比很多应用都高数据目录落在慢盘或系统盘上后面性能问题会非常难排查。还有一个小细节目录权限。PostgreSQL 的postgres系统用户需要数据目录的读写权限如果你手动创建了新的数据目录一定要记得chown postgres:postgres不然初始化数据库的时候会直接报权限错误。这个错误很常见报错信息也写得含糊新人很容易在这里卡半小时。1.3 系统依赖与网络连通性检查装 pgsql 之前我建议先把基础工具链备齐。build-essential、curl、wget、gnupg这些看着跟数据库没什么关系但实际配置官方源、编译扩展模块、排查网络问题时都会用到。用 apt 安装的时候系统会自动拉依赖不必太纠结但如果后面你要装 PostGIS、pgvector 这类第三方扩展编译环境缺了可就寸步难行。网络连通性要单独检查一下。Ubuntu 官方源在国内一般没问题但 PostgreSQL 官方 apt 源有时候连接不稳。如果遇到apt update长时间卡住或者连接超时可以考虑换国内镜像源的对应仓库地址或者干脆先离线下载 deb 包再手动安装。这个操作不丢人在这种事情上千万不要死磕时间比什么都金贵。2. 完整安装流程从空机到 pgsql 正常跑起来这个章节我把两种安装方式都过一遍。第一种是用 Ubuntu 系统源安装最快最省事第二种是配置 PostgreSQL 官方源安装指定版本适合有版本控制需求的项目。两种方式我会分别讲清楚操作步骤和背后的选择逻辑。2.1 方法一系统源一键安装sudo apt update sudo apt install postgresql postgresql-contrib两条命令装完就完事。安装过程中 apt 会自动创建postgres系统用户并初始化一个默认的数据目录。装完检查一下状态sudo systemctl status postgresql正常情况下你会看到服务是 active 的。另外注意一点Ubuntu 上 PostgreSQL 默认监听 localhost也就是本地回环地址127.0.0.1这个默认策略其实是安全的先别急着改后面需要远程访问再调整。系统源安装最大的优势是省心所有配置文件和目录结构都放在标准位置适配 Ubuntu 的 LTS 维护周期。最大的劣势是版本不灵活。比如你现在跑 Ubuntu 22.04默认源里是 PostgreSQL 14想上 16 就得走官方源或者第三方 PPA。2.2 方法二官方源安装指定版本如果你确定要用某个大版本按下面这套流程操作。以 Ubuntu 22.04 和 PostgreSQL 16 为例# 安装依赖工具 sudo apt install -y curl ca-certificates gnupg # 导入官方 GPG 密钥 sudo install -d /usr/share/postgresql-common/pgdg 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 $(lsb_release -cs)-pgdg main /etc/apt/sources.list.d/pgdg.list # 更新索引并安装 sudo apt update sudo apt install -y postgresql-16这里有个细节值得讲一下为什么要把密钥文件放到/usr/share/postgresql-common/pgdg而不是直接写signed-by指向其他路径因为 PostgreSQL 官方提供的脚本里这个目录是约定俗成的公共位置避免你在多个版本之间切换时密钥文件混乱。lsb_release -cs会自动获取系统版本代号比如 22.04 对应jammy16.04 对应xenial所以这条命令无论换到哪台机器都能自适应。装完官方源版注意 Ubuntu 上默认数据目录是/var/lib/postgresql/16/main配置文件在/etc/postgresql/16/main/下。这是和源码安装最大的区别——源码安装通常把所有配置都放在同一个前缀目录下而 apt 方式遵循 Debian 系的管理规范配置和数据分离。2.3 安装后必做设置密码与登录验证很多教程会告诉你安装完后直接sudo -u postgres psql进命令行这本没错但容易造成一个误区以为 PostgreSQL 不需要密码认证。实际上 pgsql 默认的peer认证只允许本机系统用户postgres免密登录一旦你配置远程连接认证方式就得切换成md5或scram-sha-256密码这关躲不开。设置 postgres 用户密码有两层含义一是 PostgreSQL 里的postgres超级用户密码二是 Ubuntu 系统里的postgres账户密码。两者尽量别混淆平时我们常用的是前者。sudo -u postgres psql ALTER USER postgres WITH PASSWORD 你的强密码; \q这里的sudo -u postgres psql这一句非常关键它利用了系统认证直接进入数据库命令行不需要输入数据库密码。很多新手在这一步被卡住是因为他们试图psql -U postgres然后输入密码但此时密码还没设置过自然会认证失败。完成密码设置后可以顺手验证一下基本功能建一个测试库插入几条数据跑一下查询确认服务健康。这一步不是浪费时间而是让你在还没堆业务代码的时候先确认数据库基础链路是通的。3. 核心配置让 pgsql 真正“好用”起来安装只是第一步配置才是重头戏。很多人提到 pgsql 配置就想到postgresql.conf和pg_hba.conf这两个文件方向没错但里面参数太多新手很容易被绕晕。这一章我只挑关键的、高频踩坑的配置项讲目标很明确让数据库既能本机用也能安全地支持远程连接同时性能别太离谱。3.1 网络配置如何安全地开启远程访问先修改postgresql.conf中的监听地址sudo vim /etc/postgresql/16/main/postgresql.conf找到listen_addresses这一行默认是localhost意思是只监听本机。如果想让其他机器连接改成listen_addresses **表示监听所有网络接口。从安全角度讲我不建议直接改成*如果你能确定客户端所在网段更好的写法是listen_addresses 192.168.1.100, 127.0.0.1把具体地址列出来。这样就算数据库被人扫到端口也不是随便哪个 IP 都能连进来。改完监听地址这只是第一步。真正的“守门员”是pg_hba.conf这个文件全称是 PostgreSQL Host-Based Authentication。它决定了哪些来源 IP 可以用什么方式认证。默认配置基本只允许本地peer认证所以你需要追加或修改一行host all all 192.168.1.0/24 scram-sha-256这行的意思是来自192.168.1.0/24网段的所有用户访问所有数据库必须使用scram-sha-256方式认证。注意认证方式明确写scram-sha-256而不是trust—trust等于不打密码直接放行撑死了在内网调试时用一下正式环境千万别碰。配置完成后重启服务sudo systemctl restart postgresql然后从另一台机器测试连接psql -h 你的服务器IP -p 5432 -U postgres -d postgres如果连接失败先用telnet或nc测试端口连通性再用tail -f /var/log/postgresql/postgresql-16-main.log看日志。端口不通大概率是防火墙或云安全组没放行 5432这个跟数据库本身没关系。3.2 性能参数不是所有机器都该用默认值PostgreSQL 的默认配置偏向“保守”——为了在任何机器上都能跑起来参数都设置得很小。如果你装的机器内存有 16G 或 32G那默认配置简直是在浪费硬件资源。重点调整这几个参数shared_buffers共享缓冲区建议设置为物理内存的 25%。注意不是越大越好因为 PostgreSQL 还要依赖操作系统页缓存如果这一项设太高反而可能导致内存浪费和性能抖动。work_mem单个排序或哈希操作可用的内存。默认 4MB 偏小复杂的ORDER BY、GROUP BY、JOIN语句容易落盘。可以调整到 16MB 到 64MB但要注意这个参数是“每个操作”都适用的连接数多时内存会累加。maintenance_work_mem维护操作如VACUUM、CREATE INDEX可用的内存默认 64MB可以调到 1GB 甚至更高能显著缩短大表索引创建耗时。effective_cache_size这个参数不影响实际内存分配只是告诉优化器“系统里大概有多少缓存可用”。设得过低会导致优化器偏向选择索引扫描而不是顺序扫描。这些参数的修改还是在postgresql.conf里改完不需要重启可以SELECT pg_reload_conf();热加载。不过某些参数比如shared_buffers必须重启才能生效。性能调优这件事我个人的建议是“先观察再调整”。不要一上来就抄别人的配置每台机器的负载模型、连接数、数据量都不一样。先用默认配置跑业务再用pg_stat_statements或EXPLAIN ANALYZE分析慢查询针对性调整。盲目堆参数的结果往往不是性能飞升而是内存溢出或者 OOM。3.3 字符集与数据目录两个容易忽视的坑如果建库的时候没有指定字符集PostgreSQL 默认采用模板库template1的字符集。系统初始化时这个库的编码通常跟随系统的locale设置。如果你的服务器环境变量里LANG是en_US.UTF-8那默认建出来的库一般就是 UTF8没问题但如果你拿到的机器是某些定制的系统镜像locale 设置异常可能初始化出来的数据库就不是 UTF8后面存中文就出现?或者报编码错误。解决方式有两种一是初始化时显式指定sudo -u postgres createdb mydb --encodingUTF8 --localeC二是在postgresql.conf里强制设置默认编码相关的参数。不过更省事的做法是创建业务库之前先确认模板库编码SELECT datname, pg_encoding_to_char(encoding) FROM pg_database;如果发现模板库编码不对优先调整系统 locale 后重新初始化集群。这个操作比较麻烦所以强烈建议在安装完 PostgreSQL 后做任何建库动作之前先检查一遍编码。4. 深入技巧case when 的几种高阶写法安装、配置都讲完了下面聊一个每个 pgsql 用户迟早会用到的语法CASE WHEN。这个语法在所有关系型数据库里都有但 PostgreSQL 的实现在灵活性和表达力上非常出色。理解它的底层逻辑是写出高质量 SQL 的关键一步。热搜里有“pgsql case when写法”说明很多人对这个基础语法还存在不少疑问。其实CASE WHEN的作用简单粗暴在 SQL 里做条件判断根据条件返回不同的值。它可以出现在SELECT列表里也可以出现在WHERE、ORDER BY、GROUP BY中甚至能配合聚合函数完成“条件计数”这种骚操作。4.1 基础写法与常见用法先看最标准的写法SELECT name, CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 END AS level FROM student_scores;这个写法本质上就是一个“多路分支判断”从上往下匹配命中即返回后续条件不再判断。这里有个细节很多人踩过坑条件顺序会影响结果。如果先写WHEN score 60 THEN 及格再写WHEN score 90 THEN 优秀那考了 95 分的学生也会被归到“及格”因为第一个条件就命中了后面的分支根本轮不到。所以写CASE WHEN时优先级高的条件必须放在前面。除了这种标准写法PostgreSQL 还支持简化版的CASE表达式SELECT name, CASE status WHEN 1 THEN 启用 WHEN 0 THEN 禁用 ELSE 未知 END AS status_text FROM users;简化版适合“同一个字段做等值判断”的场景可读性比标准版更清爽。但注意它只能做等值匹配不能做范围判断如果你要判断score 90这种条件还是得回到标准写法。4.2 在聚合与排序中的实战用法CASE WHEN真正强大之处在于它可以无缝配合聚合函数。举个实际场景统计每个班级的及格人数和优秀人数。通常的写法是SELECT class_id, COUNT(*) AS total, COUNT(CASE WHEN score 60 THEN 1 END) AS pass_count, COUNT(CASE WHEN score 90 THEN 1 END) AS excellent_count FROM student_scores GROUP BY class_id;注意这里COUNT只会计数非 NULL 值。CASE WHEN score 60 THEN 1 END在条件不满足时返回 NULL所以COUNT自动忽略它们。这种写法比先过滤再分组要高效得多一次扫描就完成了所有统计不用写三条子查询再 join 回来。另一种常用场景是在ORDER BY里做“自定义排序规则”。比如状态字段有pending、done、cancelled三种值你希望查询结果按pending→done→cancelled的顺序排列而不是字典序SELECT * FROM orders ORDER BY CASE status WHEN pending THEN 1 WHEN done THEN 2 WHEN cancelled THEN 3 ELSE 4 END;这种写法在管理后台做任务列表、工单列表时非常常见。客户要的排序逻辑不是简单的升序降序而是业务规则里规定的优先级顺序用CASE WHEN就能在不改表结构的情况下快速搞定。4.3 性能与可读性的平衡CASE WHEN写多了以后会遇到一个争议性问题到底该在 SQL 里写复杂逻辑还是把逻辑放到应用层去处理我的实践经验是简单的条件分组、排序映射放在 SQL 里完全没问题数据库本来就是干这个的但如果逻辑太复杂比如嵌套了三层以上的CASE WHEN那最好还是拆成子查询或者视图。为什么两个原因。第一嵌套过深的CASE WHEN可读性极差两个星期后你再回来看这段 SQL大概率要想半天才能理清分支逻辑。第二复杂的条件表达式会导致查询优化器难以准确估算行数可能产生错误的执行计划。这时候反而把一个复杂查询拆成多个简单查询让优化器可以单独优化每一步整体性能往往更好。还有一个性能误区要澄清CASE WHEN并不会阻止索引使用。有些人担心写了条件判断就走不了索引其实没有必然关系。只要WHERE子句里的条件本身是可以索引的SELECT列表里有没有CASE WHEN都不影响索引扫描。真正影响索引的是对列做函数操作比如WHERE UPPER(name) TOM这种写法。5. pgsql 系统表运维和调优的“透视镜”最后这部分我把前面提到的热词“pgsql 系统表”展开讲。PostgreSQL 和 MySQL 一个显著的不同点就是它的系统表机制极其复杂且强大。MySQL 的 information_schema 已经挺好用了但 pgsql 更狠它有完整的系统目录和一堆pg_*视图几乎把所有元信息都暴露给了数据库管理员。学会查系统表等于给你的数据库装了一个透视镜。哪些表膨胀了、哪些索引没被用到、哪个查询在占资源、磁盘上到底有啥都能通过系统表一目了然。这一部分的内容对 DBA 和新手都是刚需。5.1 必知必会的核心系统表和视图PostgreSQL 的系统表非常多但日常运维高频使用的其实就那么几个pg_database列出了集群里的所有数据库包含编码、所有者、表空间等信息。pg_class记录了表、索引、视图、序列等所有关系的元数据。注意relkind字段区分类型r是普通表i是索引v是视图。pg_attribute列信息表配合pg_class能查出一个表的所有字段名和类型。pg_index索引信息包括索引对应的表、索引列等。pg_stat_activity当前会话和查询状态排查慢查询和锁等待的第一入口。pg_stat_user_tables每个用户表的统计信息比如扫描次数、插入/更新/删除行数。光列表没意思我来演示几个实际用得到的查询。比如你想知道当前数据库里有哪些表占的空间最大SELECT relname AS table_name, pg_total_relation_size(relid) AS total_size_bytes, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;pg_total_relation_size包含了表本身、索引、TOAST 表的全部空间是评估表存储开销最全面的指标。用pg_size_pretty把字节数转成人类可读的格式避免一长串数字看得头疼。再比如排查死锁或长事务SELECT pid, usename, state, wait_event_type, wait_event, now() - xact_start AS xact_duration, query FROM pg_stat_activity WHERE state ! idle ORDER BY xact_start;如果某个事务持续时间特别长而且wait_event显示在等待锁那基本可以判断有锁竞争。这时候就不能指望系统帮你自动解决得手动分析是哪个会话持有了锁。进一步可以查询pg_locks视图把锁的持有者和等待者找出来。5.2 利用系统表定位“失效”索引索引不是建了就能一劳永逸。很多时候业务变了查询模式变了某些索引就成了摆设——占着磁盘空间每次写入还要额外维护完全得不偿失。怎么找出这些没用上的索引pg_stat_user_indexes这个视图就派上用场了SELECT i.relname AS index_name, t.relname AS table_name, s.idx_scan AS times_used, s.idx_tup_read AS rows_read, pg_size_pretty(pg_relation_size(i.oid)) AS index_size FROM pg_stat_user_indexes s JOIN pg_class i ON i.oid s.indexrelid JOIN pg_class t ON t.oid s.relid WHERE s.idx_scan 10 ORDER BY pg_relation_size(i.oid) DESC;这个查询把“扫描次数很少但占空间很大”的索引捞出来。idx_scan是索引被扫描的次数如果这个数字长期接近 0而索引体积又很大那基本可以判定为无效索引。不过删除之前还是要慎重最好跟开发确认一下有没有定期任务或特殊查询会用到别把唯一约束的索引给删了。5.3 快速定位慢查询的通用模板排查慢查询是每个 DBA 的日常。PostgreSQL 提供了pg_stat_statements扩展但它默认没有启用需要手动安装。开启方式并不麻烦-- 在 postgresql.conf 中加入 shared_preload_libraries pg_stat_statements改完后需要重启数据库。重启完成后执行CREATE EXTENSION pg_stat_statements;然后你就可以用这个视图来查最耗资源的 SQLSELECT calls, round(total_exec_time::numeric / 1000, 2) AS total_sec, round(mean_exec_time::numeric / 1000, 2) AS mean_sec, round(max_exec_time::numeric / 1000, 2) AS max_sec, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;total_exec_time是总执行时间单位是毫秒。这个查询能一眼看出哪些 SQL 占据了大部分数据库资源。注意新版 PostgreSQL 里字段名从total_time改成了total_exec_time如果用的是老版本字段名可能不同写之前先\d pg_stat_statements确认一下。有了这个清单你就有了调优的靶子。接下来的工作就是逐个分析这些慢查询看执行计划、找缺失索引、优化 SQL 写法。6. 安装与使用中的常见问题排查前面把安装、配置、SQL 技巧和系统表都过了一遍这最后一章我来集中梳理一下新手最容易踩的几个坑。这些问题我几乎每次帮别人排查时都会遇到写出来希望能帮大家节省一点试错时间。第一个是“忘记 postgres 系统用户密码”。这个其实不算问题因为 pgsql 的超级用户密码跟系统用户是分开的。只要你能sudo随时可以进入数据库重置密码sudo -u postgres psql ALTER USER postgres WITH PASSWORD newpassword;如果连sudo权限都没了那属于服务器管理范畴的问题只能找管理员帮忙这就不是数据库本身的问题了。第二个是“远程连接不上但端口明明通了”。这种情况我遇到太多次了端口通说明网络层没问题问题基本都出在pg_hba.conf和postgresql.conf的配合上。常见错误包括只改了监听地址没改pg_hba.conf、pg_hba.conf里写了trust但客户端用密码登录、或者认证方式写错了导致服务器拒绝连接。排查思路就是一句话先看日志日志永远知道真相。tail -f数据库日志看它报了哪个错误跟着错误走准没错。第三个是“默认端口被人扫了”。PostgreSQL 默认端口 5432 太出名了放在公网服务器上非常容易被扫描尝试。如果这个数据库不需要对外提供服务那建议把监听地址保持localhost别给自己找事。如果确实需要公网访问起码要修改默认端口、开启防火墙限制来源 IP、启用 SSL 连接。这些基础安全措施看起来很繁琐但真出事的时候每一道都是防线。个人经验小结装 PostgreSQL 这件事说难不难说简单也有一堆细节。我自己从第一次在 Ubuntu 上装 pgsql 到现在经历过数据目录放系统盘导致磁盘满、pg_hba.conf配置错误导致所有人连不上、误删索引导致查询性能雪崩——这些坑每一个都折腾过不短的时间。回头来看核心经验就是两条第一动手前把目录、版本、认证方式这些“地基”想清楚第二遇到问题先看日志别把时间浪费在盲猜上。这篇文章里的安装流程和排查思路都是我实际验证过的希望可以帮你在 pgsql 这条路上走得更顺。