[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / 幼い女性を性の奴隷にした帝國主義日本軍の?制性を公式に認め、謝罪しなければならない. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [71]
02.File Book
File 자료실 [96]
Site Link [14]
개인폴더 [200]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [25]
MySQL [47]
DB2 [79]
ORACLE [3061]
PSQL [135]
CUBRID [5]
기타정보 [170]
운영체제 [607]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: PostgreSQL

 회원가입

active session, pid, lock sql , kill pid / pg_terminate_backend
최길호 [LIST]   2023-02-06 10:50:36, 조회 : 85


https://www.postgresql.kr/docs/9.6/monitoring-stats.html
https://medium.com/29cm/db-postgresql-lock-%ED%8C%8C%ED%97%A4%EC%B9%98%EA%B8%B0-57d37ebe057

SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       pg_stat_get_backend_client_addr(s.backendid) AS ip,
       pg_stat_get_backend_activity_start(s.backendid) AS sql_start,
       pg_stat_get_backend_activity(s.backendid) AS query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s
where pg_stat_get_backend_activity_start(s.backendid) is not null;

-- 10분 이상 실행중
SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       pg_stat_get_backend_client_addr(s.backendid) AS ip,
       pg_stat_get_backend_activity_start(s.backendid) AS sql_start,
       pg_stat_get_backend_activity(s.backendid) AS query
    FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s
where pg_stat_get_backend_activity_start(s.backendid)
       < (now() - interval '10 minute')

-- wait, block pid sql 확인
SELECT blockeda.pid AS waiting_pid, blockeda.query as waiting_query,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
  left outer JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
  left outer JOIN pg_catalog.pg_locks blockingl ON
   (blockingl.transactionid = blockedl.transactionid
    AND blockedl.pid != blockingl.pid)
  left outer JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted ;

waiting_pid |                waiting_query                | blocking_pid | blocking_query
-------------+---------------------------------------------+--------------+----------------
       18872 | drop table test;                            |              |
       19087 | LOCK TABLE public.test IN ACCESS SHARE MODE |              |

$ ps -ef pid
postgres 18872 70405  0 00:01 ?        00:00:00 postgres: postgres postgres [local] DROP TABLE waiting
postgres 19087 70405  0 00:02 ?        00:00:00 postgres: postgres postgres [local] LOCK TABLE waiting

-- blocking_pid 없는경우 transaction 에서 commit,rollback 안해서 대기중
postgres=*# end;
COMMIT
postgres=# 이후 결과 사라짐.

-- SQL만 종료
select pg_cancel_backend(17126);
pg_cancel_backend
-------------------
t
(1 row)

SQL 잡고 있는 PID pg_cancel_backend ( blocking_pid ) 작동 하지 않는다. ==> pg_terminate_backend ( blocking_pid ) 해야 한다.
대기 하고 있는 PID pg_cancel_backend ( waiting_pid ) 하면
ERROR:  canceling statement due to user request
[ postgres=!# ==> !는 에러가 있음을 표시한다. ]
ERROR:  current transaction is aborted, commands ignored until end of transaction block

-- pid kill
SELECT pg_terminate_backend( pid );
pg_terminate_backend
----------------------
t
(1 row)



OS에서 kill 하면 둘다 죽는다. pg_terminate_backend 사용 하세요.
================================================================================

-- lock session kill 결과 연관된 세션도 같이 kill 된다.

SQL1> begin; delete /* sql--1 */ from test where dt= '2023-02-06 00:10:01.418017';
      select /* 마지막 실해중인 SQL이 보인다. */ count(1) from test;
SQL2> begin; delete /* sql--2 */ from test where dt= '2023-02-06 00:10:01.418017';
-- waiting

waiting_pid |                             waiting_query                             | blocking_pid |       blocking_query      
-------------+-----------------------------------------------------------------------+--------------+----------------------------
       16416 | delete /* sql--2 */ from test where dt= '2023-02-06 00:10:01.418017'; |        16368 | select /* 마지막 실해중인 SQL이 보인다. */ count(1) from test;

$ ps -ef | grep local
postgres 16368 70405  0 11:16 ?        00:00:00 postgres: postgres postgres [local] idle in transaction
postgres 16416 70405  0 11:17 ?        00:00:00 postgres: postgres postgres [local] DELETE waiting

kill -9 16368 [ blocking 세션 kill ] test 결과 : 둘다 죽는다.
----------------------------------------------------------------------------------------
16416 세션에서 아래와 같이 메시지 보이며 죽는다. [ 대기중이라 메세지가 바로 전달 ]
WARNING:  terminating connection because of crash of another server process
DETAIL:  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.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
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: Failed.

postgres=*# commit;
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=#

kill -9 16416 [ blocking 세션 kill ] test 결과 : 둘다 죽는다.
----------------------------------------------------------------------------------------
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: Failed.

postgres=*# commit;
WARNING:  terminating connection because of crash of another server process
DETAIL:  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.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
44.212.99.208


  LIST

제목 작성자 작성일 조회
Admin  pg_backup_start / pg_backup_stop / psql 세션 유지 / WARNING: aborting backup due to backend exiting before pg_backup_stop was called    최길호 2023/12/21 36
Admin  psql 10.7 oracle dblink [ oracle_fdw db link ]    최길호 2023/07/25 40
Admin  HA 복구 test    최길호 2023/03/03 59
Admin  log: COPY public.xxxx (xxx, xxx, xxx) TO stdout; / pg_dump 백업시 발생    최길호 2023/03/03 31
Admin  requested timeline x is not a child of this server's history    최길호 2023/03/02 31
Admin  recovery_target_action / archive log 받아서 계속 복구    최길호 2023/02/28 43
Admin  recovery_target_action = 'pause' / select pg_wal_replay_pause(); / select pg_get_wal_replay_pause_state();    최길호 2023/02/27 22
Admin  memory buffer cache    최길호 2023/02/06 28
Admin  active session, pid, lock sql , kill pid / pg_terminate_backend    최길호 2023/02/06 85
Admin  psql HA    최길호 2023/02/05 77
Admin  pg_ctl start -D . | postmaster.pid | /tmp/.s.PGSQL.5432 |    최길호 2023/02/03 47
Admin  pg_waldump    최길호 2023/02/03 27
Troubleshoot  $PGDATA/pg_wal/00000002.history 없는경우 [ ERROR: could not open file "pg_wal/00000002.history": ENOENT ]    최길호 2023/02/02 42
Admin  shmem_exit(0): 0 before_shmem_exit callbacks to make [ 로그 기록용 ]    최길호 2023/02/01 69
Troubleshoot  LOG: invalid resource manager ID in primary checkpoint record    최길호 2023/02/01 40
Admin  pg_restore [ 다른 계정, 다른 DB, sql 파일로 저장 ]    최길호 2023/01/30 35
Admin  pg_basebackup pg_dump pg_dumpall backup script    최길호 2023/01/30 141
Admin  session 정보    최길호 2023/01/27 39

    목록보기   다음페이지 1 [2][3][4][5][6][7]
       

Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
최근 게시물
04/12
[ORACLE]
ORA-01722: invalid number [ fi....
by 최길호
04/09
[기타정보]
아두이노 카카오톡.
by 최길호
04/08
[ORACLE]
ORA-00600: internal error code....
by 최길호