一、环境信息

• PostgreSQL 版本: 16.1
• 安装目录: /pgccc/app/16.1/
• 数据目录: /pgccc/pgdata/
• 源码目录: /pgccc/soft/postgresql-16.1/
• 操作系统用户: postgres

二、问题现象

在创建扩展时出现拼写错误：

CREATE EXTENSION pg_stat_statments;  -- 错误：少了一个'e'
ERROR: extension "pg_stat_statments" is not available
DETAIL: Could not open extension control file "/pgccc/app/16.1/share/postgresql/extension/pg_stat_statments.control": No such file or directory.

三、安装步骤

1. 查找扩展源码

# 在系统中查找contrib目录
find / -name contrib

# 进入postgresql源码的contrib目录
cd /pgccc/soft/postgresql-16.1/contrib/

# 查看所有可用的扩展
ll

2. 进入并编译pg_stat_statements扩展

# 进入pg_stat_statements目录
cd pg_stat_statements/

# 查看目录内容
ls

# 编译并安装扩展
make install

3. 编译安装输出

安装目录：
- /pgccc/app/16.1/lib/postgresql/pg_stat_statements.so
- /pgccc/app/16.1/share/postgresql/extension/pg_stat_statements.control
- /pgccc/app/16.1/share/postgresql/extension/ 下的所有SQL文件

四、创建扩展的正确方法

1. 连接数据库

# 切换到postgres用户
su - postgres

# 连接到PostgreSQL
psql

2. 创建扩展（正确拼写）

-- 正确的扩展名称是 pg_stat_statements（有'e'）
CREATE EXTENSION pg_stat_statements;

3. 验证扩展

-- 查看已安装的扩展
\dx pg_stat_statements;

-- 查看所有与统计相关的扩展
SELECT * FROM pg_available_extensions WHERE name LIKE '%pg_stat%';

-- 查看扩展详细信息
SELECT 
    extname, 
    extversion, 
    extrelocatable, 
    extconfig, 
    extcondition 
FROM pg_extension 
WHERE extname = 'pg_stat_statements';

五、配置参数

1. 编辑postgresql.conf配置文件

vi /pgccc/pgdata/postgresql.conf

2. 添加以下配置

# 预加载pg_stat_statements模块
shared_preload_libraries = 'pg_stat_statements'

# pg_stat_statements配置
pg_stat_statements.max = 10000              # 跟踪的最大语句数
pg_stat_statements.track = all              # 跟踪所有语句
pg_stat_statements.track_utility = on       # 跟踪实用程序命令（如DDL）
pg_stat_statements.save = on                # 重启后保留统计信息
pg_stat_statements.track_planning = on      # 跟踪计划时间（PostgreSQL 16+）
pg_stat_statements.track_wal = on           # 跟踪WAL使用情况（PostgreSQL 16+）

3. 重启PostgreSQL服务

# 使用pg_ctl重启
pg_ctl restart -D /pgccc/pgdata -l /pgccc/pgdata/logfile

# 或者使用系统服务（如果已配置）
systemctl restart postgresql-16

六、使用pg_stat_statements

1. 查看统计信息

-- 查看执行次数最多的查询
SELECT 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time,
    rows,
    100.0 * total_exec_time / sum(total_exec_time) OVER() AS percentage
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

-- 查看最耗时的查询
SELECT 
    query, 
    calls, 
    total_exec_time, 
    mean_exec_time,
    min_exec_time,
    max_exec_time
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;

-- 查看I/O最多的查询
SELECT 
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    shared_blks_dirtied,
    shared_blks_written,
    local_blks_hit,
    local_blks_read,
    local_blks_dirtied,
    local_blks_written,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements 
ORDER BY (shared_blks_read + shared_blks_written) DESC 
LIMIT 10;

2. 重置统计信息

-- 重置所有统计信息
SELECT pg_stat_statements_reset();

-- 重置特定用户的统计信息
SELECT pg_stat_statements_reset(userid);

3. 高级查询

-- 查看查询计划时间
SELECT 
    query,
    calls,
    total_plan_time,
    total_exec_time,
    mean_plan_time,
    mean_exec_time
FROM pg_stat_statements 
WHERE calls > 100 
ORDER BY total_plan_time DESC 
LIMIT 10;

-- 查看WAL使用情况（PostgreSQL 16+）
SELECT 
    query,
    calls,
    wal_records,
    wal_fpi,
    wal_bytes
FROM pg_stat_statements 
ORDER BY wal_bytes DESC 
LIMIT 10;

七、维护与管理

1. 监控扩展状态

-- 查看pg_stat_statements视图的定义
\d pg_stat_statements

-- 查看扩展依赖关系
SELECT 
    classid::regclass,
    objid,
    objsubid,
    refclassid::regclass,
    refobjid,
    deptype
FROM pg_depend 
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pg_stat_statements');

2. 清理旧数据

-- 定期清理（如每天执行）
-- 可以创建一个定时任务来清理过时的统计信息
DELETE FROM pg_stat_statements WHERE calls = 0;

3. 调整配置

-- 动态调整配置（无需重启）
ALTER SYSTEM SET pg_stat_statements.max = 20000;
SELECT pg_reload_conf();

八、常见问题解决

问题1：扩展无法加载

-- 检查是否已添加到shared_preload_libraries
SHOW shared_preload_libraries;

-- 如果没有，需要重启数据库

问题2：性能问题

-- 如果pg_stat_statements影响性能，可以调整采样率
ALTER SYSTEM SET pg_stat_statements.track = 'top';
SELECT pg_reload_conf();

问题3：磁盘空间不足

-- 减少跟踪的语句数量
ALTER SYSTEM SET pg_stat_statements.max = 5000;
SELECT pg_reload_conf();

-- 重置统计信息释放空间
SELECT pg_stat_statements_reset();

九、总结

安装要点：

1. 正确路径：确保在正确的contrib目录中编译
2. 正确拼写：扩展名是 pg_stat_statements（注意有’e’）
3. 正确配置：需要在postgresql.conf中添加shared_preload_libraries
4. 重启服务：配置修改后需要重启PostgreSQL

使用建议：

1. 生产环境：设置适当的max值，避免占用过多内存
2. 监控：定期分析统计信息，优化慢查询
3. 维护：定期清理无用的统计信息
4. 版本：PostgreSQL 16+支持更多特性（如WAL跟踪）

通过pg_stat_statements，可以深入分析数据库的查询性能，找出瓶颈，进行有针对性的优化。