企业官网建设流程全解析

1. 为什么我劝你千万别跳过 psql

很多刚接触 PostgreSQL 的人，装完数据库第一件事就是打开 pgAdmin、Navicat 或者 DataGrip，点几下鼠标建个表、跑个查询，觉得这样才算“会用数据库”。我自己早期也这么干，直到有一次在无桌面环境的 Linux 服务器上排查慢查询，才发现离开 psql 我几乎寸步难行。从那时起，我下定决心把 psql 练熟，后来工作中几乎所有 PostgreSQL 运维和调优操作都在这个黑乎乎的终端里完成。

psql 是 PostgreSQL 自带的交互式命令行工具，它不只是“能执行 SQL”这么简单。它内置了元命令（以反斜杠开头的命令）、变量替换、事务控制、脚本执行、结果格式化、自动补全、历史记录、快捷键等一系列能力。只要你的机器上有 PostgreSQL 客户端（甚至不一定装服务端，单独装个 psql 就行），就能连到远端数据库，做完大部分管理和查询工作。

这篇文章适合谁？如果你是刚接触 PostgreSQL 的新手，可以把它当成 psql 的入门实操手册；如果你已经用图形工具很久，想转到命令行提高效率，同样建议细看快捷键和元命令部分。我尽量把每个操作背后“为什么要这么用”也讲清楚，而不是单纯罗列命令。

2. 连接数据库与基础元命令，先把地基打牢

2.1 连接参数的几种姿势

psql 最基础的用法是：

psql -h 127.0.0.1 -p 5432 -U postgres -d mydb

分别对应主机、端口、用户、数据库。如果在本机且使用默认端口，可以直接：

psql -U postgres mydb

也可以偷懒用环境变量，比如：

export PGHOST=192.168.1.10 export PGPORT=5432 export PGUSER=postgres export PGDATABASE=mydb psql

这样 psql 会自动读取这些变量。我习惯在 ~/.bashrc 里配一套默认变量，日常连接省掉一大串参数。注意密码默认不写在命令行里，否则容易被 history 泄露；可以用 .pgpass 文件或 PGPASSWORD 环境变量，但后者同样有安全风险，生产环境我更推荐 .pgpass。

连接成功后，你会看到类似这样的提示符：

mydb=#

超级用户是#，普通用户是>。想确认当前连接信息，执行：

SELECT version();

或者用元命令\conninfo查看连接详情，它会告诉你当前数据库、用户、端口、连接协议等。

2.2 最常用的几个元命令

psql 的元命令以反斜杠开头，不需要分号结尾。刚入门建议先记住这几个：

\d是个很值得深挖的命令。不带参数时，它列出所有“可显示的”关系；带表名时，展示的信息比很多图形工具还详细。比如查看一个分区表，\d parent_table会列出分区键、分区子表列表；查看一个索引，\d index_name能看到它的定义语句和所属表。我排查锁等待或表结构变更时，基本全靠\d。

\x是我特别想强调的元命令。默认查询结果以列对齐方式显示，如果字段特别多，或者某个字段是 JSON、文本超长，结果会挤到没法看。执行\x之后，每条记录转成“字段名：值”的纵向展示，阅读体验好太多。也可以临时用\x on或\x off单独控制某一次查询。

2.3 查看帮助的万能钥匙

psql 自带的帮助体系做得相当完善：

• \?列出所有元命令的简短说明；
• \h列出所有 SQL 命令关键字，\h SELECT查看具体语法；
• \g执行上一条命令（或者直接分号，二者等价）。

我经常用\h查某个 SQL 子句的准确语法，比如\h CREATE TABLE、\h COPY。它输出的语法比网上很多二手教程严谨得多，而且是本机版本匹配的语法。这个习惯能让你少踩很多坑，毕竟 PostgreSQL 10 和 16 的某些语法已经不一样了。

提示：\h显示的语法中，方括号表示可选，花括号表示必选其一，竖线表示“或”。学会读这个语法图，比死记硬背命令管用得多。

3. 常用 SQL 操作的进阶技巧与结果格式化

3.1 分段执行与事务控制

psql 默认在每条带分号的 SQL 后自动提交事务。如果你想手动控制事务，最直接的方式是：

BEGIN; UPDATE account SET balance = balance - 100 WHERE id = 1; UPDATE account SET balance = balance + 100 WHERE id = 2; COMMIT;

如果中间某一步出错，且你希望回滚，不要直接输ROLLBACK，可以先观察当前事务状态。有个小技巧：如果忘了是否在事务中，可以查看提示符，事务中提示符会变成mydb=*#，多了一个*号。这个细节在写复杂脚本时很救命。

另外，psql 里可以用\set AUTOCOMMIT off关闭自动提交，之后每条 SQL 都需要你手动COMMIT才会生效。切换回自动提交用\set AUTOCOMMIT on。不过我个人不太建议日常关闭自动提交，容易忘记提交导致锁表；只有做批量数据修复时才会临时关掉。

3.2 查询结果的格式化控制

默认的查询结果格式叫“aligned”，字段名和值用竖线分隔。实际场景中，我们经常需要把结果导出成 CSV 或对齐格式，这里有几个实用设置：

\pset format csv SELECT * FROM users LIMIT 10;

执行后结果直接以 CSV 形式输出，不带表头时用：

\pset format unaligned \pset tuples_only on

tuples_only设置为 on 后，只显示数据行，不显示列名。这个组合常用于脚本处理。如果你想恢复默认：

\pset format aligned \pset tuples_only off

我遇到过不少同事喜欢把查询结果粘贴到 Excel 里处理，但默认格式粘贴过去会错位。其实 psql 自带\copy命令，可以直接把查询结果导出为 CSV：

\copy (SELECT id, name, created_at FROM users) TO '/tmp/users.csv' WITH CSV HEADER

注意\copy是 psql 客户端本地读写文件，权限和路径都以你本地机器为准；而COPY是服务端读写文件，文件必须在数据库服务器上且对 postgres 用户可写。新手经常搞混这两个命令，报“权限不足”或者“文件不存在”时，先确认自己用的是哪个。

3.3 用 \gset 把查询结果变成变量

psql 支持变量替换，最经典的用法是：

SELECT count(*) AS cnt FROM users; \gset SELECT :cnt;

\gset会把查询结果的每一列赋值给同名变量，之后用:cnt引用。这个能力在写自动化脚本时非常有用。比如你想在脚本里获取当前序列值再插入：

SELECT nextval('order_seq') AS new_id; \gset INSERT INTO orders (id, detail) VALUES (:new_id, 'test');

还可以用\if、\elif、\else做条件判断，结合\gset实现简单的流程控制。虽然 psql 不是编程语言，但做轻量自动化足够了。

4. 快捷键与命令行编辑技巧，效率翻倍的关键

4.1 行编辑与历史记录

psql 底层依赖 readline 库，所以它支持大部分 bash 风格的快捷键。我实际高频使用的主要这几个：

Tab补全是我强烈推荐新手尽早习惯的功能。psql 能补全的关键字范围很广：输入sel再按 Tab，会补全为SELECT；输入表名前几个字母再 Tab，会列出匹配的表名；输入\d后按 Tab 也能补全关系名。有了这个功能，很长的表名根本不用手敲，既快又不容易写错。

历史记录默认存在~/.psql_history文件。如果你不希望某个敏感 SQL 被记录，可以在输入之前加一个空格，前提是设置变量HISTCONTROL=ignorespace。不过这个变量是 psql 的\set还是环境变量？实际是 psql 的\set HISTCONTROL ignorespace，执行后以空格开头的命令不会进历史。但说实话我很少用，更建议敏感操作在事务中执行并谨慎确认。

4.2 多行编辑与重新编辑

psql 里 SQL 可以跨多行写，直到分号才执行。如果你写了一行发现想改上面某行，最简单的办法是直接按 Ctrl+C 取消当前输入，回到提示符重新开始。但如果你已经连续写了好几行，Ctrl+C 会全部清掉。此时按\e（或\ev、\ef）可以打开系统默认编辑器（通常是 vi 或 nano）来编辑当前的查询缓冲区。

举个例子：

SELECT * FROM users WHERE created_at > '2024-01-01';

在分号前按\e，就会用编辑器打开这段 SQL，改完后保存退出，psql 会把内容重新送进缓冲区让你继续执行。\ef可以直接编辑函数定义，\ev编辑视图定义，这两个命令对快速修改数据库对象非常方便。

4.3 修改默认编辑器

\e调用的是EDITOR环境变量指定的编辑器。如果你更喜欢 vim：

export EDITOR=vim psql

或者在 psql 内部执行：

\setenv EDITOR vim

同样，可以为VISUAL设置，优先级不同。建议统一设置EDITOR和VISUAL。我在服务器上更喜欢用 vim 做复杂编辑，因为 vim 有语法高亮、列编辑模式，改多行 SQL 比在终端直接敲舒服得多。

5. 脚本化执行与自动化运维实战

5.1 通过 -f 和 -c 执行外部脚本

psql 最常见的脚本执行方式是：

psql -h host -U user -d db -f /path/to/script.sql

脚本里可以包含 SQL 和元命令混合内容。比如一个初始化脚本init.sql：

\set ON_ERROR_STOP on BEGIN; CREATE TABLE IF NOT EXISTS app_user ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT now() ); COMMIT;

加上ON_ERROR_STOP后，一旦脚本中某条语句报错，psql 立即停止后续执行；不加这个设置，默认会报错后继续跑下一条，在批量执行时非常危险。我建议在所有生产环境变更脚本里都写上\set ON_ERROR_STOP on，并且配合事务使用。

单条命令直接执行可以用：

psql -d mydb -c "TRUNCATE TABLE temp_log;"

-c后面跟分号也可以。区别在于-c执行完立即退出，而-f执行完也退出。想连进去再继续操作，就用普通的交互模式。

5.2 在 shell 脚本中安全调用 psql

写 Shell 脚本时，我习惯把所有数据库参数放到一个配置文件里，然后用变量传递。举一个实际备份归档的例子：

#!/bin/bash DB_HOST="192.168.1.20" DB_PORT="5432" DB_USER="ops" DB_NAME="appdb" export PGPASSWORD='S3curePass!' psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" <<'EOF' \set ON_ERROR_STOP on \pset tuples_only on SELECT 'backup_start', now(); -- 这里执行需要归档的数据操作 EOF

这里用 heredoc 传 SQL，注意结束符EOF加了单引号，避免 shell 对$、反引号做变量替换。如果 SQL 里需要用到 psql 的变量，就去掉单引号并小心转义。关于密码，PGPASSWORD写在脚本里有泄露风险，更好的做法是使用.pgpass文件并设置权限为 600。

5.3 使用变量与回调处理动态脚本

psql 支持-v参数向脚本传递变量：

psql -d mydb -v start_date='2024-06-01' -v end_date='2024-06-30' -f report.sql

脚本report.sql中可以用:'start_date'引用变量并自动添加单引号：

SELECT * FROM orders WHERE created_at BETWEEN :'start_date' AND :'end_date';

这里注意，:'start_date'是带引号的安全插入方式；如果直接写:start_date，值会按字面插入，可能在 SQL 注入或类型错误。变量还能配合条件判断做动态分支：

\if :is_debug SELECT 'debug mode'; \else SELECT 'release mode'; \endif

这个能力在部署脚本里很实用：同一套脚本，测试环境传is_debug=true，生产传false。

6. 提升效率的高级配置与个性化设置

6.1 通过 .psqlrc 定制启动环境

每次进入 psql 都会自动执行~/.psqlrc文件（存在则加载）。我个人的.psqlrc长这样：

\pset null 'NULL' \pset pager on \x auto \set HISTSIZE 10000 \set COMPKEYWORD_STATUS 1

逐行解释一下：

• \pset null 'NULL'：将数据库中的 NULL 显示为字符串NULL，否则默认显示为空白，容易和空字符串混淆；
• \pset pager on：查询结果超过一屏时自动用分页器（less）显示。但我不太喜欢它频繁翻页，有时直接\pset pager off关掉，容忍结果滚动；
• \x auto：仅当结果列太宽时才自动切换扩展显示，这是我在 psql 16 里最喜欢的设置；
• \set HISTSIZE 10000：历史命令保留 10000 条，默认只有 500 条；
• \set COMPKEYWORD_STATUS 1：启用关键字大小写自动补全行为。

注意，如果想临时跳过.psqlrc，启动时加-X参数：

psql -X -d mydb

这在排查某些用户环境问题时很有用，避免个人配置干扰判断。

6.2 使用 \timing 观察执行耗时

\timing是另一把好用的尺子。开启后，每条 SQL 执行完会自动显示耗时：

mydb=# \timing on Timing is on. mydb=# SELECT count(*) FROM big_table; count ------- 1000000 (1 row) Time: 235.812 ms

这个信息对于定位慢查询非常直接。配合EXPLAIN ANALYZE使用的话：

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM big_table WHERE id > 500000;

输出的执行计划里有实际执行时间、扫描行数、缓冲命中情况。我一般先开\timing记录整体时间，再做EXPLAIN ANALYZE看细节，两者互补。

6.3 自定义提示符与颜色

提示符可以显示当前数据库、用户名、主机、甚至事务状态。默认提示符%/%R%#已经包含数据库名和事务标志。想自定义的话：

\set PROMPT1 '%n@%M:%> %`echo -n "\$?"` %/%R%# '

这个会把提示符显示为user@host:port dbname=#。不过我更推荐一种极简配置：

\set PROMPT1 '%/%R%# '

如果你有多个环境，比如开发库、测试库、生产库，可以把数据库名或连接信息用颜色区分。psql 16 支持在提示符中使用颜色转义序列，例如：

\set PROMPT1 '%[%033[1;31m%]%/%R%#%[%033[0m%] '

红色的提示符能直观提醒你“当前在关键环境”，降低误操作风险。不过这个颜色方案在部分终端下可能不生效，建议先在实际终端里测试。

7. 常见问题排查与避坑实录

7.1 报错 “FATAL: database does not exist”

很多新手连接时直接psql -U postgres，如果没指定-d，默认会尝试连接与用户名同名的库。比如你的系统用户是root，psql会尝试连一个叫root的数据库，然后报错：

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "root" does not exist

解决办法：明确指定数据库-d postgres，或者在环境变量里设置PGDATABASE=postgres。出现这种报错时先检查自己当前是什么用户、要连哪个库，别急着怀疑服务没启动。

7.2 口令认证失败与 .pgpass 配置

password authentication failed for user "xx"是最常见的认证报错之一。除了密码真的输错，还有可能是 pg_hba.conf 里规定了认证方式不是 md5/scram。排查顺序：

1. 确认用户名和密码；
2. 查看连接是否走到预期的 pg_hba 规则（可以用SHOW hba_file;找到文件位置）；
3. 尝试psql -h 127.0.0.1和psql -h /var/run/postgresql分别连，确认 socket 和 TCP 是否都允许。

日常自动化中我建议配置.pgpass：

# ~/.pgpass localhost:5432:mydb:username:supersecret

然后设置权限：

chmod 600 ~/.pgpass

注意.pgpass文件每行格式是hostname:port:database:user:password，其中数据库也可以用*匹配所有库。这个文件比PGPASSWORD安全一截，因为不会出现在进程环境变量里。

7.3 psql 卡住不动，可能是等待锁

执行SELECT * FROM some_table一直不返回，大概率是表被其他事务锁住了。这时候不要直接 Ctrl+C 了事，应该另开一个 psql 查锁和阻塞：

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state IS NOT NULL;

看到wait_event_type为Lock时，基本可以判断在等锁。再进一步查阻塞链：

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query FROM pg_stat_activity blocked JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

这个查询很实用，能直接揪出谁是“拦路虎”。碰上长时间持锁的事务，和业务方确认后可以用pg_terminate_backend(pid)终止。

7.4 乱码与编码问题

遇到中文乱码时，先检查客户端编码：

SHOW client_encoding;

正常应该是UTF8。如果显示SQL_ASCII或其他，执行：

SET client_encoding TO 'UTF8';

也可以启动时加环境变量PGCLIENTENCODING=UTF8。另外文件导入时，注意文件本身的编码要和数据库字符集匹配，通常建议用 UTF-8 无 BOM 格式。

8. 从交互到自动化，构建自己的 psql 工作流

8.1 与常见运维场景结合

psql 的价值远不止敲几条 SQL。举几个我实际长期使用的场景：

• 定期清理归档：结合 crontab 和-f脚本，每周清理过期分区表；
• 监控连接数：一条 SQL 输出当前连接池用量，直接管道给监控脚本；
• 生成建表语句：用pg_dump --schema-only配合 psql 还原，校验两个环境的表结构差异；
• 批量导出数据：\copy导出 CSV 后，再通过 Python pandas 处理或直接加载到数仓。

下面是一个简单的 shell 函数，方便快速连到测试库执行任意 SQL：

dbq() { local db="$1" local sql="$2" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$db" -tAX -c "$sql" }

调用方式：

dbq app "select count(*) from users;"

-tAX三个参数含义：-t只输出行数据，-A关闭对齐（无空格填充），-X跳过 .psqlrc。组合起来输出就是纯文本，方便继续用 grep、awk 处理。

8.2 复杂脚本的调试方法

写长 SQL 脚本时，建议先打开\set ECHO all，效果是 psql 会回显每条执行的 SQL，方便对照日志排查。配合\set VERBOSITY verbose，报错信息会更完整，包含错误位置和提示。

如果脚本里有循环或条件逻辑，可以用\timing和\echo打印过程信息：

\echo '=== Start cleanup at :current_time ===' -- 业务逻辑 \echo '=== Cleanup finished ==='

\echo类似 shell 的 echo，但可以用 psql 变量。这样执行时能看到进度，不至于面对黑屏到底跑了多少状态一无所知。

8.3 在 Windows 和远程环境下的注意点

Windows 上使用 psql 时，默认终端可能是 cmd 或 PowerShell，readline 快捷键不完全支持。建议在 Windows 上安装 Git Bash 或 WSL，这样能拿到和 Linux 一致的补全和历史体验。另外，Windows 的换行符是 CRLF，如果编写的 SQL 脚本是从 Windows 传到 Linux 服务器上执行，偶尔会遇到\r导致的语法错误，可以用sed -i 's/\r$//' script.sql清理一下。

如果是通过 SSH 远程连接，注意长期空闲的连接可能被服务器断开。可以在 psql 里执行一些轻量查询保持活跃，或者配置 SSH 的ServerAliveInterval。

9. 一些我踩过坑之后才明白的小技巧

最后想分享几个不太常见但很实用的细节，这些是我用了多年 psql 之后才慢慢积累下来的。

第一个是\watch命令。在 SQL 语句后面输入\watch 2，psql 会每 2 秒重复执行一次同一个查询，直到你按 Ctrl+C。我在排查锁等待、观察 count 变化、盯某个指标是否回暖时经常用它，比反复按上箭头回车舒服太多。

第二个是\sf和\df配合查看函数定义。\df+ function_name能看到函数的源码位置、参数、返回类型、安全属性等；\sf function_name直接输出函数完整定义，可以直接复制到编辑器里改。这两个命令在别人维护的库里接手时极其好用。

第三个是关于NULL的陷阱。在 psql 里执行SELECT NULL;默认显示一个空字符串，如果不仔细看会误以为结果为空。配置\pset null '[NULL]'后，所有 NULL 都会显示成[NULL]，一眼就能分辨。这个配置我强烈建议写进.psqlrc。

第四个是善用\gexec。比如你想对所有以2023_开头的分区表执行同一个操作，可以先生成动态 SQL，然后交给\gexec执行：

SELECT format('VACUUM (ANALYZE) %I;', tablename) FROM pg_tables WHERE tablename LIKE '2023\_%' \gexec

它会逐条执行生成的每条 SQL，省去手写循环的麻烦。

我个人的体会是，psql 这门手艺不值得刻意去背命令清单，最有效的学习方式是把它当成日常操作的主要入口，遇到问题先想“psql 能不能做到”，再去查\?和\h。用一两个星期后，你会发现自己已经离不开它的补全、历史、变量和脚本能力。等哪天真要在大促凌晨处理一个线上紧急变更，能让你稳住阵脚的，往往不是图形界面里那个鼠标，而是这个朴素命令行里飞快跑过的几行绿色输出。