Greenplum查看表的创建时间、修改时间、VACUUM、分析时间，建库时间、修改密码、赋权、资源队列、重命名等DDL时间

AiDBA宝典

发布于 2024-04-15 11:01:06

2250

发布于 2024-04-15 11:01:06

简介

对于表的DDL时间，可以进入对应的数据库中，然后查询视图pg_stat_operations、pg_stat_last_shoperation、pg_stat_operations、pg_stat_partition_operations 都可以。

对于全局对象，例如修改密码、赋权、回收权限、资源队列、建库、重命名、角色等全局对象，可以直接查询视图pg_stat_last_shoperation。

查看某个表执行DDL的操作时间

可以使用系统视图pg_stat_operations和 pg_stat_partition_operations 查看在一个对象（例如一个表）上执行的动作。

例如，要查看在一个表上执行的动作，比如它何时被创建以及它上一次是什么时候被清理和分析：

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_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数据库服务器日志文件的时间戳相同，以防您需要在日志中查找有关操作的更多详细信息。

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数据库服务器日志文件的时间戳是相同的，以便在日志中查询更多关于操作细节的信息。

示例

一定要先进入创建表的数据库

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.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记录的是全局对象，例如修改密码、赋权、回收权限、资源队列、建库、重命名、角色等全局对象：

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)