Postgresql库常用系统表

---------------------pg元数据相关start---------------------
--记录表和几乎所有具有列或者像表的东西，包括索引主键之类的
select * from pg_class;
--数据库相关信息
select * from pg_database;
--表相关信息(视图)
select * from pg_tables where tablename = 'test_24';
--表字段相关信息
select * from information_schema.columns where table_name = 'test_user_p';
--表列信息attrelid=pg_class.oid,列名：attname，非空约束
select * from pg_attribute;
--检查、主键、唯一、外键和排他约束
select * from pg_constraint;
--表继承关系（分区表） inhparent：父表OID  inhrelid：子表OID
select * from pg_inherits;
--名字空间(模式),即schema
select * from pg_namespace;
--数据库角色信息(视图)  rolconnlimit最大连接数
select * from pg_roles;
--数据库授权标识符(角色)信息  --可修改角色连接数
select * from pg_authid;
update pg_authid set rolconnlimit=-1 where rolname='test';
--数据库用户(视图)  有权限访问限制
select * from pg_shadow;
--数据库用户(视图)  是pg_shadow的公共可读视图，消除了口令域
select * from pg_user;
--视图信息  提供数据库中每个视图的具体信息
select * from pg_views;
---------------------pg元数据相关end---------------------


---------------------slot相关start---------------------
--查看slot
SELECT * FROM pg_replication_slots
--创建物理slot
SELECT * FROM pg_create_physical_replication_slot('slot_1')
--创建逻辑slot
SELECT * FROM pg_create_logical_replication_slot('logical_slot_test', 'wal2json'); 
--删除slot
SELECT pg_drop_replication_slot('logical_slot_test')
---------------------slot相关end---------------------


---------------------连接数相关start---------------------
--查看过期连接
select * from pg_stat_activity where state = 'idle'
--删除连接，括号里传pid
select pg_terminate_backend(25837);
--查看最大连接数
show max_connections;
--修改最大连接数，需要superuser权限
alter system set max_connections= 1000;
--too many connections for role "test"
select * from pg_stat_activity where usename='test'
---------------------连接数相关end---------------------


---------------------查询表名、字段、主键相关start---------------------
--查询表名(含主键的)
select a.tablename as tablename from pg_tables a 
join (select bb.relname as tablename from pg_constraint aa inner join pg_class bb on aa.conrelid = bb.oid where aa.contype='p') t on a.tablename=t.tablename 
where a.schemaname='public'
order by a.tablename;

--查询表列名
select c.column_name as name,case when c.column_name=t.colname then true else false end as isKey  from information_schema.columns c 
left join (select b.relname,c.attname as colname from pg_constraint a inner join pg_class b on a.conrelid = b.oid 
inner join pg_attribute c on c.attrelid = b.oid and c.attnum = a.conkey[1] where a.contype='p') t on c.table_name=t.relname 
where c.TABLE_NAME = 'test_user_p';

--PostgreSQL分区表-父表信息查询 
SELECT nmsp_parent.nspname AS parent_schema, parent.relname AS parent,nmsp_child.nspname AS child_schema,child.relname AS child
FROM pg_inherits 
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid 
JOIN pg_class child ON pg_inherits.inhrelid = child.oid 
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace 
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace;

--查询表名并标识是否分区表及主键字段
select a.tablename,case when c.inhparent is null then false else true end isPartitionTable,t.colname as pKey from pg_tables a 
join (select bb.oid,bb.relname as tablename,cc.attname as colname from pg_constraint aa inner join pg_class bb on aa.conrelid = bb.oid inner join pg_attribute cc on cc.attrelid = bb.oid and cc.attnum = aa.conkey[1] where aa.contype='p') t on a.tablename=t.tablename 
left join (select distinct inhparent from pg_inherits) c on t.oid=c.inhparent
left join pg_inherits d on t.oid=d.inhrelid
where a.schemaname='public' and d.inhrelid is null
order by a.tablename;

--根据主表名称查询分区表名
select t.tablename parent,a.tablename child from pg_tables a
join pg_class b on a.tablename=b.relname
join pg_inherits c on b.oid=c.inhrelid
join (select bb.oid,aa.schemaname,aa.tablename from pg_tables aa join pg_class bb on aa.tablename=bb.relname) t on c.inhparent=t.oid
where t.schemaname='public' and t.tablename in ('test_user','test_user_p','parent');

---------------------查询表名、字段、主键相关end---------------------

参考：

http://postgres.cn/docs/11/catalogs.html