Re: proper tuning for restoring from pg_dump in 8.3.7
От | Burgholzer, Robert (DEQ) |
---|---|
Тема | Re: proper tuning for restoring from pg_dump in 8.3.7 |
Дата | |
Msg-id | B6C40D17104BEF47B1CB85623CDFAC63895E51@COVMSGCES-EMB13.cov.virginia.gov обсуждение исходный текст |
Ответ на | Re: proper tuning for restoring from pg_dump in 8.3.7 ("Joshua D. Drake" <jd@commandprompt.com>) |
Ответы |
Re: proper tuning for restoring from pg_dump in 8.3.7
(David Kerr <dmk@mr-paradox.net>)
Re: proper tuning for restoring from pg_dump in 8.3.7 ("Joshua D. Drake" <jd@commandprompt.com>) Re: proper tuning for restoring from pg_dump in 8.3.7 ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) Re: proper tuning for restoring from pg_dump in 8.3.7 (Tom Lane <tgl@sss.pgh.pa.us>) Re: proper tuning for restoring from pg_dump in 8.3.7 ("Joshua D. Drake" <jd@commandprompt.com>) |
Список | pgsql-admin |
That said, the time to restore is explainable, but is there something in my tuning that is causing all of my memory to be eaten? We seem to have some undiagnosed issue whereby opening and closing large files on the system leaves a lot in the cache -- I am guessing that this is my culprit and NOT my pg tuning? Thanks again, r.b. Robert W. Burgholzer Surface Water Modeler Office of Water Supply and Planning Virginia Department of Environmental Quality rwburgholzer@deq.virginia.gov 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ -----Original Message----- From: Joshua D. Drake [mailto:jd@commandprompt.com] Sent: Wednesday, July 14, 2010 12:58 PM To: Burgholzer, Robert (DEQ) Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] proper tuning for restoring from pg_dump in 8.3.7 On Wed, 2010-07-14 at 12:33 -0400, Burgholzer, Robert (DEQ) wrote: > 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. The best you will get is ~ 22G an hour. If this is a backup you can take again in a different format, use -Fc and then use parallel restore. Even if half of the database is one table, you will still knock the restore time by 50% or so. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering
В списке pgsql-admin по дате отправления:
Предыдущее
От: "Joshua D. Drake"Дата:
Сообщение: Re: proper tuning for restoring from pg_dump in 8.3.7