Re: pg_upgrade slowness for databases with many tables

On Fri, May 22, 2015 at 9:10 AM, Stefan Seifert <nine(at)detonation(dot)org> wrote:

> Hi,
>
> 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.

Cheers,

Jeff