PostgreSQL系统表、视图归纳总结及相关运维SQL
文章目录
- 一、系统表归纳总结
- 二、性能相关视图
- 2.1 pg_stat_activity
- 2.2 pg_lock
- 2.3 pg_stat_database
- 三、相关运维SQL
一、系统表归纳总结
PostgreSQL 必知内容
跟oracle数据库一样，pg数据库也有自己的系统表和视图，在查询问题的时候会涉及到。
| 序号 | 系统表 | 内容 |
|---|---|---|
| 1 | pg_class | 该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据,以及其之间的关系 |
| 2 | pg_attribute | |
| 3 | pg_database | 该系统表存储数据库的信息,每个集群一份而不是每个数据库一份 |
| 4 | pg_index | 存储索引的部分信息，其它的信息大多数存储在pg_class |
| 5 | pg_indexes | 系统视图提供对数据库中每个索引有用信息的访问 |
| 6 | pg_partition | 系统表存储数据库内所有分区表、分区、分区索引、分区上toast表 |
| 7 | pg_object | 系统视图，存储数据库对象信息 |
| 8 | pg_proc | 系统表存储函数或存储过程的信息 |
| 9 | pg_user | 可以看到数据库中所有用户以及用户id和权限 |
| 10 | pg_user_status | 系统表提供了访问数据库用户的状态 |
| 11 | pg_authid | 系统表存储有关数据库认证标识符（角色）的信息 |
| 12 | pg_trigger | 系统表存储触发器信息 |
| 13 | pg_stat_activity | 会话视图 |
| 14 | pg_lock | 锁信息 |
| 15 | pg_stat_database | 每个数据库的统计信息 |
二、性能相关视图
2.1 pg_stat_activity
此视图显示和当前用户查询相关的信息，字段保存的是上一次执行的信息，管理员可以在任意一个库直接查所有会话信息。
查看正在运行的语句
select datname,usename,state,pid from pg_stat_activity;
查询系统中长时间运行的查询语句
select current_timestamp-query_start as runtime,datname,usename,query from pg_stat_activity where state !='idle' order by 1 desc;
查询运行时间超过一定阈值的语句
select query from pg_stat_activity where current_timestamp - query_start > interval '1 days';
查询阻塞状态的语句
select datname,usename,state,query from pg_stat_activity where waiting=true;
2.2 pg_lock
存储各打开事务所持有的锁信息
查看执行SQL是否被锁
select * from pg_locks;
根据会话查询锁信息
select t1.locktype,t1.relation::regclass,t1.virtualxid,t1.transactinid,t1.mode,t1.granted,t2.datname,t2.usename,t2.state,t2.query from pg_locks t1 join pg_stat_activity t2 on t1.pid =t2.pid where t1.pid!=pg_backend_pid();
select * from pg_locks t1 left join pg_stat_activity t2 on t1.pid=t2.pid;
2.3 pg_stat_database
该视图包含每个数据库的统计信息
select * from pg_stat_database where datname='XXX';
三、相关运维SQL
查询表字段信息
select t2.relname as tablename,t1.attname as colname,t3.typname as coltype,t1.attlen as length from pg_attribute t1 inner join pg_class t2 on t1.attrelid=t2.oid inner join pg_type t3 on t1.attrelid=t3.oid where t2.relname='XXX';
查询分区表
select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;
查询分区表属性信息
select t1.oid,t1.relname,partstrategy,boundaries,t1.reltablespace,t1.parentid from pg_partition t1,pg_class t2 where t1.parentid=t2.oid and t2.relname ='XXX' and t1.parttype='p';
查看表及索引状态
select t1.relname,t3.tablename,t1,relpersistence,t1.relkind,t1.parttype,t2.indnatts,t2.indisusable,t2.indisvalid from pg_class t1 join pg_index t2 on t1.oid=t2.indexrelid join pg_indexes t3 on t1.relname=t3.indexname where t1.relname='XXX';
查询存储过程信息
select pg_get_userbyid(proowner) as owner,procname,prokind,prosrc from pg_proc where proname ='XXXX';
查询用户信息
select * from pg_user;
查询用户状态
select t2.rolname,t1.failcount,t1.locktime,t1.rolstatus,t1.passwordexpired from pg_user_status t1 inner join pg_authid t2 on t1.roloid=t2.oid;
