PostgreSQL 判断大导入是否正在执行 pg_stat_activity

PostgreSQL 判断大导入还在跑吗pg_stat_activity 完整笔记ERP Staging 场景摘要大批量 ERP 资源导入Excel → erp_resource_import_staging → 合并正式表时Java 应用常因单条大 SQL 长时间无日志看似“卡死”。本文基于pg_stat_activity提供可直接复制的 SQL快速判断导入是否在执行、是否被锁阻塞、是否有空闲事务占锁并给出安全终止会话方案覆盖生产全排查场景。标签PostgreSQL、慢SQL、数据库锁、ERP导入、运维巡检、pg_stat_activity一、核心判断导入是否正在运行1. 查当前活跃 SQL首选-- 查看正在执行的语句判断大导入是否在跑SELECTpid,usename,application_name,state,wait_event_type,wait_event,now()-query_startASrunning_for,left(query,400)ASquery_previewFROMpg_stat_activityWHEREdatnamecurrent_database()ANDstateactiveANDpidpg_backend_pid()ORDERBYquery_start;2. 巡检只看运行超30秒的大导入-- 筛选长时间运行的慢导入SQLSELECTpid,now()-query_startASrunning_for,queryFROMpg_stat_activityWHEREdatnamecurrent_database()ANDstateactiveANDnow()-query_startinterval30 secondsANDpidpg_backend_pid()ORDERBYquery_start;快速判断stateactive 包含 erp_resource/staging导入正在执行running_for持续增长SQL正常运行未卡死无结果导入已结束/连接断开应用无日志属正常二、隐藏坑点空闲事务占锁必查不是活跃状态也会卡死导入idle in transaction事务已开启但未提交/未回滚长期持有锁不释放。3. 查空闲未提交事务-- 排查空闲但占着锁的事务SELECTpid,usename,state,now()-xact_startASxact_age,now()-state_changeASstate_age,left(query,200)ASlast_queryFROMpg_stat_activityWHEREdatnamecurrent_database()ANDpidpg_backend_pid()ANDstateidle in transactionORDERBYxact_start;三、导入卡住查锁与阻塞链4. 粗查是否在等锁-- 有结果 存在会话等待锁SELECT*FROMpg_locksWHERENOTgranted;5. 精查谁阻塞谁生产最常用-- 查看完整阻塞链被阻塞会话 阻塞源会话SELECTblocked_locks.pidASblocked_pid,blocked_activity.usenameASblocked_user,blocking_locks.pidASblocking_pid,blocking_activity.usenameASblocking_user,blocked_activity.queryASblocked_statement,blocking_activity.queryASblocking_statementFROMpg_catalog.pg_locks blocked_locksJOINpg_catalog.pg_stat_activity blocked_activityONblocked_activity.pidblocked_locks.pidJOINpg_catalog.pg_locks blocking_locksONblocking_locks.locktypeblocked_locks.locktypeANDblocking_locks.databaseISNOTDISTINCTFROMblocked_locks.databaseANDblocking_locks.relationISNOTDISTINCTFROMblocked_locks.relationANDblocking_locks.pageISNOTDISTINCTFROMblocked_locks.pageANDblocking_locks.tupleISNOTDISTINCTFROMblocked_locks.tupleANDblocking_locks.virtualxidISNOTDISTINCTFROMblocked_locks.virtualxidANDblocking_locks.transactionidISNOTDISTINCTFROMblocked_locks.transactionidANDblocking_locks.classidISNOTDISTINCTFROMblocked_locks.classidANDblocking_locks.objidISNOTDISTINCTFROMblocked_locks.objidANDblocking_locks.objsubidISNOTDISTINCTFROMblocked_locks.objsubidANDblocking_locks.pid!blocked_locks.pidJOINpg_catalog.pg_stat_activity blocking_activityONblocking_activity.pidblocking_locks.pidWHERENOTblocked_locks.granted;关键优先处理blocking_pid阻塞源头导入才能恢复。四、安全终止导入Kill 规范6. 取消 vs 终止命令对比命令作用后果优先级pg_cancel_backend(pid)取消当前执行的SQL语句中断事务回滚连接保留最高pg_terminate_backend(pid)强制断开数据库连接连接销毁全部未提交事务回滚最低慎用使用示例-- 温和取消推荐使用SELECTpg_cancel_backend(12345);-- 强制断开极端场景使用SELECTpg_terminate_backend(12345);⚠️注意PID 来自pg_stat_activity.pid禁止杀自己当前会话大导入在一个事务内终止会整批回滚优先顺序业务停任务 → 等待结束 →cancel→terminate五、ERP 大批量导入专属备忘重点关键词匹配语句包含erp_resource_import_staging/erp_resource/erp_resource_sku→ 100% 为 Staging 合并导入并行阻塞多条长时间 INSERT 同时运行 → 大概率重复导入/多实例并行互锁导致变慢数据一致性导入中途terminate会导致导入记录与库内数据不一致需重新执行导入六、排查小结一分钟速查看活不活用第1、2节查活跃SQL看占锁用第3节查空闲事务看卡住用第4、5节查锁与阻塞链停导入优先pg_cancel_backend少用强制终止极简速查版收藏备用-- 1. 看活跃导入SELECTpid,state,running_for,left(query,400)FROMpg_stat_activityWHEREstateactiveANDdatnamecurrent_database();-- 2. 看空闲占锁事务SELECT*FROMpg_stat_activityWHEREstateidle in transaction;-- 3. 看阻塞SELECT*FROMpg_locksWHERENOTgranted;-- 4. 安全取消SELECTpg_cancel_backend(PID);