PostgreSQL实用日常维护SQL

素颜马尾好姑娘i 2022-08-09 06:23 193阅读 0赞

整理了postgresql的系统表关联的常用SQL，如下：  
\--查看数据库  
select \* from pg\_database;  
  
  
\--查看表空间  
select \* from pg\_tablespace;  
  
  
\--查看语言  
select \* from pg\_language;  
  
  
\--查看角色用户  
select \* from pg\_user;  
select \* from pg\_shadow;  
select \* from pg\_roles;  
  
  
\--查看会话进程  
select \* from pg\_stat\_activity;  
  
  
\--查看表  
SELECT \* FROM pg\_tables where schemaname = 'public';  
  
  
\--查看表字段  
select \* from information\_schema.columns where table\_schema = 'public' and table\_name = 'pf\_vip\_org';  
  
  
\--查看视图  
select \* from pg\_views where schemaname = 'public';  
select \* from information\_schema.views where table\_schema = 'public';  
  
  
\--查看触发器  
select \* from information\_schema.triggers;  
  
  
\--查看序列  
select \* from information\_schema.sequences where sequence\_schema = 'public';  
  
  
\--查看约束  
select \* from pg\_constraint where contype = 'p'  
\--u unique,p primary,f foreign,c check,t trigger,x exclusion  
  
  
select a.relname as table\_name,b.conname as constraint\_name,b.contype as constraint\_type from pg\_class a,pg\_constraint b where a.oid = b.conrelid and a.relname = 'cc';  
  
  
\--查看索引  
select \* from pg\_index ;  
  
  
\--查看表上存在哪些索引以及大小  
select relname,n.amname as index\_type from pg\_class m,pg\_am n where m.relam = n.oid and m.oid in (  
select b.indexrelid from pg\_class a,pg\_index b where a.oid = b.indrelid and a.relname = 'cc');  
  
  
SELECT c.relname,c2.relname, c2.relpages\*8 as size\_kb  
FROM pg\_class c, pg\_class c2, pg\_index i  
WHERE c.relname = 'cc' AND  
c.oid = i.indrelid AND  
c2.oid = i.indexrelid  
ORDER BY c2.relname;  
  
  
\--查看索引定义  
select b.indexrelid from pg\_class a,pg\_index b where a.oid = b.indrelid and a.relname = 'cc';  
select pg\_get\_indexdef(b.indexrelid);  
  
  
\--查看过程函数定义  
select oid,\* from pg\_proc where proname = 'insert\_platform\_action\_exist'; --oid = 24610  
select \* from pg\_get\_functiondef(24610);  
  
  
\--查看表大小(不含索引等信息)  
select pg\_relation\_size('cc'); --368640 byte  
select pg\_size\_pretty(pg\_relation\_size('cc')) --360 kB  
  
  
\--查看DB大小  
select pg\_size\_pretty(pg\_database\_size('smiletao')); --12M  
  
  
\--查看服务器DB运行状态  
\[postgres@eyar ~\]$ pg\_ctl status -D $PGDATA  
pg\_ctl: server is running (PID: 2373)  
/home/postgres/bin/postgres "-D" "/database/pgdata"  
  
  
\--查看每个DB的使用情况(读,写,缓存,更新,事务等)  
select \* from pg\_stat\_database  
  
  
\--查看索引的使用情况  
select \* from pg\_stat\_user\_indexes;  
  
  
\--查看表所对应的数据文件路径与大小  
SELECT pg\_relation\_filepath(oid), relpages FROM pg\_class WHERE relname = 'empsalary';  
  
  
\--查看索引与相关字段及大小  
SELECT n.nspname AS schema\_name,  
r.rolname as table\_owner,  
bc.relname AS table\_name,  
ic.relname AS index\_name,  
a.attname AS column\_name,  
bc.relpages\*8 as index\_size\_kb  
FROM pg\_namespace n,  
pg\_class bc, -- base class  
pg\_class ic, -- index class  
pg\_index i,  
pg\_attribute a, -- att in base  
pg\_roles r  
WHERE bc.relnamespace = n.oid  
and i.indrelid = bc.oid  
and i.indexrelid = ic.oid  
and bc.relowner = r.oid  
and i.indkey\[0\] = a.attnum  
and i.indnatts = 1  
and a.attrelid = bc.oid  
and n.nspname = 'public'  
and bc.relname = 'cc'  
ORDER BY schema\_name, table\_name, index\_name, attname;  
  
  
\--查看PG锁  
select \* from pg\_locks;  
  
  
备注：relpages\*8 是实际所占磁盘大小  
  
  
\--查看表空间大小  
select pg\_tablespace\_size('pg\_default');  
  
  
\--查看序列与表的对应关系  
WITH fq\_objects AS (SELECT c.oid,c.relname AS fqname ,  
c.relkind, c.relname AS relation  
FROM pg\_class c JOIN pg\_namespace n ON n.oid = c.relnamespace ),  
  
  
sequences AS (SELECT oid,fqname FROM fq\_objects WHERE relkind = 'S'),  
tables AS (SELECT oid, fqname FROM fq\_objects WHERE relkind = 'r' )  
SELECT  
s.fqname AS sequence,  
'->' as depends,  
t.fqname AS table  
FROM  
pg\_depend d JOIN sequences s ON s.oid = d.objid  
JOIN tables t ON t.oid = d.refobjid  
WHERE  
d.deptype = 'a' and t.fqname = 'cc';