Re: dump_all/restore times?

Joe Conway <mail(at)joeconway(dot)com> writes:
> nolan(at)celery(dot)tssi(dot)com wrote:
>> Is the amount of time it is taking within reasonable limits?

> A couple of years ago I loaded a database of ~7GB on a dual ppro 200 MHz
> server (with SCSI drives). I think it took about 24 hours. Based on that
> experience, I'd say your 18 hours on a 133 Mhz machine is not bad.

It's a good idea to bump up sort_mem as high as you can before running
the restore, so that the index builds run as fast as possible. Assuming
that the new server isn't doing anything else, you could realistically
set sort_mem to maybe a quarter or a third of physical RAM for this
purpose. (Don't forget to knock it back down afterwards...)

I have a sneaking suspicion that creation of foreign key constraints may
be unreasonably inefficient during a restore, too. Have not had a
chance to check up on it though. Next time you run such a restore,
could you turn on log_statement and log_timestamp (or log_duration if
you have it) so we can see which steps take the most time?

regards, tom lane