Re: proper tuning for restoring from pg_dump in 8.3.7

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(at)deq(dot)virginia(dot)gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/

-----Original Message-----
From: Joshua D. Drake [mailto:jd(at)commandprompt(dot)com]
Sent: Wednesday, July 14, 2010 12:58 PM
To: Burgholzer, Robert (DEQ)
Cc: pgsql-admin(at)postgresql(dot)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