1. psql 到底是个什么工具为什么数据库老手离不开它如果你刚接触 PostgreSQL打开搜索引擎大概率会看到一堆图形化工具pgAdmin、Navicat、DBeaver 这些界面漂亮、按钮齐全看起来比黑底白字的命令行友好得多。但真当你开始做开发、搞运维、处理线上问题的时候会发现最离不开的反而是 PostgreSQL 自带的那条命令行客户端——psql。psql 的全称是 PostgreSQL Command-Line Client它随 PostgreSQL 服务端一起安装不需要额外下载任何东西。它天然具备脚本化能力可以用一条命令完成整个数据库的备份恢复、批量执行 SQL、定时刷新监控指标还可以直接嵌入到自动化流水线里。图形工具能做到的它基本都能做到图形工具做起来麻烦的事它往往几分钟就搞定了。这篇文章我不会从教程文档的角度给你罗列参数而是以我日常用 psql 的真实场景为主线把连接、操作、脚本、调试、排障这些环节完整过一遍。无论你是刚装好数据库的小白还是已经在生产环境摸爬滚打的老手相信都能在这里找到点有用的东西。提示这篇文章默认你已经安装好了 PostgreSQL 服务端。如果还没装建议先跳到 1.2 节把环境准备好再回来继续往下读。1.1 图形工具和 psql 的本质区别一个是逛街一个是开车很多初学者问过我一个问题既然 pgAdmin 那么直观为什么要花时间学 psql我用一个类比来解释图形工具像是逛街你能看到菜单、看到按钮、看到所有选项适合探索和临时操作。psql 像是开车你得先记住几个关键操作但一旦熟练效率是逛街没法比的。更重要的是车辆可以自动驾驶——脚本。举一个真实的例子。某次线上数据库的某个表数据异常需要批量更新一万多条记录。用 pgAdmin 打开表界面一条条改改到几十条就得崩溃。我用 psql 写了一条 UPDATE 语句加上 WHERE 条件限定范围配合事务控制先开启事务执行、检查受影响行数、确认无误后提交整个过程不超过三分钟。再比如说写存储过程调试pgAdmin 里写 PL/pgSQL 代码没办法快速反复执行而 psql 的\e命令可以直接调起系统编辑器保存后自动执行报错信息直接打在终端里这个体验比任何图形工具都舒服。1.2 想用 psql先把 PostgreSQL 装好版本选择与平台差异很多人在搜索引擎里搜postgresql 下载哪个版本其实就是想确认一件事我该装最新版还是稳定版我的建议是学习环境直接装最新的稳定版。写这篇文章时 PostgreSQL 16 已经非常成熟17 也已经发布新版本在查询性能、并行计算、逻辑复制方面都有明显改进。对新手来说装 16 或者 17 都行两者语法在基础使用上没有本质区别教程里的命令通用性很强。生产环境跟开发环境的版本尽量对齐大版本差异不要超过一个。比如开发用 16生产也用 16避免开发环境语法在生产环境不兼容的尴尬。不要在生产环境用测试版这是底线PostgreSQL 每个大版本初期可能还有隐藏的稳定性问题线上系统没必要去赌这个概率。至于postgresql 16 便携版这种需求我的看法是便携版适合临时演示、在没权限的机器上做实验但日常开发我不推荐。PostgreSQL 的完整功能依赖服务管理、目录权限、环境变量这些系统级配置便携版在这些方面天然受限出了问题排查起来反而更麻烦。老老实实用安装包装一遍你对它的理解会深得多。Windows 平台的安装尤其要注意两点第一安装过程中设置的 postgres 超级用户密码一定要记住这是你第一次连接数据库的凭证忘了就只能改配置文件重置第二默认端口 5432如果被其他程序占用了安装时可以改成别的但后续所有连接命令都要跟着变。Linux 平台用 apt 或 yum 安装macOS 推荐用 Homebrew装完之后确认一下psql --version能正常输出环境就基本没问题了。2. 连接数据库从第一行命令开始装好 PostgreSQL 之后第一件事就是连上它。psql 的连接命令格式比我见过的大多数数据库客户端都要直观核心就一句话psql -U 用户名 -h 主机地址 -p 端口号 -d 数据库名拆开看-U指定用户默认是 postgres 超级用户-h指定主机地址本地连接可以写 localhost 或 127.0.0.1-p指定端口默认 5432一般不需要改-d指定数据库名注意是数据库名不是模式名两者的区别后面会讲第一次连接通常长这样psql -U postgres -h localhost -p 5432 -d postgres回车后输入密码看到下面的输出就说明成功了psql (16.4) 输入 help 来获取帮助信息. postgres#postgres#这个提示符表示你现在正连接在名为 postgres 的数据库上后面如果是#说明你是超级用户如果是说明当前用户没有超级权限比如postgres。2.1 我第一次连接时踩过的三个坑照理说连接这个操作不该出问题但我见过太多新手在这上面卡壳包括当年的我自己。三个最常见的坑坑一默认数据库名不知道填什么。很多人以为连接数据库不需要指定库名直接就psql -U postgres回车。结果 PostgreSQL 会尝试连接一个跟你用户名同名的数据库postgres 用户的默认库是 postgres如果你创建过别的名字的库这个默认行为会连错目标。解决方式养成习惯连接时总带上-d参数。坑二密码认证失败。明明安装时设置的密码就是对的连上去就是报password authentication failed。这种情况十有八九是用户或者主机匹配错了认证规则。PostgreSQL 的认证规则由pg_hba.conf文件控制里面定义了什么样的连接方式、什么 IP 段、什么用户、需要用哪种认证方法。Windows 安装版默认通常把本地连接的认证方式设为scram-sha-256如果之前有人改过这个配置或者你用的是默认的trust绝对信任不需要密码行为就会很怪。关于这个文件的排查方法我在第六部分会详细讲。坑三端口连不上。这症状是命令执行后卡住不动直到超时。多数情况是 PostgreSQL 服务根本没启动或者防火墙把 5432 端口挡了。Linux 上可以用systemctl status postgresql查看服务状态Windows 上在服务管理器里找 PostgreSQL 的服务名手动启动。防火墙的问题用telnet 127.0.0.1 5432快速验证通不通一下就知道了。2.2 用连接串一步到位适合脚本和工具链除了用-U -h -p -d逐个指定参数psql 还支持用连接串connection string一步到位这个在配置脚本、CI 流水线里尤其好用psql postgresql://postgres:你的密码localhost:5432/mydb连接串的格式是严格的协议://用户:密码主机:端口/数据库名。如果把密码直接写在里面安全性存疑推荐用环境变量PGPASSWORD或在~/.pgpassWindows 是%APPDATA%\postgresql\pgpass.conf里维护密码这样命令行里就不用出现明文密码。另外还有一组环境变量可以简化日常操作PGHOST、PGPORT、PGUSER、PGDATABASE。只要设置好这几个直接输入psql就能连接不需要敲一长串参数。我自己的习惯是给不同的项目写一个小脚本export PGHOSTlocalhost export PGPORT5432 export PGUSERmyapp_user export PGDATABASEmyapp_db psql这种方式在频繁切换项目环境的时候特别高效前提是你清楚当前 shell 里这些变量指向哪里别在 A 项目的终端里连到 B 项目的数据库。3. 常用元命令psql 里以反斜杠开头的那些效率神器Psql 和普通 SQL 客户端最大的不同就是它内置了一套以反斜杠开头的元命令。这些命令不是 SQL 标准里的东西而是 psql 自己实现的快捷方式。用熟了以后你会发现大部分日常操作根本不用写 SQL 就能完成。3.1 查看数据库对象的几个高频命令\l列出所有数据库。这个命令比任何图形界面都快一眼看清库名、所有者、编码、权限。postgres# \l\dt列出当前模式下的所有表。注意这只会显示表视图、索引、序列不会出现在结果里。\d 表名是查看表结构的核心命令它会展示表的字段、类型、约束、索引、外键、触发器。这个命令在开发时使用频率极高写 SQL 之前先\d一下确认字段名和类型避免写完才发现列名写错了。postgres# \d users\dn查看模式列表。PostgreSQL 里有个模式的概念可以理解成数据库里的文件夹默认模式叫 public。\dt默认只看当前搜索路径里的模式这个细节经常让新手困惑明明建了表\dt却看不到因为表建在了别的模式里。\du查看所有用户和权限\df查看函数\dv查看视图\di查看索引。这一整套命令可以不用刻意记你只要记住\d后面加不同的字母就是查看不同类型对象需要的时候用\?查一下即可。3.2 \e、\timing、\x调试和格式化输出的三大法宝写复杂 SQL 的时候在 psql 提示符下一行一行敲特别痛苦。\e命令会调起系统默认编辑器比如 vim、nano你可以在编辑器里舒舒服服地写一段多行 SQL保存退出后 psql 自动执行并把结果显示出来。这个体验用过的都说好。\timing是我常年开着的开关。执行这个命令后psql 会在每条 SQL 执行完后额外显示一条执行耗时时间12.345 毫秒这在调优的时候特别有用。比如对比两个查询计划的耗时不需要额外装工具\timing开着就够了。冷知识它显示的是服务端执行时间加客户端传输时间不是纯粹的服务端耗时但用来做相对对比完全够用。\x是扩展显示模式的开关。默认情况下查询结果超过一定宽度psql 会自动换行字段多了看起来非常费劲。开了\x之后每条记录会纵向展示字段名和值一一对应排查数据问题时比网格视图清晰得多。我处理线上问题几乎全程开着\x只有需要核对大量行数据时才切回默认模式。3.3 把配置写进 .psqlrc每次启动自动加载host 连接参数、显示格式、提示符这些配置如果每次打开 psql 都要手动设置效率太低了。psql 支持启动时自动加载~/.psqlrcLinux/macOS或%APPDATA%\postgresql\psqlrc.confWindows这个配置文件。我自己的.psqlrc长这样\timing on \x auto \pset null NULL \set PROMPT1 %n%/%R%x# 一行行解释\timing on自动开启耗时显示\x auto表示结果宽度超限时自动切换扩展模式\pset null NULL把空值显示成显眼的 NULL避免把空字符串和 NULL 搞混\set PROMPT1自定义提示符%n是当前用户名%/是当前数据库名一眼就能看出自己连在哪里。这个文件配置好后就是长期收益每次打开 psql 不用重复输入那些设置值得花两分钟配置一次。4. 写 SQL 和事务在交互模式下的正确姿势psql 本质上是一个 SQL 执行环境理解它在交互模式下的行为逻辑能帮你避开很多莫名其妙的坑。4.1 分号是终结点不是回车大多数人第一次在 psql 里写 SQL 都会遇到一个困惑回车之后没有反应提示符变成了postgres-#注意是-而不是。这个行为的逻辑是psql 认为你的 SQL 还没有结束它还在等待输入。PostgreSQL 的语句终结符是分号只有看到分号才真正执行。所以你可以把一条长 SQL 拆成多行输入每行结尾不写分号最后一行写上分号回车写错了用\r清除当前输入缓冲重新开始不确定当前语句是否写完看提示符表示空闲-表示正在等待语句结束这在交互模式下是小问题但如果把 API 调用的方式也带进来就麻烦了写了不带分号的 SQL 传给服务端是不会有任何响应的。4.2 事务控制BEGIN、COMMIT、ROLLBACK 的实操PostgreSQL 默认每次执行一条语句都是一个独立事务这意味着如果你误删了一行数据直接就无法反悔了。但在 psql 里我们可以显式开启事务BEGIN; UPDATE accounts SET balance balance - 100 WHERE user_id 1; -- 检查结果确认无误 COMMIT;执行顺序是BEGIN开启事务更新语句执行后数据只是在事务里变更了其他连接看不到。此时如果发现不对劲执行ROLLBACK回滚一切恢复原样。确认没问题再COMMIT提交。我在生产环境执行任何批量更新、删除之前都强制自己走一遍这个流程。误操作在高并发环境下的代价非常大谨慎一点没有错。你可以在事务中执行查询验证受影响行数BEGIN; DELETE FROM orders WHERE created_at 2020-01-01; -- 查看删除的影响行数SELECT count(*) 先看总量 SELECT count(*) FROM orders WHERE created_at 2020-01-01; -- 注意此时 count 仍然能查到数据因为事务内删除对自身可见 COMMIT;这里的细节是在同一个事务里DELETE 之后的 SELECT 还是能看到被删的数据因为事务的隔离级别决定了你看到的是事务开始时的快照默认的 Read Committed 级别下事务内后续语句能看到之前语句的变更所以这里的说法要修正——实际上同一个事务内 DELETE 之后 SELECT 是看不到这些行的。这样说可能有点绕你只要记住在事务里可以做各种验证最后一把提交是最稳妥的做法。4.3 自动提交的开关与日常开发习惯psql 默认是自动提交模式每条语句执行完毕就提交。但显式执行了BEGIN之后后续语句会处于同一个事务里直到COMMIT或ROLLBACK。如果你想在 psql 启动时默认就不自动提交可以在.psqlrc里加\set AUTOCOMMIT off。不过我不建议新手这么做因为很容易忘记提交导致数据长期锁住其他连接全部卡住。关于锁的问题多说一句如果你在事务里执行了 UPDATE 但一直没有 COMMIT其他事务对同一行数据的更新就会阻塞。很多开发环境数据库突然很慢的问题查一下pg_stat_activity就能看到有事务开着没关。用 psql 查SELECT pid, state, query, xact_start FROM pg_stat_activity WHERE state active;发现长时间 active 的事务确认没有在跑大任务就可以考虑pg_terminate_backend(pid)结束它。5. 进阶玩法脚本化、变量和自动化如果只在交互模式下点来点去psql 的作用和其他客户端没有本质区别真正拉开差距的是脚本化能力。这节内容建议每个做后端开发和运维的人认真看两遍。5.1 用 -c 和 -f 直接执行摆脱交互模式psql -c SQL语句可以直接执行一条 SQL 并退出psql -U postgres -d mydb -c SELECT id, name FROM users LIMIT 10;这个命令可以用在 shell 脚本里、cron 定时任务里、CI 流水线里。多个 SQL 可以写在同一个-c参数里用分号分隔也可以写进一个.sql文件用-f参数指定执行psql -U postgres -d mydb -f /path/to/init.sql常用组合是-v传参数、-q安静模式、-t只输出结果行。比如用.sql文件模板做批量任务-- /path/to/report.sql SELECT id, name, created_at FROM users WHERE created_at :start_date;执行的时候用变量传入start_datepsql -U postgres -d mydb -v start_date2024-01-01 -f /path/to/report.sql-v是变量替换的关键:加变量名就会被替换成实际值。这是脚本化操作的正确姿势比直接在 shell 里拼接字符串安全得多。5.2 用 \gset 把查询结果变成变量psql 有一个比较冷门但非常强大的功能\gset。它可以把一条查询结果的字段赋值给 psql 变量然后用于后续 SQL 的构建。比如SELECT max(id) AS max_id FROM users; \gset SELECT * FROM users WHERE id :max_id;这个场景在做数据迁移、批量脚本时极其好用。比如要把上一批导入的数据的末尾 ID 作为下一批导入的开始位置\gset比手写硬编码变量灵活得多。还有个与之搭配的场景是动态生成复杂的权限语句。你可以先查出来所有需要授予权限的表然后用\gset存起来再动态拼接 GRANT 语句批量执行。5.3 \copy 与 COPY大批量数据导入导出的性能差异在 psql 里做数据导入导出有两个命令一定要分清COPY是服务端命令\copy是 psql 客户端命令。两者的核心区别是文件位置——COPY是在数据库服务器所在机器的文件系统上读写文件\copy是在你当前这台客户端机器的文件系统上读写。如果你连的是远程数据库想把本地的 CSV 导入远程库只能用\copy\copy users FROM /path/to/users.csv WITH (FORMAT csv, HEADER true);反过来想把查询结果导出成 CSV 拿走用\copy ... TO输出到本地\copy (SELECT id, name FROM users WHERE status active) TO /tmp/active_users.csv WITH (FORMAT csv, HEADER true);用 COPY 的注意点是权限——服务器端的文件路径必须是 PostgreSQL 操作系统用户有权限访问的。用\copy则没有这个限制因为它是在客户端这边发起的。性能上COPY因为不用经过客户端转发大批量数据时明显更快\copy适合中小数据量的日常操作。5.4 用 \watch 定时刷新的监控利器psql 里还有一个很多人不知道的兵器\watch。它可以每隔几秒自动重新执行上一条 SQLSELECT count(*), state FROM pg_stat_activity GROUP BY state; \watch 3这个命令的效果是每 3 秒刷新一次结果直到你按 CtrlC 退出。我在排查数据库并发问题、观察慢查询堆积、监控锁等待时经常用相当于一个简易的实时监控面板。再配合\x扩展显示和\pset格式设置直接在终端里看趋势变化连外部监控工具都不需要。6. 排查问题实录连接失败、乱码、Windows 服务启动这部分是我实际遇到的线上问题整理。很多细节在文档里不会写清楚但排障时关键就在这些细节里。6.1 端口被占用与连接超时psql: error: connection to server on socket /var/run/postgresql/.s.PGSQL.5432 failed: No such file or directory这种报错在 Linux 上特别常见含义是客户端找不到 PostgreSQL 的 socket 文件通常意味着服务端没启动或者 socket 目录被第三方软件改了。排查步骤确认服务是否启动systemctl status postgresql如果没启动启动服务并设置为开机自启systemctl start postgresql systemctl enable postgresql端口占用检查netstat -tlnp | grep 5432如果 5432 被其他程序占用需要改 PostgreSQL 的监听端口配置文件在postgresql.conf里的port参数改完重启服务。6.2 pg_hba.conf 认证方式引发的报错最常见的是把密码认证配成了trust完全不验证或者反过来配了md5但密码存储方式是scram-sha-256导致客户端认证方式跟服务端不匹配psql: error: connection to server at localhost (127.0.0.1), port 5432 failed: no pg_hba.conf entry for host 127.0.0.1, user postgres, database postgres, no encryption这个报错就是说pg_hba.conf 里没有匹配到当前连接请求的认证规则。解决办法是编辑pg_hba.conf加上对应规则的条目。文件位置在数据目录下Windows 安装版默认在C:\Program Files\PostgreSQL\版本\data\编辑后重启服务或执行SELECT pg_reload_conf();。一个通用建议pg_hba.conf 的规则从上往下匹配第一个匹配到的规则生效。所以如果你在后面写了一堆规则但前面的规则把请求拦掉了后面的等于白写。配置时把精确规则放前面宽松规则放后面。6.3 Windows 服务启动失败的排查实录postgresql windows 安装 服务启动是搜索热词里出现频率很高的一句话说明 Windows 上服务启动问题让不少人头疼过。我处理过一个典型场景安装完 PostgreSQL 16服务管理器里点启动报服务无法启动服务没有返回错误之类的提示。查 Windows 事件日志看到 PostgreSQL 的错误日志在数据目录的log文件夹里里面写着FATAL: could not write to file pg_xlog/xlogtemp.xxx: Permission denied这个问题的根源是数据目录的权限不对。PostgreSQL 服务以postgres系统用户运行但数据目录的拥有者是其他用户。解决办法是右键数据目录 → 属性 → 安全添加postgres用户并勾选完全控制。另一次是端口被别的进程占了错误日志里写FATAL: could not create listen socket for localhost HINT: Is another postmaster already running on port 5432?Windows 上netstat -ano | findstr 5432找到占用进程的 PID去任务管理器结束它或者改 postgresql.conf 里的端口号重启服务。6.4 中文乱码和编码问题psql 里查询结果中文显示成乱码多半是客户端编码和服务端不一致。检查双方的编码SHOW client_encoding; SHOW server_encoding;数据库通常应该是UTF8客户端连接时可以手动指定psql -U postgres -d mydb -E SET client_encoding UTF8或者用环境变量PGCLIENTENCODINGUTF8。还有一个经常踩的坑数据库本身创建时的编码就是错的比如建库时用了LATIN1后面存中文就会出问题。建库时一定要显式指定编码CREATE DATABASE mydb WITH ENCODING UTF8 LC_COLLATE zh_CN.UTF-8 LC_CTYPE zh_CN.UTF-8 TEMPLATE template0;注意要用template0作为模板否则会拷贝 template1 的编码和排序规则。乱码问题也可能是终端本身的编码设置造成的。Windows 默认的 cmd 是 GBK连上 UTF8 的数据库显示不乱才怪。换 Windows Terminal或者chcp 65001切到 UTF-8 代码页情况会好很多。6.5 连接数打满报错FATAL: sorry, too many clients already说明连接数被耗尽了。max_connections参数控制上限默认值是 100。连接数打满通常不是真的需要 100 个并发 SQL而是某个应用连接池配置错了把连接泄漏了。排查方法是查pg_stat_activity看哪些进程占着连接却闲着SELECT state, count(*) FROM pg_stat_activity GROUP BY state;如果idle状态的连接数量很多就是连接池没有正确释放连接。临时处理可以调大max_connections并重启但根治得改应用的连接池配置。另外一个思路是部署 PgBouncer 做连接池层但这涉及到额外组件不在本文范围内。7. 常见问题速查表从报错到解决一步到位前面每部分都讲了对应的排查思路这里汇总成一个速查表方便你遇到问题时快速定位。症状可能的报错信息排查方向连接超时connection timed out服务未启动、防火墙拦截、端口监听错误认证失败password authentication failed密码错误、pg_hba.conf 认证方式不匹配无匹配规则no pg_hba.conf entrypg_hba.conf 里缺少对应 host/user/database 规则找不到 socketNo such file or directory服务未启动、socket 目录配置错误端口占用address already in use5432 被其他进程占用改端口或杀进程连接数打满too many clients already连接池泄漏、max_connections 过小表不存在relation xxx does not exist模式搜索路径不对、表建在了别的模式里中文乱码显示为 ??? 或乱码client_encoding、数据库编码、终端代码页服务启动失败Permission denied数据目录权限、端口占用、配置文件语法错误这个表不可能覆盖所有场景但覆盖了我在日常开发和运维中遇到的大概率问题。遇到没见过的报错一个通用思路是先看错误最末尾的提示PostgreSQL 的报错信息其实写得很良心很多会直接给出 HINT 部分照着提示操作一般能解决。最后再说两句写到这里关于 psql 的日常使用我能想到的干货基本上都过了一遍。最后分享一个我自己的使用习惯凡是重复超过两次的操作我一定把它写成脚本凡是需要写进文档的命令我一定用-c或-f的方式在文档里给出完整可复制的示例。这样团队成员拿到文档直接复制就能用不用再猜测该在哪里加分号、该用哪个参数。如果你正在纠结要不要深入学习 psql我的建议是值得投入。PostgreSQL 的生态地位越来越重要而 psql 是绕不开的基础工具。花一两个小时把常用命令过一遍之后每天都在受益。遇到问题先别急着骂工具多半是你还没有完全理解它的设计逻辑。psql 的设计其实非常克制——它把该简单的地方做到极简把该灵活的地方给了足够的深度这恰恰是它能存活二十多年依然被广泛使用的原因。下载哪个版本这种问题真不用纠结太久装一个稳定版开始敲第一行psql -U postgres吧。