Re: Slow dump with pg_dump/pg_restore ? How to improve ?

Soeren Gerlach <soeren(at)all-about-shift(dot)com> writes:
> So...the dump in the above format needs some 14 minutes, the restore 10
> minutes. This seems to be very slow as it means something like 100K/sec for
> dumping and restoring. The drive is cappable of 40 Meg/seconds, so thats
> not the bottleneck ,-) Anyhow postmaster and pg_dump seem to max out the
> CPU cycles as it's running at nearly 100% while dumping and restoring.

What datatypes have you got in the large tables? Also, what character
set encoding are you using?

The only reason I can think of for dump to be that slow is if conversion
of the data to text is a big time sink. This would involve the
datatype's own output routine plus possibly a character set conversion.
You should at least make sure that no character set conversion needs to
happen (offhand I think this would only be an issue if pg_dump is
invoked with PGCLIENTENCODING set in its environment).

Also I trust you are using dump with the default COPY-style output,
not dump-as-INSERTs?

regards, tom lane