查看正在执行的sql
select * from pg_stat_activity ;
停止正在执行的sql
select pg_cancel_backend(pid) kill -9 进程id
greenplum查看集群状态
gpstate -e
greenplum集群恢复
主备对调
gprecoverseg -r
强制恢复集群
gprecoverseg -F
#greenplum启动重启关闭 gpstart 常用参数 -a 无需终端输入yes确认 -m 只启动master节点 gpstop 常用参数 -u 不停止数据库 只重新加载配置参数如pg_hba_conf、postgres.conf -f 强制停止数据库 -r 重启数据库
批量删除表
CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE
row record;
BEGIN
FOR row IN
SELECT
table_schema,
table_name
FROM
information_schema.tables
WHERE
table_type = 'BASE TABLE'
AND
table_schema = _schema
AND
table_name ILIKE ('%test%')
LOOP
EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE ';
RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
END LOOP;
END;
$$;
SELECT footgun('public', 'tablename');
