Postgresql日常维护SQL命令

PostgreSQL · ruyi · 于 1年前发布 · 670 次阅读

数据库管理相关命令


查看帮助

\?

查看数据库

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.tables where table_schema='public';

查看字段信息

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

查看视图

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

查看触发器

select * from pg_trigger;
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 = 'rpt_track_user_path_uv_mall_pkey';

查看索引

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 = 'bh_day_channel_report');

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i WHERE c.relname = 'bh_day_channel_report' 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 = 'bh_day_channel_report';
select pg_get_indexdef(b.indexrelid);  // b.indexrelid 是上表中查出的id 

查看过程函数

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

查看表大小(不含索引)

select pg_relation_size('app_day_dau_ontime');    --byte
           425984
select pg_size_pretty(pg_relation_size('app_day_dau_ontime'));   --kB
 416 kB

查看数据库大小

select pg_database_size('blackhole');
      26829013164
select pg_size_pretty(pg_database_size('blackhole'));
 25 GB

查看表空间大小

select pg_tablespace_size('pg_default');
select pg_size_pretty(pg_tablespace_size('pg_default'));

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

select * from pg_stat_database

竖向显示查询结果

\x
Expanded display is on.
select * from pg_stat_database;

查看索引使用情况

select * from pg_stat_user_indexes;

查看表对应的数据文件路径和大小

# SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'dc_wait';
pg_relation_filepath | base/16399/17301
relpages             | 3

查看表的索引与相关字段及大小

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

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 = 'dc_wait'
  ORDER BY schema_name, table_name, index_name, attname;

查看锁信息

select * from pg_locks;

本文由 ruyi 创作,采用 知识共享署名 3.0 中国大陆许可协议 进行许可。 可自由转载、引用,但需署名作者且注明文章出处。


共收到 2 条回复 Postgresql SQL命令
ruyi#11年前 0 个赞

导出表结构

pg_dump blackhole -O -s  -h127.0.0.1 -p 5432 -U postgres > blackhole.out
ruyi#211个月前 0 个赞

创建视图

CREATE OR REPLACE VIEW public.v_rpt_external_doctor_conversion AS 
 SELECT rpt_external_doctor_conversion.stat_date,
    concat(round((rpt_external_doctor_conversion.msg_uv::numeric / rpt_external_doctor_conversion.reg_uv::numeric * 100)::numeric, 2), '%') AS reg2msg_ratio,
    concat(round((rpt_external_doctor_conversion.systempay_uv::numeric / rpt_external_doctor_conversion.msg_uv::numeric * 100)::numeric, 2), '%') AS msg2systempay_ratio,
    concat(round((rpt_external_doctor_conversion.userpay_uv::numeric / nullif(rpt_external_doctor_conversion.systempay_uv::numeric,0)),2), '%') AS systempay2userpay_ratio,
    concat(round((rpt_external_doctor_conversion.nopay_uv::numeric / rpt_external_doctor_conversion.msg_uv * 100)::numeric, 2), '%') AS nopay_ratio,
    concat(round((rpt_external_doctor_conversion.total_msg_uv::numeric / rpt_external_doctor_conversion.total_reg_uv::numeric * 100)::numeric, 2), '%') AS total_reg2msg_ratio,
    concat(round((rpt_external_doctor_conversion.total_systempay_uv::numeric / rpt_external_doctor_conversion.total_msg_uv::numeric * 100)::numeric, 2), '%') AS total_msg2systempay_ratio,
    concat(round((rpt_external_doctor_conversion.total_userpay_uv::numeric / rpt_external_doctor_conversion.total_systempay_uv::numeric * 100)::numeric, 2), '%') AS total_systempay2userpay_ratio,
    concat(round((rpt_external_doctor_conversion.total_nopay_uv::numeric / rpt_external_doctor_conversion.total_msg_uv::numeric * 100)::numeric, 2), '%') AS total_nopay_ratio
   FROM rpt_external_doctor_conversion;
3楼 已删除.
4楼 已删除.
5楼 已删除.
6楼 已删除.
回复本帖 (需要登录)