What parameters to tweak for a text-based PG restore?

Every night we dump and restore a 200 GB database using:

# Production, PG 9:
pg_dump DATNAME | some-irrelevant-pipe

# QA, PG 8.3:
some-irrelevant-pipe | psql -d DATNAME

I had to go for text-based backups in order to restore a dump from 9 on 8.3.

The restore is painfully and unreasonably slow. I noticed my log is full of these:

2011-05-22 08:02:47 CDT LOG:  checkpoints are occurring too frequently (9 seconds apart)
2011-05-22 08:02:47 CDT HINT:  Consider increasing the configuration parameter "checkpoint_segments".
2011-05-22 08:02:54 CDT LOG:  checkpoints are occurring too frequently (7 seconds apart)
2011-05-22 08:02:54 CDT HINT:  Consider i开发者_运维百科ncreasing the configuration parameter "checkpoint_segments".

My question is: Is it possible that the setting of checkpoint_segments is the bottleneck? What other parameters can I tweak to speed up the process?

That machine has 4 GB RAM. Other possibly relevant settings in postgresql.conf are:

shared_buffers = 1000MB
work_mem = 200MB
maintenance_work_mem = 200MB
effective_cache_size = 2000MB
# fsync and checkpoint settings are default

Did you read this ? See specially sec 14.4.9

For the purposes of restoring a database, change:

# I don't think PostgreSQL 8.3 supports synchronous_commit
synchronous_commit = off
# only change fsync = off if your version of PG is too old to support synchronous_commit. If you do support synchronous_commit, don't ever change fsync to anything but on. Ever.
#fsync = off
checkpoint_segments = 25

Regarding checkpoint_segments, set that value to the size of your disk controller's write buffer. 25 = 400MB

Also, make sure your psql is loading everything in a single transaction:

some-irrelevant-pipe | psql -1 -d DATNAME