PostgreSQL實用查詢SQL

統計了postgresql的系統表關聯的常用SQL

select * from pg_database;

postgres=# select * from pg_database;

datname	datdba	encoding	datcollate	datctype	datistemplate	datallowconn	datconnlimit	datlastsysoid	datfrozenxid	datminmxid	dattablespace	datacl
postgres	10	6	Chinese (Simplified)_People's Republic of China.936		f	t	-1	12937	549	1	1663
template1												{=c/postgres,postgres=CTc/postgres}
template0

(3 行記錄)

--檢視表空間

postgres=# select * from pg_tablespace;

spcname	spcowner	spcacl	spcoptions
pg_default
pg_global

(2 行記錄)

--檢視語言

postgres=# select * from pg_language;

lanname	lanowner	lanispl	lanpltrusted	lanplcallfoid	laninline	lanvalidator	lanacl
internal				2246
c				2247
sql				2248
plpgsql				12925	12926	12927

(4 行記錄)

--檢視角色使用者

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