Re: pg_dump of database with numerous objects

On Wed, Jun 3, 2020 at 04:10:55PM -0400, Alvaro Herrera wrote:
> On 2020-May-31, tony(at)exquisiteimages(dot)com wrote:
>
> > I am now needing to upgrade to a new version of PostgreSQL and I am running
> > into problems when pg_upgrade calls pg_dump. pg_dump stalled at: "pg_dump:
> > saving database definition" for 24 hours before I killed the process.
> >
> > My pg_class table contains 9,000,000 entries and I have 9004 schema.
>
> We've made a number of performance improvements to pg_dump so that it
> can dump databases that are "large" in several different dimensions, but
> evidently from your report it is not yet good enough when it comes to
> dumping millions of tables in thousands of schemas. It will probably
> take some profiling of pg_dump to figure out where the bottleneck is,
> and some careful optimization work in order to make it faster. Not a
> weekend job, I'm afraid :-(

FYI, we never actually found what version of pg_dump was being used,
since pg_upgrade uses the pg_dump version in the newer cluster. We only
know the user is coming _from_ 9.3.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee