postgres-后台常用命令笔记

1、断开数据库的所有连接

select pg_terminate_backend(pid) from (select pid from pg_stat_activity where datname = 'xxx' ) a;

用于删库时提示：database "xxx" is being accessed by other users 详细：There are 2 other sessions using the database. 

2、查看长事务并杀掉长事务会话

SELECT * FROM pg_stat_activity

WHERE STATE <> 'idle'

        AND pg_backend_pid () != pid

        AND ( backend_xid IS NOT NULL OR backend_xmin IS NOT NULL )

        AND EXTRACT (epoch FROM ( now() - xact_start )) > 60

ORDER BY xact_start;　　

select pg_terminate_backend(pid)；pid为上一个结果集中的pid

3、杀掉空闲语句

select pg_terminate_backend(pid) from pg_stat_activity where state='idle';

4、批量造数据

INSERT INTO tbl_name ( column1, column2,column3,column4 ) 
SELECT generate_series ( 1, 100000 ),md5( random() :: TEXT ),clock_timestamp(),RANDOM();