PostgreSQL实用SQL

--查看数据库

002
select * from pg_database;

003

004
--查看表空间

005
select * from pg_tablespace;

006

007
--查看语言

008
select * from pg_language;

009

010
--查看角色用户

011
select * from pg_user;

012
select * from pg_shadow;

013
select * from pg_roles;

014

015
--查看会话进程

016
select * from pg_stat_activity;

017

018
--查看表

019
SELECT * FROM pg_tables where schemaname = 'public';

020

021
--查看表字段

022
select * from information_schema.columns where table_schema = 'public' and table_name = 'pf_vip_org';

023

024
--查看视图

025
select * from pg_views where schemaname = 'public';

026
select * from information_schema.views where table_schema = 'public';

027

028
--查看触发器

029
select * from information_schema.triggers;

030

031
--查看序列

032
select * from information_schema.sequences where sequence_schema = 'public';

033

034
--查看约束

035
select * from pg_constraint where contype = 'p'

036
--u unique,p primary,f foreign,c check,t trigger,x exclusion

037

038
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';

039

040
--查看索引

041
select * from pg_index ;

042

043
--查看表上存在哪些索引以及大小

044
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (

045
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');

046

047
SELECT c.relname,c2.relname, c2.relpages*8 as size_kb

048
FROM pg_class c, pg_class c2, pg_index i

049
WHERE c.relname = 'cc' AND

050
c.oid = i.indrelid AND

051
c2.oid = i.indexrelid

052
ORDER BY c2.relname;

053

054
--查看索引定义

055
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';

056
select pg_get_indexdef(b.indexrelid);

057

058
--查看过程函数定义

059
select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610

060
select * from pg_get_functiondef(24610);

061

062
--查看表大小(不含索引等信息)

063
select pg_relation_size('cc');                         --368640 byte

064
select pg_size_pretty(pg_relation_size('cc'))   --360 kB

065

066
--查看DB大小

067
select pg_size_pretty(pg_database_size('smiletao'));   --12M

068

069
--查看服务器DB运行状态

070
[postgres@eyar ~]$ pg_ctl status -D $PGDATA

071
pg_ctl: server is running (PID: 2373)

072
/home/postgres/bin/postgres "-D" "/database/pgdata"

073

074
--查看每个DB的使用情况(读,写,缓存,更新,事务等)

075
select * from pg_stat_database

076

077
--查看索引的使用情况

078
select * from pg_stat_user_indexes;

079

080
--查看表所对应的数据文件路径与大小

081
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';

082

083
--查看索引与相关字段及大小

084
SELECT n.nspname AS schema_name,

085
r.rolname as table_owner,

086
bc.relname AS table_name,

087
ic.relname AS index_name,

088
a.attname  AS column_name,

089
bc.relpages*8 as index_size_kb   

090
FROM pg_namespace n,

091
pg_class bc,             -- base class

092
pg_class ic,             -- index class

093
pg_index i,

094
pg_attribute a,           -- att in base

095
pg_roles r

096
WHERE bc.relnamespace = n.oid

097
and i.indrelid = bc.oid

098
and i.indexrelid = ic.oid

099
and bc.relowner = r.oid

100
and i.indkey[0] = a.attnum

101
and i.indnatts = 1

102
and a.attrelid = bc.oid

103
and n.nspname = 'public'

104
and bc.relname = 'cc'

105
ORDER BY schema_name, table_name, index_name, attname;

106

107
--查看PG锁

108
select * from pg_locks;

109

110
备注：relpages*8 是实际所占磁盘大小

111

112
--查看表空间大小

113
select pg_tablespace_size('pg_default');

114

115
--查看序列与表的对应关系

116
WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,

117
c.relkind, c.relname AS relation

118
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),

119

120
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),

121
tables    AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )

122
SELECT

123
s.fqname AS sequence,

124
'->' as depends,

125
t.fqname AS table

126
FROM

127
pg_depend d JOIN sequences s ON s.oid = d.objid

128
JOIN tables t ON t.oid = d.refobjid

129
WHERE

130
d.deptype = 'a' and t.fqname = 'cc';

转载于:https://my.oschina.net/progresQi/blog/224193