Subject: Pg_dump

From: Rajesh Kumar <[email-masked]>
Date: Thu, 7 Dec 2023 23:41:50 +0530

Hi

Will pg_dump cause blocking queries? If so how to take dump without
blocking?

---

From: Holger Jakobs <[email-masked]>
Date: Thu, 7 Dec 2023 19:13:40 +0100

> Will pg_dump cause blocking queries? If so how to take dump without 

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

---

From: Scott Ribe <[email-masked]>
Date: Thu, 7 Dec 2023 11:26:15 -0700

> Readers don't block writers, writers don't block readers in PostgreSQL.

Additionally, I've done some stress testing and found that pg_dump puts surprisingly low load on our dbs. Of course, like everything else, this dependent on your specifics--after all the dump will require reading all rows, so for instance if 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.

---

From: jason cable <[email-masked]>
Date: Thu, 7 Dec 2023 18:39:29 +0000

That happens to me too the last time I took dump

Get Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: Scott Ribe <[email-masked]>
Sent: Thursday, December 7, 2023 10:26:15 AM
To: Rajesh Kumar <[email-masked]>
Cc: Pgsql-admin <[email-masked]>
Subject: Re: Pg_dump

---

From: Tom Lane <[email-masked]>
Date: Thu, 07 Dec 2023 13:52:49 -0500

> Readers don't block writers, writers don't block readers in PostgreSQL.

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

---

From: M Sarwar <[email-masked]>
Date: Thu, 7 Dec 2023 19:00:24 +0000

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 <[email-masked]>
Sent: Thursday, December 7, 2023 1:52 PM
To: Holger Jakobs <[email-masked]>
Cc: [email-masked] <[email-masked]>; [email-masked] <[email-masked]>
Subject: Re: Pg_dump

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>

                        regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Thu, 7 Dec 2023 20:39:06 +0100

> 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/[email-masked]/