如何查询Postgres中索引的创建用户及对应创建时间？

核心结论 #

该需求可根据你的数据库配置情况部分或全部实现：默认配置下可查询索引创建时间（PG12及以上版本）和索引所属用户，如需追溯实际执行DDL的操作人，需要提前开启相关日志或扩展功能。

1. 默认配置下可查询的信息 #

无需提前做额外配置，你可以直接通过系统表查询到索引的所属用户、创建时间（仅PG12及以上版本支持创建时间查询），执行以下SQL即可：

SELECT 
    ic.relname AS index_name,
    tc.relname AS 所属表名,
    a.rolname AS 索引所属用户,
    ic.relcreationtime AS 索引创建时间
FROM pg_class ic
JOIN pg_index i ON ic.oid = i.indexrelid
JOIN pg_class tc ON i.indrelid = tc.oid
JOIN pg_authid a ON ic.relowner = a.oid
WHERE ic.relname IN ('someName', 'someName1');

注意：如果你的Postgres版本低于12，系统表pg_class不存在relcreationtime字段，默认无法直接查询索引创建时间。此外默认配置下仅能查询到索引的所属用户，无法区分「创建索引的操作用户」和「索引所属用户」（比如超级用户为普通用户创建索引的场景，默认只能查到索引属于普通用户，无法查到是超级用户执行的创建操作）。

2. 追溯实际执行创建DDL用户的前置要求 #

如果你需要准确获取执行CREATE INDEX语句的实际操作用户，需要在创建索引操作执行前就开启以下任意一种配置：

• 调整Postgres配置文件中的log_statement参数为'ddl'，开启后所有DDL语句都会被记录到服务端日志中，日志包含执行用户、执行时间、完整SQL内容，你可以直接在日志中搜索对应索引名的创建记录即可。
• 安装开启pg_stat_statements扩展，该扩展会记录数据库运行过程中执行的所有SQL语句，你可以通过过滤CREATE INDEX相关语句匹配到对应索引的创建记录，获取执行用户和时间信息，注意该扩展的记录数量有上限，太久之前的语句可能会被新记录覆盖淘汰。
• 开启pgaudit等审计插件，可更精细化的记录所有DDL操作的执行主体、操作时间、上下文信息，适合需要长期审计操作的场景。

如果你在创建索引前没有开启上述任意一种配置，无法回溯历史操作的实际执行用户。

内容的提问来源于stack exchange，提问作者gabriel119435