简介
对于表或索引的DDL时间,可以进入对应的数据库中,然后查询视图pg_stat_operations、pg_stat_last_shoperation、pg_stat_partition_operations 都可以。
对于全局对象,例如修改密码、赋权、回收权限、资源队列、建库、重命名、角色等全局对象,可以直接查询视图pg_stat_last_shoperation。
查看某个表或索引执行DDL的操作时间
可以使用系统视图pg_stat_operations和 pg_stat_partition_operations 查看在一个对象(例如一个表)上执行的动作。
例如,要查看在一个表上执行的动作,比如它何时被创建以及它上一次是什么时候被清理和分析:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time FROM pg_stat_operations WHERE objname='cust'; schema | table | role | action | type | time --------+-------+------+---------+-------+-------------------------- sales | cust | main | CREATE | TABLE | 2016-02-09 18:10:07.867977-08 sales | cust | main | VACUUM | | 2016-02-10 13:32:39.068219-08 sales | cust | main | ANALYZE | | 2016-02-25 16:07:01.157168-08 (3 rows) HDW=# SELECT d.actionname, count(*) from pg_stat_operations d GROUP BY d.actionname ; actionname | count ------------+------- ANALYZE | 1122 CREATE | 2653 ALTER | 764 TRUNCATE | 129 PRIVILEGE | 1063 VACUUM | 2030 (6 rows) |
查看GreenPlum的所有数据库建库时间
进入任意数据库,查询建库时间:
| SELECT d.objid dbid,b.datname,d.stausename,d.statime from pg_stat_last_shoperation d left join pg_database b on d.objid=b.oid WHERE d.staactionname='CREATE' and stasubtype='DATABASE' order by d.statime; |
pg_stat_last_shoperation简介
pg_stat_last_shoperation表包含全局对象(角色,表空间等)的元数据跟踪信息。
列 | 类型 | 参考 | 描述 |
---|
classid | oid | pg_class.oid | 包含该对象的系统目录的OID。 |
objid | oid | any OID column | 系统目录中对象的OID。 |
staactionname | name | | 对该对象采取的操作。 |
stasysid | oid | | |
stausename | name | | 对此对象执行操作的角色的名称。 |
stasubtype | text | | 操作对象的类型或执行的操作的子类。 |
statime | timestamp with timezone | | 操作的时间戳。 这与写入Greenplum数据库服务器日志文件的时间戳相同,以防您需要在日志中查找有关操作的更多详细信息。 |
pg_stat_last_operation
pg_stat_last_operation表包含关于数据库对象(表、视图等)的元数据跟踪信息。
列 | 类型 | 参考 | 描述 |
---|
classid | oid | pg_class.oid | 包含对象的系统目录的OID。 |
objid | oid | any OID column | 对象在其系统目录内的对象OID。 |
staactionname | name | | 在一个对象上采取的动作。 |
stasysid | oid | pg_authid.oid | pg_authid.oid的外键。 |
stausename | name | | 在该对象上执行操作的角色的名称。 |
stasubtype | text | | 被执行操作的对象的类型或者被执行操作的子类。 |
statime | timestamp with timezone | | 操作的时间戳。 这和写到Greenplum数据库服务器日志文件的时间戳是相同的,以便在日志中查询更多关于操作细节的信息。 |
pg_stat_operations简介
视图pg_stat_operations显示了关于最后一个执行在数据库对象(例如,表、索引、视图或者数据库)上或者全局对象(例如角色)的操作的细节信息。
列 | 类型 | 参考 | 描述 |
---|
classname | text | | pg_catalog schema中存储有关此对象的记录的系统表的名称 (pg_class=关系, pg_database=数据库,pg_namespace=schemas,pg_authid=角色) |
objname | name | | 对象的名称。 |
objid | oid | | 对象的OID。 |
schemaname | name | | 对象所在的schema的名称。 |
usestatus | text | | 对对象执行最后一次操作的角色的状态(CURRENT =系统中当前活动的角色,DROPPED =系统中不再存在的角色,CHANGED =系统中存在的角色名称,但自上次操作以来已更改)。 |
usename | name | | 对此对象执行操作的角色的名称。 |
actionname | name | | 对该对象采取的操作。 |
subtype | text | | 操作对象的类型或执行的操作的子类。 |
statime | timestamptz | | 操作的时间戳。 这与写入Greenplum数据库服务器日志文件的时间戳相同,以防您需要在日志中查找有关操作的更多详细信息。 |
示例
一定要先进入创建表的数据库
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | postgres=# create table t6(id int,abc text); CREATE TABLE postgres=# select * from pg_stat_last_operation where objid = 't6' ::regclass order by statime; classid | objid | staactionname | stasysid | stausename | stasubtype | statime ---------+-------+---------------+----------+------------+------------+------------------------------- 1259 | 32790 | CREATE | 10 | gpadmin | TABLE | 2023-02-02 09:17:28.403562+08 (1 row) postgres=# select * from pg_stat_operations where objname='t6' order by statime; classname | objname | objid | schemaname | usestatus | usename | actionname | subtype | statime -----------+---------+-------+------------+-----------+---------+------------+---------+------------------------------- pg_class | t6 | 32790 | public | CURRENT | gpadmin | CREATE | TABLE | 2023-02-02 09:17:28.403562+08 (1 row) postgres=# select * from pg_stat_last_operation where objid = 't6' ::regclass order by statime; classid | objid | staactionname | stasysid | stausename | stasubtype | statime ---------+-------+---------------+----------+------------+------------+------------------------------- 1259 | 32790 | CREATE | 10 | gpadmin | TABLE | 2023-02-02 09:17:28.403562+08 (1 row) postgres=# |
SQL总结
| SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time FROM pg_stat_operations WHERE objname='t6'; SELECT d.actionname, count(*) from pg_stat_operations d GROUP BY d.actionname ; SELECT d.actionname,d.subtype, count(*) from pg_stat_operations d GROUP BY d.actionname,d.subtype order by 1,2 ; SELECT d.staactionname,d.stasubtype, count(*) from pg_stat_last_shoperation d GROUP BY d.staactionname,d.stasubtype ; |
总结
1、若要查询某个对象,则一定要先进入创建表的数据库
2、目前只能用于GP,postgres没有这几个视图,PG的实现可以参考:https://www.xmmup.com/zaipgshujukuzhongruheshixiankeyichakanbiaodechuangjianshijianxiugaishijianvacuumfenxishijianbi.html
3、pg_stat_operations记录的内容不包括drop动作,只包括create、analyze、alter、truncate、privilege、vacuum,若某个对象被drop掉,则该对象在该表中的所有记录也会被删除。
4、pg_stat_last_shoperation记录的是全局对象,例如修改密码、赋权、回收权限、资源队列、建库、重命名、角色等全局对象:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | postgres=# SELECT d.staactionname,d.stasubtype, count(*) from pg_stat_last_shoperation d GROUP BY d.staactionname,d.stasubtype order by d.staactionname,d.stasubtype; staactionname | stasubtype | count ---------------+-------------------+------- ALTER | 10 OPTIONS | 1 ALTER | 12 OPTIONS | 1 ALTER | 2 OPTIONS | 1 ALTER | 3 OPTIONS | 1 ALTER | 8 OPTIONS | 3 ALTER | 9 OPTIONS | 22 ALTER | ACTIVE_STATEMENTS | 1 ALTER | CONNECTION LIMIT | 13 ALTER | cpu_rate_limit | 2 ALTER | OWNER | 1 ALTER | PASSWORD | 1 ALTER | RENAME | 2 ALTER | RESOURCE QUEUE | 2 ALTER | SET | 4 CREATE | DATABASE | 47 CREATE | RESOURCE QUEUE | 3 CREATE | ROLE | 47 PRIVILEGE | GRANT | 7 (18 rows) |
5、pg_stat_operations记录了表或索引的相关信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | postgres=# SELECT d.actionname,d.subtype, count(*) from pg_stat_operations d GROUP BY d.actionname,d.subtype order by 1,2 ; actionname | subtype | count ------------+----------------------+------- ALTER | 10 OPTIONS | 1 ALTER | 12 OPTIONS | 1 ALTER | 2 OPTIONS | 1 ALTER | 8 OPTIONS | 3 ALTER | 9 OPTIONS | 22 ALTER | ACTIVE_STATEMENTS | 1 ALTER | ADD INDEX | 13 ALTER | ALTER COLUMN DEFAULT | 1 ALTER | CONNECTION LIMIT | 13 ALTER | CREATEEXTTABLE | 1 ALTER | INHERIT | 4 ALTER | OWNED BY | 1 ALTER | OWNER | 52 ALTER | PASSWORD | 1 ALTER | RENAME | 3 ALTER | RESOURCE QUEUE | 3 ALTER | SET | 1 ANALYZE | | 153 CREATE | DATABASE | 53 CREATE | INDEX | 20 CREATE | RESOURCE QUEUE | 4 CREATE | ROLE | 52 CREATE | SCHEMA | 2 CREATE | SEQUENCE | 9 CREATE | TABLE | 73 CREATE | VIEW | 3 PRIVILEGE | GRANT | 13 VACUUM | | 154 VACUUM | REINDEX | 12 (29 rows) postgres=# |
参考
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/99f09222f412b1bd.md
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/10e56fb7693f9f44.md#7sl356
目前只能用于GP,postgres没有这几个视图