Обсуждение: pg_dump blocks insert update on table

Поиск
Список
Период
Сортировка

pg_dump blocks insert update on table

От
Bindra
Дата:
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 

Re: pg_dump blocks insert update on table

От
Tom Lane
Дата:
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



Re: pg_dump blocks insert update on table

От
Bindra Bambharoliya
Дата:
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 and 

SELECT 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 & Regards
Bindra Bambharoliya 


On 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


Re: pg_dump blocks insert update on table

От
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