Re: pg_dump of database with numerous objects

On 2020-05-31 11:24, Adrian Klaver wrote:
> On 5/31/20 8:05 AM, tony@exquisiteimages.com wrote:
>> I have always used pg_basebackup to backup my database and I have 
>> never had any issues.
>> 
>> 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.
> 
> Where you using the jobs option?:
> 
> https://www.postgresql.org/docs/12/pgupgrade.html
> 
> -j njobs
> --jobs=njobs
> 
>     number of simultaneous processes or threads to use


Yes, I did try with this option. It did dump the postgres database at 
the same time as my main database, but my database hung in the same 
place.

> 
>> 
>> My pg_class table contains 9,000,000 entries and I have 9004 schema.
>> 
>> I was able to get output from pg_dump if I used the -n option to dump 
>> schema with wildcards. I was able to use -n 'data???x' where x was a 
>> digit from 0 to 9. This way I was able to execute 10 concurrent 
>> pg_dump processes and dump the database in 30 minutes. I then dumped 
>> the public schema and used pg_dumpall to dump the globals.
>> 
>> Can anyone tell me if there is something else I need to do to manually 
>> dump the database? What I did do seems to have restored correctly on 
>> the upgraded server, but if I want to make sure that I haven't missed 
>> anything that will creep up on me.
>> 
>>