public inbox for [email protected]
help / color / mirror / Atom feedPg_dump
8+ messages / 8 participants
[nested] [flat]
* Pg_dump
@ 2023-12-07 18:11 Rajesh Kumar <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Rajesh Kumar @ 2023-12-07 18:11 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Hi
Will pg_dump cause blocking queries? If so how to take dump without
blocking?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pg_dump
@ 2023-12-07 18:13 Holger Jakobs <[email protected]>
parent: Rajesh Kumar <[email protected]>
1 sibling, 2 replies; 8+ messages in thread
From: Holger Jakobs @ 2023-12-07 18:13 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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
Attachments:
[application/pgp-signature] OpenPGP_signature (203B, 2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pg_dump
@ 2023-12-07 18:26 Scott Ribe <[email protected]>
parent: Holger Jakobs <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2023-12-07 18:26 UTC (permalink / raw)
To: Rajesh Kumar <[email protected]>; +Cc: Pgsql-admin <[email protected]>
> 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, 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pg_dump
@ 2023-12-07 18:39 jason cable <[email protected]>
parent: Scott Ribe <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: jason cable @ 2023-12-07 18:39 UTC (permalink / raw)
To: Scott Ribe <[email protected]>; Rajesh Kumar <[email protected]>; +Cc: Pgsql-admin <[email protected]>
That happens to me too the last time I took dump
Get Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Scott Ribe <[email protected]>
Sent: Thursday, December 7, 2023 10:26:15 AM
To: Rajesh Kumar <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Pg_dump
> 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, 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pg_dump
@ 2023-12-07 18:52 Tom Lane <[email protected]>
parent: Holger Jakobs <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-12-07 18:52 UTC (permalink / raw)
To: Holger Jakobs <[email protected]>; +Cc: [email protected]; [email protected]
Holger Jakobs <[email protected]> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pg_dump
@ 2023-12-07 19:00 M Sarwar <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2023-12-07 19:00 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Holger Jakobs <[email protected]>; +Cc: [email protected] <[email protected]>; [email protected] <[email protected]>
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 protected]>
Sent: Thursday, December 7, 2023 1:52 PM
To: Holger Jakobs <[email protected]>
Cc: [email protected] <[email protected]>; [email protected] <[email protected]>
Subject: Re: Pg_dump
Holger Jakobs <[email protected]> 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...;
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pg_dump
@ 2023-12-07 19:39 Alvaro Herrera <[email protected]>
parent: M Sarwar <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2023-12-07 19:39 UTC (permalink / raw)
To: M Sarwar <[email protected]>; +Cc: Tom Lane <[email protected]>; Holger Jakobs <[email protected]>; [email protected] <[email protected]>; [email protected] <[email protected]>
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/[email protected]/
^ permalink raw reply [nested|flat] 8+ messages in thread
* ERROR: multixact "members" limit exceeded
@ 2024-08-19 15:26 Dirk Krautschick <[email protected]>
parent: Rajesh Kumar <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dirk Krautschick @ 2024-08-19 15:26 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-08-19 15:26 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-07 18:11 Pg_dump Rajesh Kumar <[email protected]>
2023-12-07 18:13 ` Holger Jakobs <[email protected]>
2023-12-07 18:26 ` Scott Ribe <[email protected]>
2023-12-07 18:39 ` jason cable <[email protected]>
2023-12-07 18:52 ` Tom Lane <[email protected]>
2023-12-07 19:00 ` M Sarwar <[email protected]>
2023-12-07 19:39 ` Alvaro Herrera <[email protected]>
2024-08-19 15:26 ` ERROR: multixact "members" limit exceeded Dirk Krautschick <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox