Re: pg_upgrade slowness for databases with many tables

On 2015-05-22 12:34:54 -0700, Jeff Janes wrote:
> On Fri, May 22, 2015 at 9:10 AM, Stefan Seifert <nine@detonation.org> wrote:
> > upgrading a database containing > 90000 tables and about the same number of
> > views and indexes with pg_upgrade takes several hours. I've started the
> > upgrade more than five hours ago and the "Creating dump of database
> > schemas"
> > step is still not finished.

> > The database is version 9.2, pg_upgrade is version 9.4.1. In a discussion
> > at
> > lwn.net I've been told, that 9.4.1 already contains fixes for
> > inefficiencies
> > and that it shouldn't be so slow anymore: http://lwn.net/Articles/645600/
> >
> > What can I do to help improve pg_dump/pg_upgrade for my use case?
> >
>
> Unfortunately the 9.4 version of pg_dump has to run against the 9.2 server,
> where some of the improvements are not applicable.
>
> Your next upgrade should be much less painful.  But unfortunately this one
> will be slow.
>
> If it is intolerable, you could try to port
> commit eeb6f37d89fc60c6449ca12ef9e into a custom build of 9.2.
>
> This is more a topic for the performance list than for bugs.

I actually suggested -bugs... So that's my fault, if any. There've been
a number of problems with the dependency code with a larger number of
objects.

It'd be interesting to see a profile of 9.2 while a pg_dump is running,
to see what the problem is.

Greetings,

Andres Freund