Re: Restoring from PostgreSQL 9.5 dump to 10 is super slow

Hi,

On 2019-05-07 18:04:04 +0530, Siddharth Karandikar wrote:
> But before reaching there I had done following config changes
> - maintenance_work_mem 1GB
> - autovacuum off

unlikely to matter for a bulk load.

> - synchronous_commit off

unlikely to matter for a bulk load.

> - shared_buffers=4GB
>
> And finally I explicitly set wal_buffers = 128MB becasue with -1 it
> was calculating it to be just 16MB.
>
> With above configurations, pg9.5 dump of large tables could be
> restored on pg10 in just `94m31.869s` - this is huge improvement
> compared to 831m25.539s that I was getting before.
> Yay!!
>
> Still not sure why it just worked on to restore on pg9.5 without any
> of these config changes.

Ah, I bet the wal_level is different in the two clusters. The default in
9.5 wasn't usable for replication and hot backups. But the newer one
also has increased overhead in precisely one case - when loading data
into a new table. Try setting wal_level = minimal in 10.

> And also not sure if this is the best performance that one can get.
> Will making wal_buffers 512MB improve it?

Extremely unlikely.

Regards,

Andres