PostgreSQL 必备 100 个小技巧（详细整理总结）

前言

本文整理 100 条 PostgreSQL 日常使用频率较高的技巧与命令，覆盖实例信息、连接会话、事务与锁、SQL 性能、表与索引、Vacuum 与统计信息、WAL 与检查点、主从复制、备份恢复、权限管理等核心场景。主要面向 PostgreSQL 14–18 版本，不同版本的统计视图字段可能存在差异，执行前建议先确认数据库版本。

一、实例与基础信息（1–10）

1. 查看 PostgreSQL 版本

SELECT version();
SHOW server_version;

2. 脚本中获取版本号

SELECT current_setting('server_version');

比解析 version() 的返回文本更方便。

3. 查看当前数据库

SELECT current_database();

4. 查看当前用户

SELECT current_user;
SELECT current_user, session_user;  -- session_user 为原始连接用户

session_user 表示最初建立连接的用户，current_user 可能因 SET ROLE 发生变化。

5. 查看服务器地址和端口

SELECT inet_server_addr(), inet_server_port();

在 VIP、负载均衡、读写分离环境中非常实用。

6. 查看客户端地址和端口

SELECT inet_client_addr(), inet_client_port();

7. 查看数据库启动时间与运行时长

SELECT pg_postmaster_start_time();
SELECT now() - pg_postmaster_start_time() AS uptime;

8. 查看当前时间和时区

SELECT now(), current_timestamp, current_setting('TimeZone');

9. 查看数据目录

SHOW data_directory;
SELECT current_setting('data_directory');

10. 查看配置文件路径

SHOW config_file;
SELECT current_setting('config_file') AS config_file,
       current_setting('hba_file') AS hba_file,
       current_setting('ident_file') AS ident_file;

分别对应 postgresql.conf、pg_hba.conf、pg_ident.conf。

二、数据库、模式与对象（11–20）

11. 查看所有数据库

\l                    -- psql 中
SELECT datname, datdba::regrole AS owner FROM pg_database ORDER BY datname;

12. 查看当前数据库大小

SELECT pg_size_pretty(pg_database_size(current_database()));

13. 查看所有数据库大小（按大小排序）

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS database_size
FROM pg_database WHERE datallowconn
ORDER BY pg_database_size(datname) DESC;

14. 查看当前数据库中的模式

\dn                    -- psql 中
SELECT schema_name, schema_owner FROM information_schema.schemata ORDER BY schema_name;

15. 查看当前搜索路径

SHOW search_path;

search_path 影响未指定 Schema 的对象解析顺序。

16. 查看指定模式中的表

\dt public.*          -- psql 中
SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

17. 查看表结构

\d public.table_name
\d+ public.table_name   -- 显示字段、索引、约束、表大小等详细信息

18. 查看视图与物化视图

\dv                    -- 查看视图
\dm                    -- 查看物化视图
SELECT * FROM pg_views;

19. 查看函数和存储过程

\df
\df+                   -- 更详细信息

20. 查看扩展

\dx                    -- psql 中
SELECT * FROM pg_extension;

三、psql 常用元命令（21–30）

21. 切换数据库

\c database_name

22. 切换扩展显示模式

\x

查看宽表结果时非常实用。

23. 显示 SQL 执行时间

\timing on

24. 定时重复执行上一条 SQL

\watch 2

每 2 秒重复执行，适合实时观察连接数、复制延迟、Vacuum 进度等指标。

25. 将查询结果输出到文件

\o output.txt

26. 通过客户端导出 CSV

\copy public.table_name TO '/tmp/table.csv' CSV HEADER

与 COPY 不同，\copy 是客户端命令，文件路径相对于客户端。

27. 执行 SQL 文件

\i /path/to/file.sql

28. 列出所有角色/用户

\du

29. 查看表权限

\dp public.table_name

30. 退出 psql

\q

四、连接与会话管理（31–40）

31. 查看当前所有连接

SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity;

32. 查看数据库最大连接数

SHOW max_connections;

33. 查看当前连接数

SELECT count(*) FROM pg_stat_activity;

34. 查看空闲事务中的连接（idle in transaction）

SELECT pid, usename, state, query, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

idle in transaction 是常见问题来源，会阻塞 Vacuum 和锁释放。

35. 终止连接

SELECT pg_terminate_backend(pid);

36. 取消正在执行的查询

SELECT pg_cancel_backend(pid);

37. 设置空闲事务超时

SET idle_in_transaction_session_timeout = '5min';

或修改 postgresql.conf。

38. 查看连接池建议 生产环境始终使用 PgBouncer 或应用侧连接池，PostgreSQL 默认 100 个连接、每个后端 5–10 MB，很容易触及内存限制。

39. 限制用户连接数

ALTER ROLE app_user CONNECTION LIMIT 20;

40. 查看数据库是否允许连接

SELECT datname, datallowconn FROM pg_database;

五、事务与锁管理（41–50）

41. 查看当前锁信息

SELECT * FROM pg_locks;

42. 查看未授予的锁（等待中的锁）

SELECT * FROM pg_locks WHERE NOT granted;

43. 查看锁阻塞关系

SELECT pid, pg_blocking_pids(pid), query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

很多卡顿问题不是 SQL 本身慢，而是等待另一个长事务释放锁。

44. 查看锁等待事件

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

45. 查看长事务

SELECT pid, usename, query, state, now() - xact_start AS xact_duration
FROM pg_stat_activity
WHERE state != 'idle' AND xact_start IS NOT NULL
ORDER BY xact_duration DESC;

46. 使用 RETURNING 获取操作后的数据

INSERT INTO users (name, email) VALUES ('张三', 'zhang@example.com') RETURNING id;
UPDATE users SET email = 'new@example.com' WHERE id = 1 RETURNING *;
DELETE FROM users WHERE id = 1 RETURNING *;

47. UPSERT（插入或更新）

INSERT INTO users (id, name, email) VALUES (1, '张三', 'zhang@example.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

48. 使用事务包装批量操作

BEGIN;
-- 多条 DML 操作
COMMIT;

批量操作建议用事务包装，提高性能。

49. 查看事务隔离级别

SHOW transaction_isolation;

50. 设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

六、SQL 性能与执行计划（51–60）

51. 查看估算执行计划（不实际执行）

EXPLAIN SELECT ...;

52. 查看实际执行计划（真正执行）

EXPLAIN ANALYZE SELECT ...;

生产环境慎用，会实际执行查询。

53. 查看带缓存信息的执行计划

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

可以查看命中和未命中的缓存块数。

54. 查看 JSON 格式执行计划

EXPLAIN (FORMAT JSON) SELECT ...;

55. 安装 pg_stat_statements 扩展

CREATE EXTENSION pg_stat_statements;

需要先在 postgresql.conf 中加载 shared_preload_libraries。

56. 查看总耗时最高的 SQL

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

既要看单次执行慢的 SQL，也要看执行次数极高的 SQL。

57. 重置 pg_stat_statements 统计

SELECT pg_stat_statements_reset();

58. 查看缓存命中率

SELECT 'cache hit rate' AS name,
       sum(heap_blks_hit) * 100 / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

59. 查看顺序扫描 vs 索引扫描比例

SELECT schemaname, tablename, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

60. 强制使用索引扫描（临时）

SET enable_seqscan = off;

仅用于调试，生产环境不建议。

七、表与索引管理（61–75）

61. 查看表大小

SELECT pg_size_pretty(pg_table_size('public.table_name'));
SELECT pg_size_pretty(pg_total_relation_size('public.table_name'));  -- 包含索引

62. 查看索引大小

SELECT pg_size_pretty(pg_indexes_size('public.table_name'));

63. 查看所有表大小（按大小排序）

SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

64. 查看未使用的索引

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

注意：不能因为 idx_scan = 0 就直接删除索引，需要确认统计信息是否刚重置。

65. 查看重复索引定义

-- 需要比较索引列、顺序、排序方式、表达式和过滤条件，不能只比较索引名称

实际判断重复索引时，应重点比较索引列、顺序、排序方式、表达式和过滤条件。

66. 创建 B-tree 索引

CREATE INDEX idx_name ON table_name (column_name);

67. 创建复合索引

CREATE INDEX idx_composite ON table_name (col1, col2);

复合索引列顺序应遵循最左前缀原则，将等值查询条件列放前面。

68. 创建覆盖索引（包含额外列）

CREATE INDEX idx_covering ON table_name (col1) INCLUDE (col2, col3);

覆盖索引可以避免回表查询。

69. 创建部分索引（条件索引）

CREATE INDEX idx_partial ON table_name (column_name) WHERE status = 'active';

只索引部分数据，节省空间。

70. 创建 GIN 索引（全文搜索/JSON）

CREATE INDEX idx_gin ON table_name USING GIN (jsonb_column);

71. 创建 BRIN 索引（大表时序数据）

CREATE INDEX idx_brin ON table_name USING BRIN (created_at);

适用于数据分布与物理顺序强相关的大表。

72. 重建索引

REINDEX INDEX index_name;
REINDEX TABLE table_name;

索引膨胀或损坏时需要重建。

73. 查看索引使用情况

SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan;

74. 重置表统计信息

SELECT pg_stat_reset_single_table_counters('table_name'::regclass);

75. 查看缺失的外键索引

-- 查找外键列上没有索引的情况
SELECT conrelid::regclass AS table_name, conname AS fk_name, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE contype = 'f'
AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = conrelid
    AND (SELECT array_agg(attnum) FROM pg_attribute WHERE attrelid = conrelid AND attnum = ANY(i.indkey))
    @> (SELECT array_agg(attnum) FROM pg_attribute WHERE attrelid = conrelid AND attname = ANY(
        (SELECT unnest(string_to_array(substring(pg_get_constraintdef(oid) FROM '\((.*?)\)'), ',')))
    ))
);

八、Vacuum、Autovacuum 与统计信息（76–88）

PostgreSQL 采用 MVCC 机制，更新和删除会产生死亡元组，需要 Vacuum 清理。

76. 手动执行 VACUUM

VACUUM table_name;           -- 清理死亡元组，不回收空间给 OS
VACUUM FULL table_name;      -- 重写表，压缩数据，归还空间给 OS（会锁表）

VACUUM FULL 会锁表，生产环境慎用。

77. 手动执行 ANALYZE 更新统计信息

ANALYZE table_name;

78. 查看表的死亡元组数量

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

大量死亡元组说明 Autovacuum 可能跟不上。

79. 查看表膨胀率

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       round(100 * (pg_total_relation_size(schemaname||'.'||tablename) -
       pg_table_size(schemaname||'.'||tablename)) / pg_total_relation_size(schemaname||'.'||tablename), 2) AS bloat_pct
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY bloat_pct DESC;

80. 查看 Autovacuum 配置

SELECT name, setting, unit, source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name;

81. 调整统计信息目标值

ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 1000;

适用于数据分布倾斜、默认统计信息粒度不足的字段。

82. 查看正在执行的 Vacuum 进度

SELECT pid, datname, relid::regclass AS table_name, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count, num_dead_item_ids
FROM pg_stat_progress_vacuum;

PostgreSQL 为 VACUUM、ANALYZE、CREATE INDEX、CLUSTER、COPY 等操作提供进度视图。

83. 查看 Autovacuum Worker 进程

SELECT pid, datname, usename, backend_type, query_start,
       wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

84. 查看事务年龄和冻结风险

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

事务 ID 年龄过高可能导致数据库进入防止事务 ID 回卷的保护状态。

85. 查看表级冻结年龄

SELECT n.nspname AS schema_name, c.relname AS table_name,
       age(c.relfrozenxid) AS xid_age
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
ORDER BY age(c.relfrozenxid) DESC LIMIT 20;

86. 手动冻结

VACUUM FREEZE table_name;

87. 调整表的填充因子（fillfactor）

ALTER TABLE table_name SET (fillfactor = 80);

默认 100，频繁更新的表设小一些可减少页面分裂。

88. 查看表和索引的存储参数

SELECT relname, reloptions FROM pg_class WHERE relname = 'table_name';

九、WAL、检查点与归档（89–94）

89. 查看当前 WAL 位置

SELECT pg_current_wal_lsn();

90. 查看 WAL 文件名

SELECT pg_walfile_name(pg_current_wal_lsn());

91. 计算两个 WAL 位置的差值

SELECT pg_size_pretty(pg_wal_lsn_diff('0/5000000'::pg_lsn, '0/4000000'::pg_lsn));

92. 查看 WAL 配置

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('wal_level', 'max_wal_size', 'min_wal_size', 'wal_buffers',
               'wal_compression', 'checkpoint_timeout', 'checkpoint_completion_target',
               'archive_mode', 'archive_command')
ORDER BY name;

93. 查看 WAL 统计信息

SELECT * FROM pg_stat_wal;

常见字段：wal_records、wal_fpi、wal_bytes、wal_buffers_full、wal_write、wal_sync。

94. 手动执行检查点

CHECKPOINT;

十、主从复制与流复制（95–97）

95. 查看复制状态

SELECT * FROM pg_stat_replication;

查看主库上的复制连接状态、延迟等信息。

96. 查看复制槽

SELECT * FROM pg_replication_slots;

复制槽未消费会导致 WAL 堆积。

97. 查看从库恢复状态

SELECT * FROM pg_stat_wal_receiver;   -- 从库上执行
SELECT pg_is_in_recovery();           -- 判断是否处于恢复模式

十一、备份与恢复（98–99）

98. pg_dump 逻辑备份

pg_dump -d database_name > backup.sql                # SQL 格式
pg_dump -Fc -d database_name > backup.dump           # 自定义压缩格式
pg_dump -Fc -d database_name -t table_name > backup.dump  # 单表备份

使用 -Fc 自定义格式配合 pg_restore 更灵活。

99. pg_restore 恢复

pg_restore -d database_name backup.dump
pg_restore -d database_name -t table_name backup.dump  # 恢复单表

十二、权限管理（100）

100. 权限管理常用操作

-- 授予读取权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_readonly;

-- 配置默认权限（未来新建的表自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

-- 将角色授予用户
GRANT app_readonly TO app_user;

-- 修改密码
ALTER ROLE app_user PASSWORD 'NewStrongPassword';

-- 禁止登录
ALTER ROLE app_user NOLOGIN;

-- 删除角色（需确认该用户没有拥有对象）
DROP ROLE app_user;

附：故障排查的正确顺序

真正有价值的不是背下所有命令，而是知道什么时候使用哪一类命令。当 PostgreSQL 出现卡顿时，建议按以下顺序排查：

第一步：检查连接和正在执行的 SQL

SELECT * FROM pg_stat_activity;

确认：连接数暴增、长时间运行 SQL、大量空闲连接、idle in transaction、相同 SQL 集中并发、明显的等待事件。

第二步：检查长事务和锁等待

SELECT * FROM pg_locks WHERE NOT granted;
SELECT pid, pg_blocking_pids(pid), query FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

很多卡顿是 SQL 在等待另一个长事务释放锁。

第三步：检查 SQL 执行计划和历史负载

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;                    -- 当前 SQL
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC;  -- 历史 SQL

第四步：检查死亡元组和 Autovacuum

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

如果存在大量死亡元组，继续判断：是否有长事务阻止清理、Autovacuum 是否被关闭、参数是否过于保守。

以上 100 条技巧覆盖了 PostgreSQL 日常使用和运维的绝大部分场景。命令只是工具，理解 PostgreSQL 的 MVCC、事务可见性、Vacuum 机制等核心原理，才能在生产环境中从容应对各种问题。