博客分类：
前几天由于做9.1PIRT，postgresql.conf中的
wal_keep_segments =1000
# in logfile segments, 16MB 0 disables
wal_keep_segments值设过大，导致我的linux系统硬盘容量满了。。。。
就把pg_xlog删去，发现机器启动不了了。
在群里问了一个大牛得到了解决办法
pg_resetxlog [-f] [-n] [-ooid ] [-x xid ] [-e xid_epoch ] [-m mxid ] [-O mxoff ] [-l timelineid,fileid,seg ] datadir
i_am_birdman
浏览: 189028 次
来自: 厦门
、27日，上海浦东，Postgres中国用 ...
不错，讲的很详细。
PostgreSQL的管理启动服务pg_ctl -D /pat ...
songshuang 写道现在觉悟也不晚！加油！
加油呵呵
现在觉悟也不晚！加油！
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'> 博客详情
摘要: postgresql batch delete or logout the user connections
一台开发机子上有很多的postgres用户进程，同事问想在不重启机子的情况怎么样批量地删除进程。&
首先要说一下的是，postgresql是以进程的方式连接到数据库里面的，所以通常有两种方式删除进程，1是在OS层面，2是在数据库内部
一、OS上批量删除
[root@db1 kenyon]# ps -ef|grep postgres|grep idle
00:00:00 postgres: postgres postgres [local] idle
00:00:00 postgres: postgres postgres [local] idle
00:00:00 postgres: postgres postgres [local] idle
00:00:00 postgres: postgres postgres [local] idle
[root@db1 kenyon]# ps -ef|grep postgres|grep idle |awk '{print $2}' | xargs kill
[root@db1 kenyon]#su - postgres
[postgres@db1 ~]$ psql
psql (9.3.2)
Type "help" for help.
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
postgres=# \d
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=# \d
No relations found.
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
二、数据库里面删除
--查询进程
postgres=# select datname,pid,usename,application_name,waiting,state,query from pg_stat_
application_name
| waiting |
----------+-------+----------+-------------------------+---------+---------------------+----------------------------------------------------------------------------------------
postgres | 14337 | postgres | pgAdmin III - ????????? | f
| SELECT 1 FROM pg_available_extensions WHERE name='adminpack'
postgres | 14339 | postgres | psql
| select pg_sleep(600);
postgres | 14341 | postgres | psql
postgres | 14343 | postgres | psql
| idle in transaction | select * from tbl_kenyon limit 2;
postgres | 14347 | postgres | psql
| select datname,pid,usename,application_name,waiting,state,query from pg_stat_
postgres | 14349 | postgres | psql
| select pg_backend_pid();
(6 rows) 删除进程有两个内置函数
1.pg_cancel_backend(pid)
2.pg_terminate_backend(pid)
第一个函数只对取消查询操作有效，比如上面有一个pg_sleep(600)的查询，取消操作，第二个则等同于OS的kill pid
--Session A
postgres=# select pg_cancel_backend(14339);
pg_cancel_backend
-------------------
--状态变成IDLE，说明该进程是空闲状态
postgres=# select datname,pid,usename,application_name,waiting,state,query from pg_stat_
application_name
| waiting |
----------+-------+----------+-------------------------+---------+---------------------+----------------------------------------------------------------------------------------
postgres | 14337 | postgres | pgAdmin III - ????????? | f
| SELECT 1 FROM pg_available_extensions WHERE name='adminpack'
postgres | 14339 | postgres | psql
| select pg_sleep(600);
postgres | 14341 | postgres | psql
postgres | 14343 | postgres | psql
| idle in transaction | select * from tbl_kenyon limit 2;
postgres | 14347 | postgres | psql
| select datname,pid,usename,application_name,waiting,state,query from pg_stat_
postgres | 14349 | postgres | psql
| select pg_backend_pid();
--Session B，取消查询并尝试取消update操作
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
postgres=# select pg_sleep(600);
canceling statement due to user request
postgres=#
postgres=# update tbl_kenyon set id=id+1;
postgres=#
--Session A
postgres=# select pg_cancel_backend(14339);
pg_cancel_backend
-------------------
--可以发现状态没有改变
postgres=# select datname,pid,usename,application_name,waiting,state,query from pg_stat_
application_name
| waiting |
----------+-------+----------+-------------------------+---------+---------------------+----------------------------------------------------------------------------------------
postgres | 14337 | postgres | pgAdmin III - ????????? | f
| SELECT 1 FROM pg_available_extensions WHERE name='adminpack'
postgres | 14339 | postgres | psql
| idle in transaction | update tbl_kenyon set id=id+1;
postgres | 14341 | postgres | psql
postgres | 14343 | postgres | psql
| idle in transaction | select * from tbl_kenyon limit 2;
postgres | 14347 | postgres | psql
| select datname,pid,usename,application_name,waiting,state,query from pg_stat_
postgres | 14349 | postgres | psql
| select pg_backend_pid();
postgres=# select pg_terminate_backend(14339);
pg_terminate_backend
----------------------
--进程已经被杀掉了
postgres=# select datname,pid,usename,application_name,waiting,state,query from pg_stat_
application_name
| waiting |
----------+-------+----------+-------------------------+---------+---------------------+----------------------------------------------------------------------------------------
postgres | 14337 | postgres | pgAdmin III - ????????? | f
| SELECT 1 FROM pg_available_extensions WHERE name='adminpack'
postgres | 14341 | postgres | psql
postgres | 14343 | postgres | psql
| idle in transaction | select * from tbl_kenyon limit 2;
postgres | 14347 | postgres | psql
| select datname,pid,usename,application_name,waiting,state,query from pg_stat_
postgres | 14349 | postgres | psql
| select pg_backend_pid();
--Session B
postgres=# 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
----------------
(1 row) 3.批量删除
--也可以自己加条件选择删除
postgres=# select pg_terminate_backend(pid)
from pg_stat_activity where pid && pg_backend_pid() ;
pg_terminate_backend
----------------------
三、Kill -9
不要使用kill -9来删除进程，不到万一千万别使用该命令，该命令极易导致服务器进程异常，甚至奔溃。使用kill -9时系统日志输出的一些信息，有一些crash信息（shm、重启postgres.pid等），虽然还能继续使用，其实是修复好了的缘故，使用kill -9将导致其他的进程被重置或重连，有点类似断电重启。
13:07:03.963 CST,,,1fbc8.37f5,19,, 11:13:12 CST,,0,LOG,00000,"server process (PID 14549) was terminated by signal 9: Killed","Failed process was running: select pg_backend_pid();",,,,,,,,""
13:07:03.964 CST,,,1fbc8.37f5,20,, 11:13:12 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
13:07:03.966 CST,,,117.38c9,2,, 13:05:27 CST,1/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the curr
ent 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.",,,,,,,""
13:07:03.972 CST,,,1fbc8.37f5,21,, 11:13:12 CST,,0,LOG,00000,"all server
reinitializing",,,,,,,,,""
13:07:03.990 CST,,,177.38d7,1,, 13:07:03 CST,,0,LOG,00000,"database sy last known up at
13:05:27 CST",,,,,,,,,""
13:07:03.990 CST,,,177.38d7,2,, 13:07:03 CST,,0,LOG,00000,"database system was no automatic recovery in progress",,,,,,,,,""
13:07:04.000 CST,,,177.38d7,3,, 13:07:03 CST,,0,LOG,00000,"record with zero length at 0/1839858",,,,,,,,,""
13:07:04.000 CST,,,177.38d7,4,, 13:07:03 CST,,0,LOG,00000,"redo is not required",,,,,,,,,""
13:07:04.006 CST,,,1fbc8.37f5,22,, 11:13:12 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
13:07:04.007 CST,,,178.38db,1,, 13:07:04 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
13:07:06.268 CST,,,14557,"",52b9167a.38dd,1,"", 13:07:06 CST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"" 普通的kill pid的输出
13:09:03.910 CST,"postgres","postgres",14557,"[local]",52b9167a.38dd,3,"idle", 13:07:06 CST,2/0,0,FATAL,57P01,"terminating connection due to administrator command",,,,,,,,,"psql"
13:09:03.910 CST,"postgres","postgres",14557,"[local]",52b9167a.38dd,4,"idle", 13:07:06 CST,,0,LOG,00000,"disconnection: session time: 0:01:57.642 user=postgres database=postgres host=[local]",,,,,,,,,"psql"
四、总结 删除postgresql的进程使用kill或者pg_terminate_backend()命令，不要使用kill -9
测试2专业灌水
这个操作会把数据库的进程也干掉的
支付宝支付
微信扫码支付
打赏金额: ￥
已支付成功
打赏金额: ￥如何看待pgone复出及视频几小时后删除？ - 知乎11被浏览<strong class="NumberBoard-itemValue" title="分享邀请回答71 条评论分享收藏感谢收起HGDBPG表中添加删除列的方法
HGDB表中添加删除列：
highgo=# \d test
Table &public.test&
Column | Type | Modifiers
--------+---------+------------
id | integer | default 15
no | integer |
highgo=# select *
highgo=# alter table test add column name varchar(10);
ALTER TABLE
highgo=# select *
id | no | name
----+----+------
highgo=# insert into test (no,name) values (1,&#39;a&#39;);
INSERT 0 1
highgo=# select *
id | no | name
----+----+------
15 | 1 | a
highgo=# alter table t
ALTER TABLE
highgo=# select *&#xe625;扫码登录更安全
选择其中一个已登录的账户
选择其中一个已登录的账户
不再记住密码
会员名/邮箱/手机号
&#xe625;密码登录在这里
手机扫码，安全登录
二维码已失效
扫一扫登录
扫描成功！
请在手机上确认登录
login.n.et2}