Potgresql常用系统表

数据库

Potgresql常用系统表

1.收集所有 PostgreSQL 数据库的信息，包括数据库名称、数据库所有者的 OID、数据目录等信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT oid as " OID",
       datname as "数据库名称",
       datdba as "数据库所有者的OID",
       encoding as "编码方式",
       datcollate as "Collation 顺序",
       datctype as "字符集转换顺序",
       datistemplate as "是否是模板数据库",
       datallowconn as "是否允许连接",
       datconnlimit as "允许连接的最大用户数",
       datlastsysoid as "最近一次系统OID的值",
       datfrozenxid as "最早事务ID",
       datminmxid as "最小 MultiXact ID",
       dattablespace as "默认表空间",
       datacl as "访问控制列表"
FROM pg_catalog.pg_database;

2.pg_tablespace
收集所有表空间的信息，包括表空间名称、所属数据库等信息

1
2
3
4
5
6
SELECT oid as "OID",
       spcname as "表空间名称",
       spcowner as "所有者的OID",
       spcacl as "访问控制列表",
       spcoptions as "选项"
FROM pg_catalog.pg_tablespace;

3.pg_user
收集所有 PostgreSQL 用户的信息，包括用户名、用户的角色、密码哈希等信息。

1
2
3
4
5
6
7
8
9
10
SELECT usename as "用户名",
       usesysid as "用户系统ID",
       usecreatedb as "是否允许创建数据库",
       usesuper as "是否是超级用户",
       userepl as "是否允许进行流复制",
       usebypassrls as "是否允许绕过行级安全策略",
       passwd as "用户口令",
       valuntil as "密码过期时间",
       useconfig as "用户配置"
FROM pg_catalog.pg_user;

4.pg_roles
收集所有 PostgreSQL 角色的信息，包括角色名称、是否为超级用户、连接限制等信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT rolname as "角色名称",
       rolsuper as "是否是超级用户",
       rolinherit as "是否继承表的权限",
       rolcreaterole as "是否允许创建角色",
       rolcreatedb as "是否允许创建数据库",
       rolcanlogin as "是否允许登录连接",
       rolreplication as "是否允许流复制",
       rolconnlimit as "连接数限制",
       rolpassword as "密码哈希值",
       rolvaliduntil as "密码过期时间",
       rolbypassrls as "是否允许绕过行级安全策略",
       rolconfig as "角色配置参数",
       oid as "OID"
FROM pg_catalog.pg_roles;

5.pg_stat_database
收集每个 PostgreSQL 数据库的统计信息，包括连接数、查询数、IO 信息等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT datid as "数据库ID",
       datname as "数据库名称",
       numbackends as "当前连接数",
       xact_commit as "已提交事务数",
       xact_rollback as "已回滚事务数",
       blks_read as "读取磁盘块数",
       blks_hit as "缓存命中块数",
       tup_returned as "检索的行数",
       tup_fetched as "读取的行数",
       tup_inserted as "插入的行数",
       tup_updated as "更新的行数",
       tup_deleted as "删除的行数",
       conflicts as "写入冲突次数",
       temp_files as "临时文件数",
       temp_bytes as "临时文件占用空间",
       deadlocks as "死锁数量",
       checksum_failures as "校验和错误数",
       checksum_last_failure as "最近校验和检查错误时间",
       blk_read_time as "磁盘块读取时间(毫秒)",
       blk_write_time as "磁盘块写入时间(毫秒)",
       stats_reset as "统计信息重置时间"
FROM pg_catalog.pg_stat_database;

6.pg_stat_bgwriter
收集后台写入进程的统计信息。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT checkpoints_timed as "定时检查点次数",
       checkpoints_req as "请求检查点次数",
       checkpoint_write_time as "检查点写入耗时(单位:毫秒)",
       checkpoint_sync_time as "检查点同步耗时(单位:毫秒)",
       buffers_checkpoint as "检查点期间脏页数量",
       buffers_clean as "后台清理的脏页数量",
       maxwritten_clean as "后台清理的最大块号",
       buffers_backend as "从后端读取的缓存块数量",
       buffers_backend_fsync as "后端强制刷新数量",
       buffers_alloc as "分配的缓存块数量",
       stats_reset as "统计信息重置时间"
FROM pg_catalog.pg_stat_bgwriter;

7.pg_stat_user_tables
收集每个用户表的统计信息，包括 SELECT、INSERT、UPDATE、DELETE、序列扫描等信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT relid as "表ID",
       schemaname as "所属模式",
       relname as "表名",
       seq_scan as "顺序扫描次数",
       seq_tup_read as "顺序扫描行数",
       idx_scan as "索引扫描次数",
       idx_tup_fetch as "索引扫描行数",
       n_tup_ins as "插入行数",
       n_tup_upd as "更新行数",
       n_tup_del as "删除行数",
       n_tup_hot_upd as "热更新行数",
       n_live_tup as "当前行数",
       n_dead_tup as "死亡行数",
       n_mod_since_analyze as "自上次分析以来修改的行数",
       last_vacuum as "上次手动VACUUM时间",
       last_autovacuum as "上次自动VACUUM时间",
       last_analyze as "上次手动ANALYZE时间",
       last_autoanalyze as "上次自动ANALYZE时间",
       vacuum_count as "VACUUM操作次数",
       autovacuum_count as "自动VACUUM操作次数",
       analyze_count as "ANALYZE操作次数",
       autoanalyze_count as "自动ANALYZE操作次数"
FROM pg_catalog.pg_stat_user_tables;

8.pg_stat_xact_user_tables
收集自 PostgreSQL 启动以来的每个用户事务表的统计信息，包括 COMMIT、ROLLBACK、INSERT、UPDATE、DELETE 等信息。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT relid as "表ID",
       schemaname as "所属模式",
       relname as "表名",
       seq_scan as "顺序扫描次数",
       seq_tup_read as "顺序扫描行数",
       idx_scan as "索引扫描次数",
       idx_tup_fetch as "索引扫描行数",
       n_tup_ins as "插入行数",
       n_tup_upd as "更新行数",
       n_tup_del as "删除行数",
       n_tup_hot_upd as "热更新行数"
FROM pg_catalog.pg_stat_xact_user_tables;

9.pg_locks
收集所有锁的信息，包括锁类型、锁状态、所属表等信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT locktype as "锁类型",
       database as "数据库ID",
       relation as "关系ID",
       page as "页ID",
       tuple as "行ID",
       virtualxid as "虚拟事务ID",
       transactionid as "事务ID",
       classid as "对象类别ID",
       objid as "对象ID",
       objsubid as "对象子ID",
       virtualtransaction as "虚拟事务名称",
       pid as "持有该锁的进程ID",
       mode as "锁模式",
       granted as "锁是否已授权",
       fastpath as "是否使用快捷方式"
FROM pg_catalog.pg_locks;

10.pg_statio_user_tables
收集每个用户表的 IO 统计信息，包括读取次数、写入次数、磁盘 I/O 等信息。

1
2
3
4
5
6
7
8
9
10
11
12
SELECT relid as "表ID",
       schemaname as "所属模式",
       relname as "表名",
       heap_blks_read as "堆块读取数",
       heap_blks_hit as "堆块命中数",
       idx_blks_read as "索引块读取数",
       idx_blks_hit as "索引块命中数",
       toast_blks_read as "TOAST块读取数",
       toast_blks_hit as "TOAST块命中数",
       tidx_blks_read as "TOAST索引块读取数",
       tidx_blks_hit as "TOAST索引块命中数"
FROM pg_catalog.pg_statio_user_tables;

11.根据表明查询字段信息

1
2
3
4
5
6
SELECT cols.column_name AS "字段名称", cols.data_type AS "数据类型", 
       cols.character_maximum_length AS "最大长度", cols.is_nullable AS "是否可为空", 
       cols.column_default AS "默认值"
FROM information_schema.columns cols
WHERE cols.table_name = 'pg_shadow' AND cols.table_schema = 'pg_catalog';

系统表分类

用户权限类（User Access Control）
pg_authid: 数据库用户信息表；
pg_auth_members: 数据库用户和角色归属表；
pg_database: 数据库信息表；
pg_db_role_setting: 数据库角色参数设定表；
pg_default_acl: 对象缺省权限表；
pg_namespace: 命名空间（模式）信息表；
pg_roles: 角色信息表；
pg_seclabel: 安全标记信息表；
pg_shadow: 角色密码信息表；
pg_shdescription: 共享对象描述信息表；
pg_shseclabel: 共享对象安全标记信息表；
pg_tablespace: 表空间信息表；
pg_user: 数据库用户信息表

数据库监控类（Database Monitoring）
连接信息类（Connection Information）
pg_stat_activity: 所有活跃的 SQL 连接的信息；
pg_stat_replication: 流复制进程信息；
pg_stat_subscription: 各个订阅的信息。
进程信息类（Process Information
pg_locks: 所有锁定信息；
pg_prepared_xacts: 所有的预处理事务；
pg_stat_database: 数据库级别运行统计信息；
pg_stat_database_conflicts: 数据库级别冲突信息；
pg_stat_replication_slots: 流复制插槽信息。

存储信息类（Storage Information）
pg_attribute_encoding: 字段编码信息表；
pg_attrdef: 表字段默认值信息表；
pg_class: 表、索引、视图等关系对象的信息表；
pg_constraint: 约束信息表；
pg_inherits: 继承关系信息表；
pg_largeobject: 大型对象信息表；
pg_partitioned_table: 分区表信息表；
pg_partition: 分区信息表；
pg_platter: 存储设备信息表；
pg_stat_all_indexes: 所有索引的 IO 统计信息表；
pg_stat_xact_all_tables: 所有表的事务统计信息表；
pg_stat_xact_sys_tables: 系统表的事务统计信息表；
pg_stat_all_tables: 所有表的 IO 统计信息表；
pg_stat_sys_tables: 系统表的 IO 统计信息表；
pg_statio_all_indexes: 所有索引的 IO 统计信息表；
pg_statio_all_sequences: 所有序列的 IO 统计信息表；
pg_statio_all_tables: 所有表的 IO 统计信息表；
pg_statio_sys_indexes: 系统索引的 IO 统计信息表；
pg_statio_sys_sequences: 系统序列的 IO 统计信息表；
pg_statio_sys_tables: 系统表的 IO 统计信息表；
pg_tablespace: 表空间信息表。

常用sql

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
select pid from pg_stat_activity where datname='test'; //查看数据库的链接状态

SELECT pg_terminate_backend(30382); //结束数据库链接

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r'
and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc,relname desc;  
#查询当前数据库库的表记录


ALTER USER <username> WITH PASSWORD '<new_password>';


#批量更改表所有者
SELECT
'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to onwer;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;