Re: proper tuning for restoring from pg_dump in 8.3.7

"Burgholzer, Robert (DEQ)" <Robert.Burgholzer@deq.virginia.gov>
wrote:

> cat dumpfile | psql db_name

Try: psql -1 -f dumpfile db_name

> The trouble is that my system free memory (according to top) goes
> to about 60M,

What do you get from free or vmstat?  (Oon't trust top too far on
memory usage reporting.)

> temp_buffers = 512MB

You don't need that for a restore (and probably not at all -- read
the docs on what it does).  I don't think that one matters for a
restore, but I would reduce it back to the default, just to be safe.

> work_mem = 256MB

That's probably too high, depending on the number of connections and
your usage pattern.  Again, probably not material for a restore.

> maintenance_work_mem = 64MB

This one matters -- it controls how much RAM is available to sort
entries during an index build.  On a single-threaded restore I would
probably set that to 1GB to 2GB, and then reduce it later.

> vacuum_cost_page_hit = 3

You normally don't want to adjust this one.  vacuum_cost_delay and
maybe vacuum_cost_limit are the useful knobs to turn in this area.

You might want to consider:

wal_buffers = 16MB

Just for the restore you might want some settings you probably don't
want in production.  They can cause database corruption if there is
a crash, but you can probably live with that during a restore -- you
just reinitialize and try again.

fsync = off
synchronous_commit = off
full_page_writes = off
archive_mode = off

Depending on your hardware, you might get a benefit from setting
checkpoint_segments, checkpoint_timeout,
checkpoint_completion_target, bgwriter_lru_maxpages and/or
bgwriter_lru_multiplier  higher.

-Kevin