delete sql 怎么恢复 postgre
发布网友
发布时间：2022-04-26 19:05
共2个回答
懂视网
时间：2022-04-12 15:24
先看下两个函数的官方解释： pg_cancel_backend() 取消后台操作，回滚未提交事物 pg_terminate_backend() 中断session，回滚未提交事物 这里和oracle类似kill session的操作是pg_terminate_backend() pg_cancel_backend() 举例： session A: postgres=# creat
先看下两个函数的官方解释：
pg_cancel_backend() 取消后台操作，回滚未提交事物 pg_terminate_backend() 中断session，回滚未提交事物 这里和oracle类似kill session的操作是pg_terminate_backend() pg_cancel_backend() 举例：
session A:
postgres=# create table tb1 (a int); CREATE TABLE postgres=# begin;
postgres=# insert into tb1 select generate_series(1,100000000);
session B：
postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+------------------------------------------------------ 12699 | postgres | 10305 | 10 | postgres | psql | | | -1 | 2013-12-03 14:38:05.887116+08 | 2013-12-03 15:12:17 .773935+08 | 2013-12-03 15:14:31.454816+08 | f | insert into tb1 select generate_series(1,100000000);
postgres=# select pg_cancel_backend(10305); pg_cancel_backend ------------------- t
session A： ERROR: canceling statement due to user request postgres=# commit; ROLLBACK postgres=# select * from tb1; a --- (0 rows)
session B:
postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+--------------------------------- 12699 | postgres | 10305 | 10 | postgres | psql | | | -1 | 2013-12-03 14:38:05.887116+08 |
pg_terminate_backend() 举例：session A：
postgres=# create table tb2 (a int); CREATE TABLE postgres=# begin; BEGIN postgres=# insert into tb2 select generate_series(1,100000000);
session B：
postgres=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+------------------------------------------------------ 12699 | postgres | 10305 | 10 | postgres | psql | | | -1 | 2013-12-03 14:38:05.887116+08 | 2013-12-03 15:24:22 .45073+08 | 2013-12-03 15:24:33.362185+08 | f | insert into tb2 select generate_series(1,100000000);
postgres=# select pg_terminate_backend(10305); pg_terminate_backend ---------------------- t (1 row)
session A：
postgres=# insert into tb2 select generate_series(1,100000000); FATAL: terminating connection due to administrator command FATAL: terminating connection due to administrator command The connection to the server was lost. Attempting reset: Succeeded. postgres=# commit; WARNING: there is no transaction in progress COMMIT postgres=# select * from tb2; a --- (0 rows)
postgres=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | waiting | current_query -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------- -----------+-------------------------------+---------+--------------------------------- 12699 | postgres | 10918 | 10 | postgres | psql | | | -1 | 2013-12-03 15:25:12.613672+08 | 2013-12-03 15:30:29 .544088+08 | 2013-12-03 15:30:29.544088+08 | f | select * from pg_stat_activity;
通过以上实验理解起来应该 很简单了，procpid=10305在pg_cancel_backend()下，session还在，事物回退，在pg_terminate_backend()操作后，session消失，事物回退。如果在某些时候pg_terminate_backend()不能杀死session，那么可以在os层面，直接kill -9 pid
热心网友
时间：2022-04-12 12:32
查看哪些SQL语句正在执行，语句如下：
SELECT datname,procpid,query_start, current_query,waiting,client_addr FROM pg_stat_activity WHERE waiting='t';
说明：
datname表示数据库名
procpid表示当前的SQL对应的PID
query_start表示SQL执行开始时间
current_query表示当前执行的SQL语句
waiting表示是否正在执行，t表示正在执行，f表示已经执行完成
client_addr表示客户端IP地址
kill有两种方式，第一种是：
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询，对update、delete 及DML不生效)
第二种是：
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作
