Re: Improving pg_dump performance when handling large numbers of LOBs

Might it be worth a modest amount of time using some basic profiling to see where the time is going? A week is a looonnngg time, even for 150e6 operations. For example, if there an unexpectedly high IO load, some temporary M.2 storage might help? On Tue, 6 Feb 2024, 01:36 Ron Johnson, wrote: > O

Re: Improving pg_dump performance when handling large numbers of LOBs

On 2/5/24 12:32, Ken Marshall wrote: On Mon, Feb 05, 2024 at 09:17:53PM +0100, Andreas Joseph Krogh wrote: På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis < wyatt.tel...@gmail.com >: No, we don't have the ability to make schema changes and the schema

Re: Improving pg_dump performance when handling large numbers of LOBs

Wyatt Tellis writes: > Yes, the LOBs themselves are tiny, but there are a lot of them (~150 > million) which seem to be slowing down pg_dump. Note, we did not > design/build this system and agree that use of LOBs for this purpose was > not necessary. I don't know of anything much you can do at t

Re: Improving pg_dump performance when handling large numbers of LOBs

On Mon, Feb 05, 2024 at 09:17:53PM +0100, Andreas Joseph Krogh wrote: > > På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis < > wyatt.tel...@gmail.com >: > > No, we don't have the ability to make schema changes and the schema in the > PG15 copy needs to m

Re: Improving pg_dump performance when handling large numbers of LOBs

På mandag 05. februar 2024 kl. 21:10:53, skrev Wyatt Tellis < wyatt.tel...@gmail.com >: No, we don't have the ability to make schema changes and the schema in the PG15 copy needs to match what's in the PG 12 versions Well then, I guess it boils down to how badly

Re: Improving pg_dump performance when handling large numbers of LOBs

Just checking... and I sympathize with your situation. On Mon, Feb 5, 2024 at 2:56 PM Wyatt Tellis wrote: > Yes, the LOBs themselves are tiny, but there are a lot of them (~150 > million) which seem to be slowing down pg_dump. Note, we did not > design/build this system and agree that use of LO

Re: Improving pg_dump performance when handling large numbers of LOBs

No, we don't have the ability to make schema changes and the schema in the PG15 copy needs to match what's in the PG 12 versions Wyatt On Mon, Feb 5, 2024 at 12:05 PM Andreas Joseph Krogh wrote: > På mandag 05. februar 2024 kl. 20:55:43, skrev Wyatt Tellis < > wyatt.tel...@gmail.com>: > > Yes,

Re: Improving pg_dump performance when handling large numbers of LOBs

På mandag 05. februar 2024 kl. 20:55:43, skrev Wyatt Tellis < wyatt.tel...@gmail.com >: Yes, the LOBs themselves are tiny, but there are a lot of them (~150 million) which seem to be slowing down pg_dump. Note, we did not design/build this system and agree that u

Re: Improving pg_dump performance when handling large numbers of LOBs

Yes, the LOBs themselves are tiny, but there are a lot of them (~150 million) which seem to be slowing down pg_dump. Note, we did not design/build this system and agree that use of LOBs for this purpose was not necessary. Wyatt On Mon, Feb 5, 2024 at 11:36 AM Ron Johnson wrote: > On Mon, Feb 5

Re: Improving pg_dump performance when handling large numbers of LOBs

On 2/5/24 11:35 AM, Ron Johnson wrote: On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis wrote: Hi, We've inherited a series of legacy PG 12 clusters that each contain a database that we need to migrate to a PG 15 cluster. Each database contains about 150 million large objects tota

Re: Improving pg_dump performance when handling large numbers of LOBs

On Mon, Feb 5, 2024 at 2:01 PM Wyatt Tellis wrote: > Hi, > > We've inherited a series of legacy PG 12 clusters that each contain a > database that we need to migrate to a PG 15 cluster. Each database contains > about 150 million large objects totaling about 250GB. > 250*10^9 / (150*10^6) = 1667

Improving pg_dump performance when handling large numbers of LOBs

Hi, We've inherited a series of legacy PG 12 clusters that each contain a database that we need to migrate to a PG 15 cluster. Each database contains about 150 million large objects totaling about 250GB. When using pg_dump we've found that it takes a couple of weeks to dump out this much data. We'

RE: Improving pg_dump performance

-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Monday, July 23, 2018 8:56 AM To: Ron Cc: pgsql-general@lists.postgresql.org Subject: Re: Improving pg_dump performance On 07/23/2018 06:47 AM, Ron wrote: > On 07/23/2018 08:46 AM, Stephen Frost wr

Re: Improving pg_dump performance

On 2018-07-23 09:17:41 -0500, Ron wrote: > On 07/23/2018 09:11 AM, Andres Freund wrote: > > Hi, > > > > On 2018-07-23 02:23:45 -0500, Ron wrote: > > > We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that > > > needs to be migrated to a new data center and then restored to v9.6.

Re: Improving pg_dump performance

On 07/23/2018 09:11 AM, Andres Freund wrote: Hi, On 2018-07-23 02:23:45 -0500, Ron wrote: We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. Have you considered using pg_upgrade instead? Yes, but: 1.

Re: Improving pg_dump performance

On 07/23/2018 08:56 AM, Adrian Klaver wrote: On 07/23/2018 06:47 AM, Ron wrote: On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from a 9.6 syste

Re: Improving pg_dump performance

Hi, On 2018-07-23 02:23:45 -0500, Ron wrote: > We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that > needs to be migrated to a new data center and then restored to v9.6.9. Have you considered using pg_upgrade instead? Greetings, Andres Freund

Re: Improving pg_dump performance

On 07/23/2018 06:47 AM, Ron wrote: On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from a 9.6 system? Yes, you can do a parallel backup, but you wo

Re: Improving pg_dump performance

On 07/23/2018 08:46 AM, Stephen Frost wrote: Greetings, * Ron (ronljohnso...@gmail.com) wrote: An interesting idea.  To clarify: it's possible to parallel backup a running 8.4 cluster remotely from a 9.6 system? Yes, you can do a parallel backup, but you won't be able to get a consistent snaps

Re: Improving pg_dump performance

Greetings, * Ron (ronljohnso...@gmail.com) wrote: > An interesting idea.  To clarify: it's possible to parallel backup a running > 8.4 cluster remotely from a 9.6 system? Yes, you can do a parallel backup, but you won't be able to get a consistent snapshot. You'll need to pause all changes to th

Re: Improving pg_dump performance

On 07/23/2018 08:27 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 15:06 schrieb Ron: On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data cente

Re: Improving pg_dump performance

On 07/23/2018 12:23 AM, Ron wrote: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. The database has many large tables full of bytea columns containing pdf images, and so the dump file is going

Re: Improving pg_dump performance

Greetings, * Ron (ronljohnso...@gmail.com) wrote: > We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that > needs to be migrated to a new data center and then restored to v9.6.9. You should be using 9.6's pg_dump to perform the export. Might be a bit annoying to do, but you sh

Re: Improving pg_dump performance

Am 23.07.2018 um 15:06 schrieb Ron: On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you can use th

Re: Improving pg_dump performance

On 07/23/2018 02:32 AM, Andreas Kretschmer wrote: Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you can use the pg_dump from the newer version (9.6) to

Re: Improving pg_dump performance

Am 23.07.2018 um 09:23 schrieb Ron: Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. you can use the pg_dump from the newer version (9.6) to dump the old database, over the net. In this wa

Improving pg_dump performance

Hi, We've got an old (v8.4.17, thus no parallel backups) 2.9TB database that needs to be migrated to a new data center and then restored to v9.6.9. The database has many large tables full of bytea columns containing pdf images, and so the dump file is going to be more than 2x larger than the