psql 连接 PostgreSQL 与 SSL 配置

正确使用连接 URI、环境变量、pgpass、超时和 TLS 验证连接 PostgreSQL

明确连接五要素

psql -X \
  --host=db.example.com \
  --port=5432 \
  --username=app_reader \
  --dbname=commerce

目标由 host、port、database、user 和 TLS 参数共同决定。不要只看数据库名；同名 database 可以存在于多个实例。

连接 URI 等价写法：

psql -X "postgresql://app_reader@db.example.com:5432/commerce?sslmode=verify-full"

不要把密码写入命令行 URI、源代码或日志。交互使用提示，自动化使用 secret manager、短期凭据、.pgpass 或 libpq service file。

pgpass

Unix 默认文件是 ~/.pgpass，权限必须限制为 0600：

hostname:5432:database:username:password

chmod 600 ~/.pgpass

Windows 默认位置是 %APPDATA%\postgresql\pgpass.conf。通配符会扩大凭据适用范围，应尽量写具体 host、database 和 user。

连接 service 文件

libpq 的 service 文件可以把 host、user 和 TLS 参数从命令行与 shell history 中移出。默认路径是 ~/.pg_service.conf，可用 PGSERVICEFILE 覆盖：

[prod]
host=db.example.com
port=5432
user=app_reader
dbname=commerce
sslmode=verify-full

psql -X service=prod

应用程序也可以通过 PGSERVICE=prod 引用同一条配置。密码仍放在 .pgpass 或 secret manager 中，不要写进 service 文件。

SSL 模式

`sslmode`	行为	使用建议
`disable`	不使用 TLS	仅受控本机/隔离测试
`require`	要求加密，但不完整验证身份	比明文好，不足以抵抗错误端点
`verify-ca`	验证证书链	仍不验证主机名
`verify-full`	验证证书链和主机名	远程生产连接的推荐目标

verify-full 要求 URI 中的 host 与证书身份匹配，并正确配置根证书。云平台可能有自己的 CA 轮换流程，不能永久固定一份过期证书。

连接后立即确认

\conninfo
SELECT
  current_database(), current_user, session_user,
  inet_server_addr(), inet_server_port(),
  current_setting('server_version') AS server_version,
  current_setting('TimeZone') AS timezone;

脚本建议使用：

psql -X --set ON_ERROR_STOP=on --file migration.sql "$DATABASE_URL"

-X 避免用户 .psqlrc 改变自动化行为；ON_ERROR_STOP 让脚本在 SQL 错误时退出。psql 退出码语义见 PostgreSQL 18 psql 文档。

面向脚本的输出格式

交互式输出默认是表格对齐；管道处理需要非对齐、仅元组的输出：

psql -X -A -F, -t -c "SELECT id, email FROM users" > users.csv

-A 关闭对齐，-F, 指定字段分隔符，-t 只输出数据行。会话内的等价控制是 \pset format csv、\pset null '[NULL]'，以及 \o /tmp/out.txt（再次执行 \o 回到标准输出）。

实用 one-liner

按总大小列出最大的表：

psql -X -c "
SELECT schemaname||'.'||relname AS table_name,
       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;"

当前正在执行的查询，按开始时间排序：

psql -X -c "
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start
LIMIT 20;"

确认 PID 后终止失控的后端进程：

psql -X -c "SELECT pg_terminate_backend(12345);"

交互式 ~/.psqlrc

~/.psqlrc 在每次交互式启动时执行；-X 会跳过它，因此不影响自动化。一个最小模板：

\timing on
\pset null '[NULL]'
\x auto
\pset linestyle unicode
\pset border 2

\set conns 'SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE state <> ''idle'';'
\set locks 'SELECT pid, mode, locktype, relation::regclass, granted FROM pg_locks WHERE NOT granted;'

\set HISTFILE ~/.psql_history- :DBNAME
\set HISTCONTROL ignoredups

之后 :conns 和 :locks 会展开为存储的查询；按库分离的 history 文件避免不同数据库的命令互相污染。

连接超时与查询超时不同

连接参数控制建立会话要等多久；statement_timeout 控制 SQL 执行；lock_timeout 只控制等待锁。应用层还要设置请求截止时间，并确保超时后取消或释放数据库连接。

连接失败时记录完整错误和 SQLSTATE，再查看错误速查，不要通过关闭 TLS 或扩大权限来试错。

On this page