PostgreSQL常用操作指令

PostgreSQL 的核心操作依赖 psql 元命令（以 \ 开头的快捷命令）和 标准 SQL 语句，二者需配合使用。元命令用于数据库管理（如查看对象结构、切换数据库），SQL 语句用于数据操作（如增删改查）。以下按使用频率整理关键指令，重点标注高频场景的必记命令和易错点。

---

一、连接与基础操作

1. 连接数据库

• 本地连接（默认用户 postgres）： 

  psql -U postgres -d postgres  # 连接默认数据库
  

• 远程连接： 

  psql -h 192.168.1.100 -p 5432 -U dbuser -d mydb
  

• 连接后关键操作： 
  • \c mydb：切换当前数据库（比 USE mydb 更高效）。 
  • \conninfo：查看当前连接信息（验证用户、数据库、IP）。 
  • \q：退出 psql。

重点：生产环境避免在命令中明文写密码，改用 .pgpass 文件管理凭证。

2. 核心元命令速查

场景	命令	说明
列出所有数据库	\l 或 \l+	\l+ 显示详细大小和权限（必记）。
列出当前库所有表	\dt 或 \dt+	\dt+ 显示表大小和注释（必记）。
查看表结构	\d 表名	显示列、约束、索引；\d+ 表名 额外显示注释和存储参数（必记）。
查看索引	\di	列出当前库所有索引；\di 表名* 过滤指定表的索引。
查看用户/角色	\du	显示所有用户及其权限（必记）。

关键规则：元命令无需分号结尾，且不区分大小写（如 \DT 等效于 \dt）。

---

二、数据操作与维护

1. 高频 SQL 操作

（1）数据查询与诊断

• 查看当前连接： 

  SELECT pid, usename, application_name, client_addr, state, query 
  FROM pg_stat_activity 
  WHERE state != 'idle';
  

• 诊断慢查询（需开启 pg_stat_statements）： 

  SELECT query, calls, total_time, mean_time 
  FROM pg_stat_statements 
  ORDER BY mean_time DESC 
  LIMIT 10;
  

• 终止阻塞查询： 

  SELECT pg_terminate_backend(pid);  -- **强制终止连接**（慎用）
  

（2）表与索引维护

• 重建索引（解决索引膨胀）： 

  REINDEX TABLE 表名;  -- 重建单表所有索引（**高频维护操作**）
  

• 更新统计信息（优化执行计划）： 

  ANALYZE 表名;  -- **执行VACUUM后必须运行**，否则查询性能下降
  

重点： 

• VACUUM FULL 会锁表，生产环境改用 VACUUM ANALYZE 表名（推荐日常维护命令）。 
• 索引失效主因：查询时使用的距离运算符（如 <=>）与索引类型（如 vector_cosine_ops）不匹配，需用 EXPLAIN 验证。

2. 权限与用户管理

• 创建用户并授权： 

  CREATE USER appuser WITH PASSWORD 'secure_password';
  GRANT CONNECT ON DATABASE mydb TO appuser;
  GRANT SELECT ON TABLE public.users TO appuser;  -- **按需授权**
  

• 修改用户密码： 

  ALTER USER appuser WITH PASSWORD 'new_password';
  

安全规范： 

• 禁止直接授权 SUPERUSER 给应用用户。 
• 远程访问时，pg_hba.conf 中必须限制 IP 范围（避免 0.0.0.0/0）。

---

三、备份与恢复

1. 逻辑备份（推荐日常使用）

• 单库备份： 

  pg_dump -U postgres -Fc mydb > mydb_$(date +%Y%m%d).dump
  

  • -Fc：生成自定义格式压缩文件（支持并行恢复，必用）。
• 单库恢复： 

  pg_restore -U postgres -d mydb mydb_20240521.dump
  

2. 关键注意事项

• 恢复前必须创建空库： 

  createdb -T template0 -O owner mydb  # 避免模板库污染
  

• 验证备份有效性： 

  pg_restore -l mydb_20240521.dump  # 检查文件内容
  

重点： 

• 不要用 pg_dumpall 备份生产库（会包含角色和全局设置，恢复易出错）。 
• 备份文件必须定期验证，否则灾难时可能无法恢复。

---

四、性能优化关键命令

1. 索引与查询调优

• 查找未使用索引（降低维护成本）： 

  SELECT schemaname, tablename, indexname 
  FROM pg_stat_user_indexes 
  WHERE idx_scan = 0;
  

• 查看表大小（定位大表）： 

  SELECT pg_size_pretty(pg_total_relation_size('表名'));
  

2. 执行计划分析

• 强制使用索引验证： 

  SET enable_seqscan = off;  -- 临时禁用顺序扫描
  EXPLAIN ANALYZE SELECT * FROM large_table WHERE indexed_col = 1;
  

• 重置查询计时： 

  \timing on  -- **每次优化必开**，精确测量耗时
  

关键规则： 

• 执行 EXPLAIN ANALYZE 前必须开 \timing，否则无法验证实际优化效果。 
• 若计划中出现 Seq Scan（顺序扫描），90% 概率是索引未生效。

---

五、高频避坑指南

1. 元命令与 SQL 混淆： 
   • \dt 是元命令（无分号），SELECT * FROM pg_tables; 是 SQL（需分号）。
2. 远程连接失败： 
   • 检查 postgresql.conf 中 listen_addresses = '*' 和 pg_hba.conf 的 IP 限制规则。
3. 索引未生效： 
   • 确认查询中的运算符（如 ORDER BY embedding <=> query）与索引类型（vector_cosine_ops）严格匹配。

---

核心原则： 

• 元命令用于管理（\l, \dt, \d），SQL 用于数据操作（SELECT, INSERT）。 
• 所有维护操作（VACUUM/ANALYZE/REINDEX）必须配合 \timing 验证效果。 
• 生产环境操作前，先用 EXPLAIN 检查执行计划，避免全表扫描。

熟练掌握上述 10 个高频命令（\l+, \dt+, \d+, ANALYZE, REINDEX, pg_dump -Fc, pg_restore, EXPLAIN ANALYZE, pg_stat_activity, pg_size_pretty），可覆盖 90% 日常运维场景。