Re: Optimize pg_dump schema-only

I'm afraid it is 9.2.4. I'm using pg_upgrade from 9.6 and that is using
pg_dump from 9.6.

I noticed on 2 installations with similar table numbers (~200,000),
schema and hardware that one was done in hours and the other didn't
finish over the weekend. Keeping tabs on pg_stat_activity indicated
pg_dump was still processing and nothing else running.

Would you say that updating to 9.2.24 would be beneficial before
upgrading to 9.6? An update is pretty quick and could be worth the time
if there aren't additional requirements prior to starting the upgrade.

Thank you.
Senor

On 4/28/2019 18:19, Tom Lane wrote:
> senor <frio_cervesa(at)hotmail(dot)com> writes:
>> I know from a previous post to the community that pg_dump --schema-only is single threaded and the --jobs option cannot benefit pg_dump in my case (single DB, Single schema, 100000+ tables). Using pg_upgrade with the --link option is very fast except for the pg_dump portion of the upgrade which takes days.
> One simple question is whether the source server is the last available
> minor release (9.2.24 I believe). If not, you may be missing performance
> fixes that would help. pg_dump -s on 100K tables should not take "days",
> so I'm guessing you're hitting some O(N^2) behavior somewhere, and it
> might be something we fixed.
>
> Likewise make sure that pg_dump is the newest available in the destination
> release series.
>
> regards, tom lane