Thread: 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/