psql 速查清单（Postgres）

连接

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

环境变量：

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

基础 meta 命令

\?          -- 帮助
\l          -- 列出数据库
\c mydb     -- 切换数据库
\dt         -- 列出表
\dv         -- 列出视图
\df         -- 列出函数
\dn         -- schema
\du         -- 用户/角色
\x          -- 扩展显示（长记录很有用）
\timing     -- 显示执行耗时

查看表结构

\d my_table
\d+ my_table

常用查询：大小/索引/膨胀

数据库/表大小

-- 数据库大小
select pg_size_pretty(pg_database_size(current_database()));

-- 表大小（含索引）
select
  relname as table,
  pg_size_pretty(pg_total_relation_size(relid)) as total,
  pg_size_pretty(pg_relation_size(relid)) as data,
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as index
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc
limit 20;

索引使用情况（粗看）

select
  relname as table,
  idx_scan as index_scans,
  seq_scan as seq_scans
from pg_stat_user_tables
order by seq_scan desc
limit 20;

EXPLAIN / 性能排查

explain select * from my_table where id = 1;
explain analyze select * from my_table where id = 1;
explain (analyze, buffers) select * from my_table where id = 1;

经验：

• analyze 会真实执行（线上慎用）
• buffers 能看 IO 压力

导入/导出

导出查询结果为 CSV（本机）

\copy (select * from my_table limit 100) to './out.csv' csv header

从 CSV 导入

\copy my_table(col1, col2) from './in.csv' csv header

会话/锁排查

当前连接

select pid, usename, state, wait_event_type, wait_event, query
from pg_stat_activity
where datname = current_database()
order by state, pid;

锁

select
  a.pid,
  a.state,
  a.query,
  l.locktype,
  l.mode,
  l.granted
from pg_locks l
join pg_stat_activity a on a.pid = l.pid
where a.datname = current_database()
order by l.granted, a.pid;