Re: Pg_dump

I agree with Tom. This is making the difference. I ran into this scenario several times in the past.
But whole database is becoming slow when the dump is happening .
Thanks,
Sarwar

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Thursday, December 7, 2023 1:52 PM
To: Holger Jakobs <holger(at)jakobs(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>; rajeshkumar(dot)dba09(at)gmail(dot)com <rajeshkumar(dot)dba09(at)gmail(dot)com>
Subject: Re: Pg_dump

Holger Jakobs <holger(at)jakobs(dot)com> writes:
> Am 07.12.23 um 19:11 schrieb Rajesh Kumar:
>> Will pg_dump cause blocking queries? If so how to take dump without
>> blocking?

> Readers don't block writers, writers don't block readers in PostgreSQL.
> pg_dump is a reader.

To enlarge on that a bit: pg_dump takes AccessShareLock on every
table it intends to dump. This does not conflict with ordinary
DML updates. It *will* conflict with anything that wants
AccessExclusiveLock, which typically is schema-altering DDL.
See

https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Fexplicit-locking.html%23LOCKING-TABLES&data=05%7C01%7C%7Cdcf516f1dede486a176608dbf755c798%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638375720015977401%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=m%2FLUxihtuaXHgYaoa%2FTGzs0qf4Z7ifc7JptfpV0SYdI%3D&reserved=0<https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES>

So the answer to your question is "don't try to alter the
database schema while pg_dump is running". You can alter
database content freely, though.

regards, tom lane