PostgreSQL 是否可以获取在PostgreSQL中执行的查询历史记录

在本文中，我们将介绍如何在PostgreSQL中获取查询历史记录。查询历史记录对于数据库管理员和开发人员来说非常重要，可以追踪和分析数据库性能和行为。

1. 通过pg_stat_statements模块获取查询历史记录

PostgreSQL提供了一个名为pg_stat_statements的模块，它可以记录并跟踪执行的查询语句。要启用pg_stat_statements模块，需要在postgresql.conf文件中进行配置，并重新启动数据库。

通过以下步骤启用pg_stat_statements模块：

打开postgresql.conf文件：

sudo vim /etc/postgresql/<version>/main/postgresql.conf

在文件中找到以下行，并取消注释：

shared_preload_libraries = 'pg_stat_statements'

保存文件并退出。
重新启动PostgreSQL数据库：

sudo service postgresql restart

运行以下命令创建pg_stat_statements扩展：

psql -U <username> -d <database> -c 'CREATE EXTENSION pg_stat_statements'

现在，查询历史记录将开始记录到pg_stat_statements视图中。可以使用以下命令查看查询历史记录：

psql -U <username> -d <database> -c 'SELECT * FROM pg_stat_statements'

该命令将返回一个包含所有查询历史记录的表格。

2. 使用pg_query_state扩展获取详细的查询历史记录

如果你需要更详细的查询历史记录，包括每个查询执行的详细信息和计划信息，可以使用pg_query_state扩展。这个扩展可以提供关于每个查询的更多细节，比如执行时间、I/O成本和内存使用情况。

以下是使用pg_query_state扩展获取查询历史记录的步骤：

在你的PostgreSQL服务器上，使用以下命令安装pg_query_state扩展：

git clone https://github.com/ankane/pg_query_state.git
cd pg_query_state
make && make install

打开postgresql.conf文件：

sudo vim /etc/postgresql/<version>/main/postgresql.conf

在文件中找到以下行，并将其取消注释：

shared_preload_libraries = 'pg_query_state'

保存文件并退出。
重新启动PostgreSQL数据库：

sudo service postgresql restart

现在，pg_query_state扩展已经安装并启用。可以使用以下查询来查找详细的查询历史记录：

SELECT pgs.queryid,
       pgs.userid,
       pgs.dbid,
       pgs.query,
       pgs.calls,
       pgs.total_time,
       pgs.rows,
       pgs.shared_blks_hit,
       pgs.shared_blks_read
FROM pg_stat_statements pgs
JOIN pg_query_state('pg_stat_statements') q USING (queryid)
ORDER BY pgs.total_time DESC;

这将返回一个包含详细查询历史记录的表格，包括查询ID、用户ID、数据库ID、查询语句、调用次数、总执行时间、返回行数以及共享块的命中和读取。

3. 通过pgAudit扩展监视查询历史记录

pgAudit是一个强大的扩展，可以提供完整的审计能力，包括查询历史记录。通过pgAudit，你可以记录所有执行的查询语句，并获取更详细的上下文信息，如客户端IP地址和用户名。

以下是使用pgAudit扩展来监视查询历史记录的步骤：

在你的PostgreSQL服务器上，使用以下命令安装pgAudit扩展：

git clone https://github.com/pgaudit/pgaudit.git
cd pgaudit
make && make install

进入你的数据库，并运行以下命令来创建pgAudit扩展：

CREATE EXTENSION pgaudit;

设置pgAudit的详细级别，以确定要记录的查询类型。可以使用以下命令设置全局（数据库级别）的pgAudit详细级别（示例为SQL_SELECT）：

ALTER SYSTEM SET pgaudit.log = 'read, write, misc' ;

重新加载PostgreSQL配置：

sudo service postgresql reload

现在，pgAudit扩展已经安装并配置完成。它将开始记录所有满足详细级别要求的查询。可以使用以下查询来查看查询历史记录：

SELECT * FROM pgaudit.pg_statements;

这将返回一个包含所有记录的查询历史记录的表格，包括时间戳、用户名、查询语句等。

总结

通过pg_stat_statements、pg_query_state和pgAudit等扩展，我们可以很容易地获取并跟踪在PostgreSQL中执行的查询历史记录。这些查询历史记录对于数据库性能调优、故障排除和安全审计非常有价值。无论使用哪种方法，都可以根据需要选择适合的工具来监视和分析查询历史记录。

请记住，在实际环境中启用这些扩展时，要谨慎操作，确保合理使用系统资源，并根据具体需求配置相关参数。