Re: pg_restore takes ages

On Fri, 3 Oct 2003, Vivek Khera wrote:

> >>>>> "SD" == Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> writes:
>
> SD> You can try creating index/triggers first and load the data. At the
> SD> end it will take a while before you get a usable database with either
> SD> approach but see what works faster for you.
>
> The triggers and FK's don't do much at the time they are created.
> They work upon update/insert/delete of data.
>
> SD> footprint. I dropped the table and recreated it. Also created index
> SD> before loading data. The loading was slow with this approach but it
> SD> finished in 3 hours. And I had an updated index as well. Just had to
> SD> run vacuum over it.
>
> I cannot believe that this was faster than load data followed by
> create index. Perhaps you needed to bump sort_mem so the index could
> be created more efficiently. I also find that bumping up
> checkpoint_segments to a high number speeds things up considerably.

also, turning off fsync during the load helps a lot. Be sure to turn it
back on when you're done of course.