PostgreSQL批量删除用户进程

概述一台开发机子上有很多的postgres用户进程，同事问想在不重启机子的情况怎么样批量地删除进程。  首先要说一下的是，postgresql是以进程的方式连接到数据库里面的，所以通常有两种方式删除进程，1是在OS层面，2是在数据库内部 一、OS上批量删除 [root@db1 kenyon]# ps -ef|grep postgres|grep idlepostgres 14184 14030 0

一台开发机子上有很多的postgres用户进程，同事问想在不重启机子的情况怎么样批量地删除进程。

首先要说一下的是，postgresql是以进程的方式连接到数据库里面的，所以通常有两种方式删除进程，1是在OS层面，2是在数据库内部

一、OS上批量删除

[root@db1 kenyon]# ps -ef|grep postgres|grep IDlepostgres 14184 14030  0 10:56 ?        00:00:00 postgres: postgres postgres [local] IDle       postgres 14206 14030  0 10:56 ?        00:00:00 postgres: postgres postgres [local] IDle       postgres 14228 14030  0 10:57 ?        00:00:00 postgres: postgres postgres [local] IDle       postgres 14230 14030  0 10:57 ?        00:00:00 postgres: postgres postgres [local] IDle       [root@db1 kenyon]# ps -ef|grep postgres|grep IDle |awk '{print }' | xargs kill[root@db1 kenyon]#su - postgres[postgres@db1 ~]$ psqlpsql (9.3.2)Type "help" for help.postgres=# select pg_backend_pID(); pg_backend_pID ----------------          14184postgres=# \dserver closed the connection unexpectedly        This probably means the server terminated abnormally        before or while processing the request.The connection to the server was lost. Attempting reset: Succeeded.--进程已经被删除，下面的再次查询已经是另外一个进程了postgres=# \dNo relations found.postgres=# select pg_backend_pID(); pg_backend_pID ----------------          14262(1 row)

二、数据库里面删除

--查询进程postgres=# select datname,pID,usename,application_name,waiting,state,query from pg_stat_activity; datname  |  pID  | usename  |    application_name     | waiting |        state        |                                         query                                          ----------+-------+----------+-------------------------+---------+---------------------+---------------------------------------------------------------------------------------- postgres | 14337 | postgres | pgadmin III - ????????? | f       | IDle                | SELECT 1 FROM pg_available_extensions WHERE name='adminpack' postgres | 14339 | postgres | psql                    | f       | active              | select pg_sleep(600); postgres | 14341 | postgres | psql                    | f       | IDle                |  postgres | 14343 | postgres | psql                    | f       | IDle in transaction | select * from tbl_kenyon limit 2; postgres | 14347 | postgres | psql                    | f       | active              | select datname,query from pg_stat_activity; postgres | 14349 | postgres | psql                    | f       | IDle                | select pg_backend_pID();(6 rows)

删除进程有两个内置函数
1.pg_cancel_backend(pID)
2.pg_terminate_backend(pID)

第一个函数只对取消查询 *** 作有效，比如上面有一个pg_sleep(600)的查询，取消 *** 作，第二个则等同于OS的kill pID

--Session Apostgres=# select pg_cancel_backend(14339); pg_cancel_backend ------------------- t(1 row)--状态变成IDLE，说明该进程是空闲状态postgres=# select datname,query from pg_stat_activity; datname  |  pID  | usename  |    application_name     | waiting |        state        |                                         query                                          ----------+-------+----------+-------------------------+---------+---------------------+---------------------------------------------------------------------------------------- postgres | 14337 | postgres | pgadmin III - ????????? | f       | IDle                | SELECT 1 FROM pg_available_extensions WHERE name='adminpack' postgres | 14339 | postgres | psql                    | f       | IDle                | select pg_sleep(600); postgres | 14341 | postgres | psql                    | f       | IDle                |  postgres | 14343 | postgres | psql                    | f       | IDle in transaction | select * from tbl_kenyon limit 2; postgres | 14347 | postgres | psql                    | f       | active              | select datname,query from pg_stat_activity; postgres | 14349 | postgres | psql                    | f       | IDle                | select pg_backend_pID();(6 rows)--Session B，取消查询并尝试取消update *** 作postgres=# select pg_backend_pID(); pg_backend_pID ----------------          14339(1 row)postgres=# select pg_sleep(600);ERROR:  canceling statement due to user requestpostgres=# begin;BEGINpostgres=# update tbl_kenyon set ID=ID+1;UPDATE 20postgres=# --Session Apostgres=# select pg_cancel_backend(14339); pg_cancel_backend ------------------- t(1 row)--可以发现状态没有改变postgres=# select datname,query from pg_stat_activity; datname  |  pID  | usename  |    application_name     | waiting |        state        |                                         query                                          ----------+-------+----------+-------------------------+---------+---------------------+---------------------------------------------------------------------------------------- postgres | 14337 | postgres | pgadmin III - ????????? | f       | IDle                | SELECT 1 FROM pg_available_extensions WHERE name='adminpack' postgres | 14339 | postgres | psql                    | f       | IDle in transaction | update tbl_kenyon set ID=ID+1; postgres | 14341 | postgres | psql                    | f       | IDle                |  postgres | 14343 | postgres | psql                    | f       | IDle in transaction | select * from tbl_kenyon limit 2; postgres | 14347 | postgres | psql                    | f       | active              | select datname,query from pg_stat_activity; postgres | 14349 | postgres | psql                    | f       | IDle                | select pg_backend_pID();(6 rows)postgres=# select pg_terminate_backend(14339); pg_terminate_backend ---------------------- t(1 row)--进程已经被杀掉了postgres=# select datname,query from pg_stat_activity; datname  |  pID  | usename  |    application_name     | waiting |        state        |                                         query                                          ----------+-------+----------+-------------------------+---------+---------------------+---------------------------------------------------------------------------------------- postgres | 14337 | postgres | pgadmin III - ????????? | f       | IDle                | SELECT 1 FROM pg_available_extensions WHERE name='adminpack' postgres | 14341 | postgres | psql                    | f       | IDle                |  postgres | 14343 | postgres | psql                    | f       | IDle in transaction | select * from tbl_kenyon limit 2; postgres | 14347 | postgres | psql                    | f       | active              | select datname,query from pg_stat_activity; postgres | 14349 | postgres | psql                    | f       | IDle                | select pg_backend_pID();(5 rows)--Session Bpostgres=# select pg_backend_pID();server closed the connection unexpectedly        This probably means the server terminated abnormally        before or while processing the request.The connection to the server was lost. Attempting reset: Succeeded.postgres=# select pg_backend_pID(); pg_backend_pID ----------------          14365(1 row)

3.批量删除

--也可以自己加条件选择删除postgres=# select pg_terminate_backend(pID)  from pg_stat_activity where pID <> pg_backend_pID() ;pg_terminate_backend ---------------------- t t t t t t(6 rows)

三、Kill -9
不要使用kill -9来删除进程，不到万一千万别使用该命令，该命令极易导致服务器进程异常，甚至奔溃。使用kill -9时系统日志输出的一些信息，有一些crash信息（shm、重启postgres.pID等），虽然还能继续使用，其实是修复好了的缘故，使用kill -9将导致其他的进程被重置或重连，有点类似断电重启。

2013-12-24 13:07:03.963 CST,14325,52b8fbc8.37f5,19,2013-12-24 11:13:12 CST,LOG,00000,"server process (PID 14549) was terminated by signal 9: Killed","Failed process was running: select pg_backend_pID();",""2013-12-24 13:07:03.964 CST,20,"terminating any other active server processes",""2013-12-24 13:07:03.966 CST,14537,52b91617.38c9,2,2013-12-24 13:05:27 CST,1/0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit,because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",""2013-12-24 13:07:03.972 CST,21,"all server processes terminated; reinitializing",""2013-12-24 13:07:03.990 CST,14551,52b91677.38d7,1,2013-12-24 13:07:03 CST,"database system was interrupted; last kNown up at 2013-12-24 13:05:27 CST","database system was not properly shut down; automatic recovery in progress",""2013-12-24 13:07:04.000 CST,3,"record with zero length at 0/1839858",4,"redo is not required",""2013-12-24 13:07:04.006 CST,22,"database system is ready to accept connections",""2013-12-24 13:07:04.007 CST,14555,52b91678.38db,2013-12-24 13:07:04 CST,"autovacuum launcher started",""2013-12-24 13:07:06.268 CST,14557,"",52b9167a.38dd,2013-12-24 13:07:06 CST,"connection received: host=[local]",""

普通的kill pID的输出

2013-12-24 13:09:03.910 CST,"postgres","[local]","IDle",2/0,FATAL,57P01,"terminating connection due to administrator command","psql"2013-12-24 13:09:03.910 CST,"disconnection: session time: 0:01:57.642 user=postgres database=postgres host=[local]","psql"

四、总结 删除postgresql的进程使用kill或者pg_terminate_backend()命令，不要使用kill -9

总结

以上是内存溢出为你收集整理的PostgreSQL批量删除用户进程全部内容，希望文章能够帮你解决PostgreSQL批量删除用户进程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。