Re: pg_restore 7.4.7 locks itself out

On 2006-04-10, Alban Hertroys <alban(at)magproductions(dot)nl> wrote:
> Tom Lane wrote:
>> Alban Hertroys <alban(at)magproductions(dot)nl> writes:
>>
>>>postgres 15092 0.0 0.3 43692 12924 ? D 14:11 0:00 postgres:
>>>postgres vh3_live [local] INSERT
>>
>> This process is not blocked on a lock: it's waiting for disk I/O.
>>
>> Thoughts that come to mind include (1) it's going fine and you're not
>> patient enough; (2) something wrong with your disk drive; (3) DB is
>> mounted across NFS and you're having network problems.
>
> Really? I've been waiting for it to finish ever since, amounting to
> almost 4 hours now. It doesn't seem to have progressed one bit since it
> started. Well, I'll let it run overnight and see what has happened by
> tomorrow morning.

Notice the "INSERT" there. For a restore, you'd expect it to be "COPY",
_unless_ you used the -d option to pg_dump (this is a common mistake to
make, given that all the other utilities use -d to specify the database
name).

Restoring an inserts dump is _SLOW_ to the point of being glacial, because
(unless it's a text dump and you edited in BEGIN/COMMIT statements yourself)
it'll be committing each row individually, which limits the restore speed to
a couple of hundred rows/sec unless you have evil (or battery-backed) write
caching or you've turned fsync off. COPY (which pg_dump will generate if
you didn't use -d) runs two or three orders of magnitude faster.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services