GP(FD)运维命令

不定时更新

0. 数据库相关

0.1 版本

select version();

0.2 虚拟内存

show gp_vmem_protect_limit;

0.3 查询会话内存

show statement_mem;
show max_statement_mem;

0.4 查询编码

show client_encoding;
show server_encoding;

1. 表相关

1.1 查询所有表

select tablename from pg_tables WHERE schemaname='ods';
Select relname as TABLE_NAME ,col_description(c.oid, 0) as COMMENTS from pg_class c
where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;

1.2 查看表的索引

select * from pg_index where indrelid in (select oid from pg_class where relname = 'table_name')
select * from pg_indexes where tablename = 'table_name';

1.3 删除所有的表

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not"current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

1.4 清空所有表

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not"current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'ods') LOOP
        EXECUTE 'truncate TABLE ' || quote_ident(r.tablename);
    END LOOP;
END $$;

1.5 删除所有的索引

-- 1.5.1 创建存储过程
CREATE OR REPLACE FUNCTION drop_all_indexes() RETURNS INTEGER AS $$
DECLARE
  i RECORD;
BEGIN
  FOR i IN 
    (SELECT relname FROM pg_class
       -- exclude all pkey, exclude system catalog which starts with 'pg_'
      WHERE relkind = 'i' AND relname NOT LIKE '%_pkey%' AND relname NOT LIKE 'pg_%')
  LOOP
    -- RAISE INFO 'DROPING INDEX: %', i.relname;
    EXECUTE 'DROP INDEX ' || i.relname;
  END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

-- 1.5.2 执行存储过程
SELECT drop_all_indexes();
-- 执行前，可注释掉EXECUTE行，取消RAISE行注释，可返回要删除掉的所有注释信息

2. 视图相关

2.1 创建物化视图

CREATE MATERIALIZED VIEW M_VIEW_NAME AS
SELECT语句

2.2 刷新物化视图

REFRESH MATERIALIZED VIEW ods.M_VIEW_NAME;

2.3 查询所有的物化视图

SELECT oid :: regclass :: text
 FROM pg_class
 WHERE relkind ='m';

2.4 删除所有的物化视图

SELECT 'DROP MATERIALIZED VIEW '||string_agg(oid :: regclass :: text ,',') AS DROPQUERY
 FROM pg_class
 WHERE relkind ='m';

2.5 删除所有视图

SELECT
	'DROP VIEW ' || string_agg ( TABLE_NAME :: TEXT, ',' ) 
FROM
	information_schema.views 
WHERE
	table_schema = 'ods';

3. 锁

3.1 查询进程

SELECT * FROM pg_stat_activity;

3.2 查询死锁

SELECT pid, T.* FROM pg_stat_activity T WHERE usename = 'gpadmin' AND datname = 'fine_data_db' AND waiting = 't';

3.3 关闭事务(一般直接用下一个)

SELECT pg_cancel_backend(procpid);

3.4 关闭事务与进程（管理员版本)

SELECT pg_terminate_backend(procpid);