"Burgholzer, Robert (DEQ)" <Robert(dot)Burgholzer(at)deq(dot)virginia(dot)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