postgresql实用查询sql 整理了postgresql的系统表关联的常用sql，如下： --查看数据库 www.2cto.com select * from pg_database; --查看表空间 select * from pg_tablespace; --查看语言 select * from pg_language; --查看角色用户 select * from pg_user;
postgresql实用查询sql
整理了postgresql的系统表关联的常用sql，如下：
--查看数据库 www.2cto.com
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';