public inbox for [email protected]help / color / mirror / Atom feed
Pg_dump 17+ messages / 10 participants [nested] [flat]
* Pg_dump @ 2023-12-07 18:11 Rajesh Kumar <[email protected]> 0 siblings, 1 reply; 17+ 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] 17+ messages in thread
* Re: Pg_dump @ 2023-12-07 18:13 Holger Jakobs <[email protected]> parent: Rajesh Kumar <[email protected]> 0 siblings, 2 replies; 17+ 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] 17+ messages in thread
* Re: Pg_dump @ 2023-12-07 18:26 Scott Ribe <[email protected]> parent: Holger Jakobs <[email protected]> 1 sibling, 1 reply; 17+ 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] 17+ messages in thread
* Re: Pg_dump @ 2023-12-07 18:39 jason cable <[email protected]> parent: Scott Ribe <[email protected]> 0 siblings, 0 replies; 17+ 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] 17+ messages in thread
* Re: Pg_dump @ 2023-12-07 18:52 Tom Lane <[email protected]> parent: Holger Jakobs <[email protected]> 1 sibling, 1 reply; 17+ 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] 17+ messages in thread
* Re: Pg_dump @ 2023-12-07 19:00 M Sarwar <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 17+ 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] 17+ messages in thread
* Re: Pg_dump @ 2023-12-07 19:39 Alvaro Herrera <[email protected]> parent: M Sarwar <[email protected]> 0 siblings, 0 replies; 17+ 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] 17+ messages in thread
* pg_dump @ 2024-08-12 19:09 Wasim Devale <[email protected]> 0 siblings, 2 replies; 17+ messages in thread From: Wasim Devale @ 2024-08-12 19:09 UTC (permalink / raw) To: Pgsql-admin <[email protected]>; pgsql-admin Hi All Please let me know if the below option persists while using pg_dump command: -t table_name --exclude-column column_name I have a table with a column having bytea data type (BLOBS). I just want to exclude this in the dump file as it is of 99GB but I want the other data in the same table which is of only 22MB. Please let me know any work around this. I used this option in pg_dump but that didn't work: --exclude-table-data = table_name PG version 12.19 Thanks, Wasim ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-12 19:16 David G. Johnston <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 0 replies; 17+ messages in thread From: David G. Johnston @ 2024-08-12 19:16 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Pgsql-admin <[email protected]>; pgsql-admin On Monday, August 12, 2024, Wasim Devale <[email protected]> wrote: > > > Please let me know if the below option persists while using pg_dump > command: > > -t table_name --exclude-column column_name > No, there is no option to dump a partial table, rows or columns. > > I just want to exclude this in the dump file as it is of 99GB but I want > the other data in the same table which is of only 22MB. > > Please let me know any work around this. > Write your own copy command to export that table’s contents. See the —snapshot option. David J. ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-12 19:18 Holger Jakobs <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 1 reply; 17+ messages in thread From: Holger Jakobs @ 2024-08-12 19:18 UTC (permalink / raw) To: [email protected] Am 12.08.24 um 21:09 schrieb Wasim Devale: > Hi All > > Please let me know if the below option persists while using pg_dump > command: > > -t table_name --exclude-column column_name > > I have a table with a column having bytea data type (BLOBS). I just > want to exclude this in the dump file as it is of 99GB but I want the > other data in the same table which is of only 22MB. > > Please let me know any work around this. > > I used this option in pg_dump but that didn't work: > > --exclude-table-data = table_name > > PG version 12.19 > > Thanks, > Wasim No, pg_dump can only dump complete tables. An alternative would be exporting the result of a select command via COPY or \copy to a file, which can easily be read again with COPY or \copy COPY is an SQL command and has to be used by a superuser because it writes to (or reads from) a file on the server. \copy is a psql command and thus can only be executed in psql (not any other client) and by any user, because it writes to (or reads from) a file on the client computer. Kind Regards, Holger -- Holger Jakobs, Bergisch Gladbach Attachments: [application/pgp-signature] OpenPGP_signature (203B, 2-OpenPGP_signature) download ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 06:22 Wasim Devale <[email protected]> parent: Holger Jakobs <[email protected]> 0 siblings, 2 replies; 17+ messages in thread From: Wasim Devale @ 2024-08-13 06:22 UTC (permalink / raw) To: Holger Jakobs <[email protected]>; +Cc: [email protected] I did one thing, we can exclude the toast table in the pg_dump command that has the BLOBS data for a particular table. --exclude-table-data=pg_toast.pg_toast_10176226 Thanks, Wasim On Tue, 13 Aug, 2024, 12:48 am Holger Jakobs, <[email protected]> wrote: > Am 12.08.24 um 21:09 schrieb Wasim Devale: > > Hi All > > > > Please let me know if the below option persists while using pg_dump > > command: > > > > -t table_name --exclude-column column_name > > > > I have a table with a column having bytea data type (BLOBS). I just > > want to exclude this in the dump file as it is of 99GB but I want the > > other data in the same table which is of only 22MB. > > > > Please let me know any work around this. > > > > I used this option in pg_dump but that didn't work: > > > > --exclude-table-data = table_name > > > > PG version 12.19 > > > > Thanks, > > Wasim > No, pg_dump can only dump complete tables. > > An alternative would be exporting the result of a select command via > COPY or \copy to a file, which can easily be read again with COPY or \copy > > COPY is an SQL command and has to be used by a superuser because it > writes to (or reads from) a file on the server. > \copy is a psql command and thus can only be executed in psql (not any > other client) and by any user, because it writes to (or reads from) a > file on the client computer. > > Kind Regards, > > Holger > > -- > > Holger Jakobs, Bergisch Gladbach > > ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 06:22 Wasim Devale <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 0 replies; 17+ messages in thread From: Wasim Devale @ 2024-08-13 06:22 UTC (permalink / raw) To: Holger Jakobs <[email protected]>; +Cc: [email protected] Please let me know your inputs. On Tue, 13 Aug, 2024, 11:52 am Wasim Devale, <[email protected]> wrote: > I did one thing, we can exclude the toast table in the pg_dump command > that has the BLOBS data for a particular table. > > --exclude-table-data=pg_toast.pg_toast_10176226 > > Thanks, > Wasim > > On Tue, 13 Aug, 2024, 12:48 am Holger Jakobs, <[email protected]> wrote: > >> Am 12.08.24 um 21:09 schrieb Wasim Devale: >> > Hi All >> > >> > Please let me know if the below option persists while using pg_dump >> > command: >> > >> > -t table_name --exclude-column column_name >> > >> > I have a table with a column having bytea data type (BLOBS). I just >> > want to exclude this in the dump file as it is of 99GB but I want the >> > other data in the same table which is of only 22MB. >> > >> > Please let me know any work around this. >> > >> > I used this option in pg_dump but that didn't work: >> > >> > --exclude-table-data = table_name >> > >> > PG version 12.19 >> > >> > Thanks, >> > Wasim >> No, pg_dump can only dump complete tables. >> >> An alternative would be exporting the result of a select command via >> COPY or \copy to a file, which can easily be read again with COPY or \copy >> >> COPY is an SQL command and has to be used by a superuser because it >> writes to (or reads from) a file on the server. >> \copy is a psql command and thus can only be executed in psql (not any >> other client) and by any user, because it writes to (or reads from) a >> file on the client computer. >> >> Kind Regards, >> >> Holger >> >> -- >> >> Holger Jakobs, Bergisch Gladbach >> >> ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 06:33 Tom Lane <[email protected]> parent: Wasim Devale <[email protected]> 1 sibling, 1 reply; 17+ messages in thread From: Tom Lane @ 2024-08-13 06:33 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Holger Jakobs <[email protected]>; [email protected] Wasim Devale <[email protected]> writes: > I did one thing, we can exclude the toast table in the pg_dump command that > has the BLOBS data for a particular table. > --exclude-table-data=pg_toast.pg_toast_10176226 Utterly pointless. pg_dump does not dump toast tables as such; it's only concerned with their parent "regular" tables. It will fetch data from the regular tables, and it's not concerned with the server-side implementation detail that some of that data might be coming out of a toast table. regards, tom lane ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 06:42 Wasim Devale <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 3 replies; 17+ messages in thread From: Wasim Devale @ 2024-08-13 06:42 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Holger Jakobs <[email protected]>; [email protected] Then what might be the solution for it if I am only concerned about using pg_dump On Tue, 13 Aug, 2024, 12:03 pm Tom Lane, <[email protected]> wrote: > Wasim Devale <[email protected]> writes: > > I did one thing, we can exclude the toast table in the pg_dump command > that > > has the BLOBS data for a particular table. > > --exclude-table-data=pg_toast.pg_toast_10176226 > > Utterly pointless. pg_dump does not dump toast tables as such; > it's only concerned with their parent "regular" tables. It will > fetch data from the regular tables, and it's not concerned with > the server-side implementation detail that some of that data > might be coming out of a toast table. > > regards, tom lane > ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 12:04 Ron Johnson <[email protected]> parent: Wasim Devale <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: Ron Johnson @ 2024-08-13 12:04 UTC (permalink / raw) To: Pgsql-admin <[email protected]> "Free your mind, and *pg_dump* will follow." Maybe *En Vogue*, but probably not. On Tue, Aug 13, 2024 at 2:43 AM Wasim Devale <[email protected]> wrote: > Then what might be the solution for it if I am only concerned about using > pg_dump > > On Tue, 13 Aug, 2024, 12:03 pm Tom Lane, <[email protected]> wrote: > >> Wasim Devale <[email protected]> writes: >> > I did one thing, we can exclude the toast table in the pg_dump command >> that >> > has the BLOBS data for a particular table. >> > --exclude-table-data=pg_toast.pg_toast_10176226 >> >> Utterly pointless. pg_dump does not dump toast tables as such; >> it's only concerned with their parent "regular" tables. It will >> fetch data from the regular tables, and it's not concerned with >> the server-side implementation detail that some of that data >> might be coming out of a toast table. >> >> regards, tom lane >> > -- Death to America, and butter sauce. Iraq lobster! ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 12:05 Ron Johnson <[email protected]> parent: Wasim Devale <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: Ron Johnson @ 2024-08-13 12:05 UTC (permalink / raw) To: Pgsql-admin <[email protected]> Oops... should have been "free your mind, and COPY will follow." On Tue, Aug 13, 2024 at 2:43 AM Wasim Devale <[email protected]> wrote: > Then what might be the solution for it if I am only concerned about using > pg_dump > > On Tue, 13 Aug, 2024, 12:03 pm Tom Lane, <[email protected]> wrote: > >> Wasim Devale <[email protected]> writes: >> > I did one thing, we can exclude the toast table in the pg_dump command >> that >> > has the BLOBS data for a particular table. >> > --exclude-table-data=pg_toast.pg_toast_10176226 >> >> Utterly pointless. pg_dump does not dump toast tables as such; >> it's only concerned with their parent "regular" tables. It will >> fetch data from the regular tables, and it's not concerned with >> the server-side implementation detail that some of that data >> might be coming out of a toast table. >> >> regards, tom lane >> > -- Death to America, and butter sauce. Iraq lobster! ^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: pg_dump @ 2024-08-13 12:35 David G. Johnston <[email protected]> parent: Wasim Devale <[email protected]> 2 siblings, 0 replies; 17+ messages in thread From: David G. Johnston @ 2024-08-13 12:35 UTC (permalink / raw) To: Wasim Devale <[email protected]>; +Cc: Tom Lane <[email protected]>; Holger Jakobs <[email protected]>; [email protected] <[email protected]> On Monday, August 12, 2024, Wasim Devale <[email protected]> wrote: > Then what might be the solution for it if I am only concerned about using > pg_dump > Create a new table to house the bytea data, copy the bytea data to it, drop the bytea column from the existing table. David J. ^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-08-13 12:35 UTC | newest] Thread overview: 17+ 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-12 19:09 pg_dump Wasim Devale <[email protected]> 2024-08-12 19:16 ` Re: pg_dump David G. Johnston <[email protected]> 2024-08-12 19:18 ` Re: pg_dump Holger Jakobs <[email protected]> 2024-08-13 06:22 ` Re: pg_dump Wasim Devale <[email protected]> 2024-08-13 06:22 ` Re: pg_dump Wasim Devale <[email protected]> 2024-08-13 06:33 ` Re: pg_dump Tom Lane <[email protected]> 2024-08-13 06:42 ` Re: pg_dump Wasim Devale <[email protected]> 2024-08-13 12:04 ` Re: pg_dump Ron Johnson <[email protected]> 2024-08-13 12:05 ` Re: pg_dump Ron Johnson <[email protected]> 2024-08-13 12:35 ` Re: pg_dump David G. Johnston <[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