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've tried using the jobs option with the directory format but that seems to save each LOB separately which makes moving the resulting dump to another location unwieldy. Has anyone else had to deal with dumping a database with these many LOBs? Are there any suggestions for how to improve performance? Thanks, Wyatt
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, 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 bytes. That's *tiny*. > > Am I misunderstanding you? > >>
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, 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. > > Well, the data is there nonetheless, is it an option to convert it to > bytea before migration? > > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andr...@visena.com > www.visena.com > <https://www.visena.com> > >
