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@xxxxxxxxxxxxxxxx 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ -----Original Message----- From: Joshua D. Drake [mailto:jd@xxxxxxxxxxxxxxxxx] Sent: Wednesday, July 14, 2010 12:58 PM To: Burgholzer, Robert (DEQ) Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: 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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
