Обсуждение: pg_dump blocks insert update on table
Hi team,
We are using PostgreSQL 11.17
While doing pg_dumpall, it blocks insert/update on some table where copy statemnets is executed.
Request to help on immediate basis as we are facing on production system.
Thanks and regards
Bindra Bambharoliya
Bindra <11bindra11@gmail.com> writes: > We are using PostgreSQL 11.17 You realize that 11.x is out of support, right? > While doing pg_dumpall, it blocks insert/update on some table where copy > statemnets is executed. pg_dump only takes AccessShareLock on tables it dumps. That does not block inserts or updates. What may be happening is that you have some third process that is trying to take an exclusive lock on the table. It will queue up behind pg_dump's nonexclusive lock, and then other operations such as insert/update will block behind it. This is just speculation since you've provided zero detail, but that's what I'd look for. The pg_locks view could help you identify the culprit. regards, tom lane
Also same time I checked backup logs.
It is at same table. Dumping data for stage.etl_logs.
On Thu, 29 Feb 2024, 23:03 Bindra Bambharoliya, <bindra.bambharoliya@gmail.com> wrote:
Hi,I executed below query andSELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;Output I got is blocked_statement--call etl_master();current_statement_in_blocking_process-- copy stage.etl_logs(....) to stdout.Hope this helps. Let me know if more details required..Thanks & RegardsBindra BambharoliyaOn Thu, 29 Feb 2024, 22:45 Tom Lane, <tgl@sss.pgh.pa.us> wrote:Bindra <11bindra11@gmail.com> writes:
> We are using PostgreSQL 11.17
You realize that 11.x is out of support, right?
> While doing pg_dumpall, it blocks insert/update on some table where copy
> statemnets is executed.
pg_dump only takes AccessShareLock on tables it dumps. That does not
block inserts or updates. What may be happening is that you have some
third process that is trying to take an exclusive lock on the table.
It will queue up behind pg_dump's nonexclusive lock, and then other
operations such as insert/update will block behind it. This is just
speculation since you've provided zero detail, but that's what I'd
look for. The pg_locks view could help you identify the culprit.
regards, tom lane
Bindra Bambharoliya <bindra.bambharoliya@gmail.com> writes: >> Output I got is blocked_statement--call etl_master(); So what kind of lock is that waiting to acquire (blocked_locks.mode)? regards, tom lane