Home

深色模式

PostgreSQL 登录与 psql ​

概述 ​

装好 PostgreSQL 以后，第一件该熟悉的事不是建表，也不是调参数，而是先把登录和 psql 用顺。psql 看起来像个朴素的命令行工具，但它基本覆盖了日常排障、查看对象、执行脚本和临时跑 SQL 的大部分场景。

很多“数据库有问题”的反馈，最后都不是数据库真坏了，而是地址写错、用户不对、认证没过、连到了别的库，或者只是不会看当前连接信息。先把 psql 练熟，后面的文章会轻松不少。

登录方式 ​

用 TCP 连接本机 PostgreSQL ​

sh
psql -h 127.0.0.1 -p 5432 -U app_rw -d app

这也是图形客户端背后最常见的连接方式。参数含义很直接：

  • -h：主机地址
  • -p：端口
  • -U：用户名，也就是角色名
  • -d：数据库名

用 Unix socket 连接 ​

sh
psql -U postgres -d app

不写 -h 时，psql 会优先尝试本地 Unix socket。这种方式常见于数据库服务器本机，或者 docker compose exec postgres psql 进去以后。

用 URL 连接串登录 ​

sh
psql "postgresql://app_rw:app_rw_pass@127.0.0.1:5432/app?sslmode=disable"

连接串适合放进脚本、环境变量和临时排障命令里。应用程序、迁移工具、GUI 客户端，很多也都支持这种格式。

直接进入容器执行 psql ​

sh
docker compose exec postgres psql -U postgres -d app

宿主机没装客户端时，这种方式最省事。只要容器能起来，就能先进去确认数据库本身是不是正常。

启动参数 ​

日常高频的其实就几项：

  • -h：主机
  • -p：端口
  • -U：角色名
  • -d：数据库名
  • -W：强制提示输入密码
  • -c：执行一条命令后退出
  • -f：执行 SQL 文件后退出
  • -X：不加载本地 ~/.psqlrc

例如执行一条 SQL：

sh
psql -h 127.0.0.1 -U app_rw -d app -c "SELECT now();"

执行脚本文件：

sh
psql -h 127.0.0.1 -U app_rw -d app -f ./schema.sql

脚本执行时，比较稳的写法通常还会加上 ON_ERROR_STOP：

sh
psql -v ON_ERROR_STOP=1 -h 127.0.0.1 -U app_rw -d app -f ./schema.sql

这样脚本中间一旦报错，就会立即停下来，不会后面一串 SQL 继续往下跑。

环境变量 ​

如果一台机器上经常连同一个 PostgreSQL，可以先把连接参数放到环境变量里：

sh
export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=app_rw
export PGDATABASE=app

这样后面直接执行：

sh
psql

临时传密码时，也有人会这样写：

sh
PGPASSWORD=app_rw_pass psql -h 127.0.0.1 -U app_rw -d app

它适合临时排障，不适合长期放进公开脚本。需要长期本地免输密码时，更常见的做法是配置 ~/.pgpass。

常用命令 ​

先记住这组就够用了：

sql
\conninfo
\l
\c app
\dn
\dt
\d users
\du
\x auto
\timing on
\q

它们分别做这些事：

  • \conninfo：看当前连的是谁、哪个库、哪个地址
  • \l：列出所有数据库
  • \c app：切换到 app 数据库
  • \dn：查看 schema 列表
  • \dt：查看当前 schema 下的表
  • \d users：看 users 表结构
  • \du：查看角色列表
  • \x auto：结果过宽时自动切换纵向显示
  • \timing on：显示每条 SQL 的执行耗时
  • \q：退出

如果一时忘了命令，可以用：

sql
\?
\h SELECT

\? 看 psql 元命令帮助，\h SELECT 看某条 SQL 的语法帮助。

再往后更常用的还有这几个：

sql
\d+ users
\dt *.*
\di
\df
\pset pager off

它们适合这些场景：

  • \d+ users：看更详细的表结构、索引和存储信息
  • \dt *.*：把所有 schema 里的表都列出来
  • \di：看索引
  • \df：看函数
  • \pset pager off：输出太长时先关掉分页器

排障确认 ​

排障时，建议先跑下面几条：

sql
\conninfo
SELECT current_user, current_database();
SHOW search_path;
\dn
\dt

这组信息能很快回答几个常见问题：

  • 现在连的是不是想连的那台库
  • 当前角色是谁
  • 当前数据库是不是对的
  • 默认 schema 是什么
  • 目标表到底在不在当前 schema 里

很多人明明表存在，却总说 relation does not exist，最后发现只是连到了另一个库，或者 search_path 根本没指向那张表所在的 schema。

执行脚本 ​

交互式执行 ​

进到 psql 后，直接输入 SQL，以分号结尾：

sql
SELECT now();
SELECT * FROM users LIMIT 5;

用 -c 执行单条命令 ​

sh
psql -h 127.0.0.1 -U app_rw -d app -c "SELECT count(*) FROM users;"

这很适合脚本里做健康检查和一次性查询。

用 -f 执行脚本文件 ​

sh
psql -v ON_ERROR_STOP=1 -h 127.0.0.1 -U app_rw -d app -f ./init.sql

在 psql 里引入脚本 ​

sql
\i ./init.sql

这适合已经进了交互终端，想顺手跑一段本地 SQL 文件。

常见报错 ​

password authentication failed for user ​

通常就是用户名和密码对不上，或者连到了另一台数据库。先用 \conninfo 确认连接目标，再确认角色名和密码。

role "xxx" does not exist ​

说明实例里没有这个角色。不是密码错，是角色本身就不存在，或者连错实例了。

database "xxx" does not exist ​

角色没问题，但目标数据库没有建出来。先 \l 看一眼数据库列表。

connection refused ​

多半是服务没起来、端口不对，或者 listen_addresses 没放开。先查进程和监听，再去看认证文件。

no pg_hba.conf entry ​

数据库收到了连接，但 pg_hba.conf 没有允许这条来源地址、数据库和角色组合。这个报错很具体，别再回头猜密码。

could not translate host name ​

主机名写错了，或者 DNS 解析不到。Docker 容器里最常见的是把服务名写错，或者把 localhost 当成了别的容器。

FATAL: sorry, too many clients already ​

数据库连接数已经满了。先确认应用连接池是不是没有收好，再去看数据库的 max_connections，别第一反应就把连接数一路调大。

使用习惯 ​

比较实用的习惯通常是这样：

  1. 先用 psql 连接上数据库
  2. 先看 \conninfo
  3. 再看 \l、\du、\dt
  4. 确认对象存在后，再执行自己的 SQL
  5. 需要回放脚本时，用 -v ON_ERROR_STOP=1 -f

先确认环境，再下手改数据，比直接对着报错瞎猜要省时间得多。

参考 ​