Обсуждение: Best settings to load a fresh database

PostgreSQL 8.3
Linux RedHat 4.X
24G of memory

When loading a file generated from pg_dumpall is there a key setting in the configuration file that would allow the load to work faster.

Thanks,

Lance Campbell
Project Manager/Software Architect/DBA
Web Services at Public Affairs
217-333-0382

On Thu, Aug 6, 2009 at 12:42 PM, Campbell, Lance<lance@illinois.edu> wrote:
> PostgreSQL 8.3
> Linux RedHat 4.X
> 24G of memory
>
> When loading a file generated from pg_dumpall is there a key setting in the
> configuration file that would allow the load to work faster.

The ones I can think of are cranking up work_mem and
maintenance_work_mem and disabling fsync.  Be sure to renable fsync
afterwards if you value your data.

On Thu, Aug 06, 2009 at 01:42:06PM -0500, Campbell, Lance wrote:
> PostgreSQL 8.3
> Linux RedHat 4.X
> 24G of memory
>
> When loading a file generated from pg_dumpall is there a key setting in
> the configuration file that would allow the load to work faster.
>
> Thanks,
>
> Lance Campbell
> Project Manager/Software Architect/DBA
> Web Services at Public Affairs
> 217-333-0382
>

I have found that increasing maintenance_work_mem speeds
index rebuilds, turn off synchronous_commit or fsync if
you really can afford to start over. Another big help is
to use the parallel pg_restore from PostgreSQL 8.4.0 to
perform the restore.

Cheers,
Ken

Kenneth Marshall escreveu:
> I have found that increasing maintenance_work_mem speeds
> index rebuilds, turn off synchronous_commit or fsync if
> you really can afford to start over. Another big help is
> to use the parallel pg_restore from PostgreSQL 8.4.0 to
> perform the restore.
>
And make sure archive mode is turned off. Otherwise, you can't use the WAL
bypass facility.


--
  Euler Taveira de Oliveira
  http://www.timbira.com/