Arshavir Grigorian <ag@m-cam.com> writes:
> Tom Lane wrote:
>> How are you measuring that write rate (seeing that pg_restore doesn't
>> provide any such info)?
> Well, if the restore is going on for X number of hours and you have Y
> records loaded, it's not hard to ballpark.
Yeah, but how do you know that you have Y records loaded?
What I'm trying to get at is what the restore is actually spending its
time on. It seems unlikely that a COPY per se would run that slowly;
far more likely that the expense is involved with index construction
or foreign key verification. You could possibly determine what's what
by watching the backend process with "ps" to see what statement type
it's executing most of the time.
BTW, is this a full database restore (schema + data), or are you trying
to load data into pre-existing tables? The latter is generally a whole
lot slower because both index updates and foreign key checks have to be
done retail instead of wholesale. There are various ways of working
around that but you have to be aware of what you're doing.
Also, if it is indexing that's eating the time, boosting the sort_mem
setting for the server would help a lot.
regards, tom lane