From: | "Burgholzer, Robert (DEQ)" <Robert(dot)Burgholzer(at)deq(dot)virginia(dot)gov> |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | proper tuning for restoring from pg_dump in 8.3.7 |
Date: | 2010-07-14 16:33:10 |
Message-ID: | B6C40D17104BEF47B1CB85623CDFAC63895DE6@COVMSGCES-EMB13.cov.virginia.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
I am restoring a fairly sizable database from a pg_dump file (COPY FROM
STDIN style of data) -- the pg_dump file is ~40G.
My system has 4 cores, and 12G of RAM. I drop, then recreate the
database, and I do this restore via a: cat dumpfile | psql db_name. The
trouble is that my system free memory (according to top) goes to about
60M, which causes all operations on the server to grind to a halt, and
this 40G restore will take a couple hours to complete.
I noted that the restore file doesn't do anything inappropriate such as
creating indices BEFORE adding the data or anything - thus I can only
suspect that my trouble has to do with performance tuning ineptitude in
postgresql.conf.
My settings (ones that I have changed):
shared_buffers = 512MB
temp_buffers = 512MB
work_mem = 256MB
maintenance_work_mem = 64MB
max_fsm_pages = 655360
vacuum_cost_page_hit = 3
Any insight would be most appreciated.
r.b.
Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
rwburgholzer(at)deq(dot)virginia(dot)gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2010-07-14 16:57:30 | Re: proper tuning for restoring from pg_dump in 8.3.7 |
Previous Message | Silvio Brandani | 2010-07-14 14:44:51 | Re: upgrade postgres 8.1.21 to version 8.3.6 |