Thread: Pg_dump

Am 07.12.23 um 19:11 schrieb Rajesh Kumar:
> Hi
>
> 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.

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

> Readers don't block writers, writers don't block readers in PostgreSQL.
>
> pg_dump is a reader.
>
> --
> Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Additionally, I've done some stress testing and found that pg_dump puts surprisingly low load on our dbs. Of course,
likeeverything else, this dependent on your specifics--after all the dump will require reading all rows, so for
instanceif you're disk-bound, you could see a performance hit. But generally, if your db is running in a reasonably
"healthy"performance range and not already close to limits, pg_dump won't have a performance impact visible to users. 

Holger Jakobs <holger@jakobs.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://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

On 2023-Dec-07, M Sarwar wrote:

> 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 .

For large databases with very high rate of updates, a running pg_dump
can prevent vacuum from removing old versions of rows.  This can make
the operations slower because of accumulation of bloat.

For such situations, pg_dump is not really recommended.  It's better to
use a physical backup (say, pgbarman), or if you really need a pg_dump
output file for some reason, create a replica (with _no_
hot_standby_feedback) and run pg_dump there.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I'm always right, but sometimes I'm more right than other times."
                                                  (Linus Torvalds)
https://lore.kernel.org/git/Pine.LNX.4.58.0504150753440.7211@ppc970.osdl.org/

Hi,

a customer gets lots of

ERROR: multixact "members" limit exceeded

So they are running a vacuum now....but it seems that the error still
persists
even if the related table is already finish with vacuum.

Is there any possiblilty that a vacuum run won't solve those errors.

Thanks

Dirk