PostgreSQL实用日常维护SQL

转载自： http://my.oschina.net/Kenyon/blog/85395

1. 查看数据库

select * from pg_database;

2. 查看表空间

select * from pg_tablespace;

3. 查看语言

select * from pg_language;

4. 查看角色用户

select * from pg_user;select * from pg_shadow;select * from pg_roles;

5. 查看会话进程

postgres=# select * from pg_stat_activity; datid | datname  | pid  | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |              query              -------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+--------------------------------- 12896 | postgres | 2212 |       10 | postgres | psql             |             |                 |          -1 | 2015-05-07 08:13:30.395563+08 |                               | 2015-05-07 11:51:33.647046+08 | 2015-05-07 11:51:33.647088+08 | f       | idle   | end; 12896 | postgres | 4896 |       10 | postgres | psql             |             |                 |          -1 | 2015-05-07 11:51:55.301628+08 |                               | 2015-05-07 12:25:39.739174+08 | 2015-05-07 12:25:39.73955+08  | f       | idle   | select func(); 12896 | postgres | 8257 |       10 | postgres | psql             |             |                 |          -1 | 2015-05-07 16:14:37.431853+08 | 2015-05-07 16:14:38.404417+08 | 2015-05-07 16:14:38.404417+08 | 2015-05-07 16:14:38.404422+08 | f       | active | select * from pg_stat_activity;(3 rows)

6. 查看表

SELECT * FROM pg_tables where schemaname = 'public';

7. 查看表字段的基本信息

postgres=# select * from information_schema.columns where table_schema = 'public' and table_name = 'tb1'; table_catalog | table_schema | table_name | column_name | ordinal_position |         column_default          | is_nullable |     data_type     | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable ---------------+--------------+------------+-------------+------------------+---------------------------------+-------------+-------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+-------------- postgres      | public       | tb1        | id          |                1 | nextval('tb1_id_seq'::regclass) | NO          | integer           |                          |                        |                32 |                       2 |             0 |                    |               |                    |                       |                      |                    |                   |                  |                |                |               |             | postgres    | pg_catalog | int4     |               |              |            |                     | 1              | NO                  | NO          |                     |                |                    |                  |                  |                | NEVER        |                       | YES postgres      | public       | tb1        | name        |                2 |                                 | YES         | character varying |                          |             1073741824 |                   |                         |               |                    |               |                    |                       |                      |                    |                   |                  |                |                |               |             | postgres    | pg_catalog | varchar  |               |              |            |                     | 2              | NO                  | NO          |                     |                |                    |                  |                  |                | NEVER        |                       | YES(2 rows)

8. 查看表字段的统计信息

postgres=# select * from pg_stats where tablename='tb1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals |  most_common_freqs  |     histogram_bounds      | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram ------------+-----------+---------+-----------+-----------+-----------+------------+------------------+---------------------+---------------------------+-------------+-------------------+------------------------+--------------------- public     | tb1       | id      | f         |         0 |         4 |         -1 |                  |                     | {1,2,3,4,5,6,7,8,9,10,11} |    0.263636 |                   |                        |  public     | tb1       | name    | f         |         0 |         3 |  -0.272727 | {aa,cc}          | {0.636364,0.272727} |                           |           1 |                   |                        | (2 rows)

9. 查看视图

postgres=# select * from pg_views where schemaname = 'public'; schemaname | viewname | viewowner |      definition       ------------+----------+-----------+----------------------- public     | view_tb1 | postgres  |  SELECT tb1.id,      +            |          |           |     tb1.name         +            |          |           |    FROM tb1          +            |          |           |   WHERE (tb1.id > 5);(1 row)

postgres=# select * from information_schema.views where table_schema = 'public'; table_catalog | table_schema | table_name |    view_definition    | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into ---------------+--------------+------------+-----------------------+--------------+--------------+--------------------+----------------------+----------------------+---------------------------- postgres      | public       | view_tb1   |  SELECT tb1.id,      +| NONE         | YES          | YES                | NO                   | NO                   | NO               |              |            |     tb1.name         +|              |              |                    |                      |                      |                |              |            |    FROM tb1          +|              |              |                    |                      |                      |                |              |            |   WHERE (tb1.id > 5); |              |              |                    |                      |                      | (1 row)

10. 查看触发器

postgres=# select * from information_schema.triggers where  event_object_table='goods_list'; trigger_catalog | trigger_schema |        trigger_name         | event_manipulation | event_object_catalog | event_object_schema | event_object_table | action_order | action_condition |              action_statement               | action_orientation | action_timing | action_reference_old_table | action_reference_new_table | action_reference_old_row | action_reference_new_row | created -----------------+----------------+-----------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+---------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+--------- postgres         | public         | tri_delete_goods_from_store | UPDATE             | postgres              | public              | goods_list         |              |                  | EXECUTE PROCEDURE delete_goods_from_store() | ROW                | AFTER         |                            |                            |                          |                          | (1 row)

11. 查看序列

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

12. 查看约束

select * from pg_constraint where contype = 'p'; --u unique,p primary,f foreign,c check,t trigger,x exclusionselect 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';

13. 查看索引

select * from pg_index ;

14. 查看索引的使用信息

select * from pg_stat_user_indexes;

15. 查看表上存在哪些索引以及大小

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 = 'tb1');SELECT c.relname,c2.relname, c2.relpages*8 as size_kbFROM pg_class c, pg_class c2, pg_index iWHERE c.relname = 'tb7' ANDc.oid = i.indrelid ANDc2.oid = i.indexrelidORDER BY c2.relname; 

16. 查看索引定义

select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'tb7';select pg_get_indexdef(b.indexrelid);postgres=# select pg_get_indexdef('idx_tb7_id'::regclass);                 pg_get_indexdef                 ------------------------------------------------- CREATE INDEX idx_tb7_id ON tb7 USING btree (id)(1 row)

17. 查看过程函数定义

select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610select * from pg_get_functiondef(24610);

postgres=# select oid from pg_proc where proname='f_test';  oid  ------- 57414 57404(2 rows)postgres=# select pg_get_functiondef(57404);                 pg_get_functiondef                  ----------------------------------------------------- CREATE OR REPLACE FUNCTION public.f_test(i integer)+  RETURNS void                                      +  LANGUAGE plpgsql                                  + AS $function$                                      + begin                                              + update tb1 set name='cc' where id=i;               + end;                                               + $function$                                         +(1 row)

18. 查看视图定义

postgres=# select pg_get_viewdef('view_tb1');    pg_get_viewdef     -----------------------  SELECT tb1.id,      +     tb1.name         +    FROM tb1          +   WHERE (tb1.id > 5);(1 row)

19. 查看表大小(不含索引等信息)

select pg_relation_size('cc');                         --368640 byteselect pg_size_pretty(pg_relation_size('cc'))   --360 kB

20. 查看DB大小

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

21. 查看服务器DB运行状态

[postgres@eyar ~]$ pg_ctl status -D $PGDATApg_ctl: server is running (PID: 2373)/home/postgres/bin/postgres "-D" "/database/pgdata" 

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

select * from pg_stat_database;

23. 查看索引的使用情况

select * from pg_stat_user_indexes;

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

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

25. 查看索引与相关字段及大小

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;

26. 查看PG锁

select * from pg_locks;

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

27. 查看表空间大小

select pg_tablespace_size('pg_default');

28. 查看序列与表的对应关系

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