Postgre使用

查看正在执行的SQL

SELECT procpid, start, now() - start AS lap, current_query
FROM
    (SELECT backendid, pg_stat_get_backend_pid(S.backendid) AS procpid,
        pg_stat_get_backend_activity_start(S.backendid) AS start,
       pg_stat_get_backend_activity(S.backendid) AS current_query
    FROM
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S
    ) AS S
WHERE current_query <> '<IDLE>'
ORDER BY  lap DESC;

procpid：进程id
start：进程开始时间
lap：经过时间
current_query：执行中的sql

--------------

select * from pg_stat_activity where state = 'active' and usename = 'gf_app' order by query_start limit 1; ---慢查询

select * from pg_stat_activity where state = 'active' and query like '%CREATE INDEX%';

select datname,query from pg_stat_activity where state = 'active' and backend_start < '2021-08-31 14:27';

---------------

2.怎样停止正在执行的sql
SELECT pg_cancel_backend(进程id);

SELECT pg_terminate_backend(PID);

或者用系统函数 kill -9 进程id;

3.任务重跑过程中正在建索引，在调度杀掉后还需要干预db

4.ERROR: execute cannot be used while an asynchronous query is underway。断开连接重新执行

4.查看表结构：

SELECT 
a.attname as 字段名, 
format_type(a.atttypid,a.atttypmod) as 类型, 
a.attnotnull as 非空, 
col_description(a.attrelid,a.attnum) as 注释 
FROM pg_class as c,pg_attribute as a 
WHERE
a.attrelid = c.oid 
and a.attnum>0 
and c.relname = '表名';