pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump blocks insert update on table
4+ messages / 3 participants
[nested] [flat]
* pg_dump blocks insert update on table
@ 2024-02-29 15:06 Bindra <11bindra11@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Bindra @ 2024-02-29 15:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_dump blocks insert update on table
@ 2024-02-29 17:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bindra <11bindra11@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2024-02-29 17:14 UTC (permalink / raw)
To: Bindra <11bindra11@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_dump blocks insert update on table
@ 2024-02-29 17:37 Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Bindra Bambharoliya @ 2024-02-29 17:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin@lists.postgresql.org
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
>>
>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_dump blocks insert update on table
@ 2024-02-29 17:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-02-29 17:59 UTC (permalink / raw)
To: Bindra Bambharoliya <bindra.bambharoliya@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-02-29 17:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-29 15:06 pg_dump blocks insert update on table Bindra <11bindra11@gmail.com>
2024-02-29 17:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-29 17:37 ` Bindra Bambharoliya <bindra.bambharoliya@gmail.com>
2024-02-29 17:59 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox