Re: Tuning postgres for fast restore?

Tino Schwarze a écrit :
> [...]
> I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
> but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
> Is there any advice to speed up restoring, postgresql.conf-wise?
>
> I already have a script which does the data loading and index creation
> in parallel. I'm looking for advice regarding shared_mem, work_mem and
> maintenance_mem - shall I raise them?
>

You should definitely raise shared_buffers and maintenance_work_mem.

> I currently have:
> shared_buffers = 240MB
> work_mem = 4096 # no units here, d'oh!
> maintenance_work_mem = 150000

When there's no unit, work_mem and maintenance_work_mem are in KB. You
didn't tell us your PostgreSQL release, but shared_buffers can be set at
least to 1GB and maintenance_work_mem to 512MB. You can also raise
checkpoint_segments.

Regards.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com