PostgreSQL实用日常维护SQL_sql

概述转自：http://my.oschina.net/Kenyon/blog/85395?fromerr=tNxqr6NN 整理了postgresql的系统表关联的常用SQL，如下： --查看数据库select * from pg_database;--查看表空间select * from pg_tablespace;--查看语言select * from pg_language;-

转自：http://my.oschina.net/Kenyon/blog/85395?fromerr=tNxqr6NN

整理了postgresql的系统表关联的常用sql，如下：

--查看数据库select * from pg_database;--查看表空间from pg_tablespace;--查看语言from pg_language;--查看角色用户from pg_user;from pg_shadow;from pg_roles;--查看会话进程from pg_stat_activity;--查看表SELECT * FROM pg_tables where schemaname = 'public';--查看表字段from information_schema.columns where table_schema = 'public' and table_name = 'pf_vip_org';--查看视图from pg_vIEws 'public';from information_schema.vIEws --查看触发器triggers;--查看序列from information_schema.sequences where sequence_schema = 'public'; --查看约束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';--查看索引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 where a.oID = b.indrelID '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; --查看索引定义'cc';select pg_get_indexdef(b.indexrelID);--查看过程函数定义select oID,* from pg_proc where proname = 'insert_platform_action_exist'; --oID = 24610from pg_get_functiondef(24610);--查看表大小(不含索引等信息)select pg_relation_size('cc');                         --368640 byteselect 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 $PGDATApg_ctl: server is running (PID: 2373)/home/postgres/bin/postgres "-D" "/database/pgdata" --查看每个DB的使用情况(读,写,缓存,更新,事务等)from pg_stat_database --查看索引的使用情况 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*as index_size_kb FROM pg_namespace n,pg_class bc,-- base class pg_class ic,161)">-- index class pg_index i,pg_attribute a,161)">-- 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 = and bc.relname = BY schema_name,table_name,index_name,attname;--查看PG锁from pg_locks;备注：relpages*8 是实际所占磁盘大小--查看表空间大小select pg_tablespace_size('pg_default');--查看序列与表的对应关系  WITH fq_objects AS (SELECT c.oID,c.relname AS fqname,c.relkind,0)">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 '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';