Re: 7 hrs for a pg_restore? - Mailing list pgsql-performance

From Douglas J Hunley
Subject Re: 7 hrs for a pg_restore?
Date
Msg-id 200802191420.11347.doug@hunley.homeip.net
Whole thread Raw
In response to Re: 7 hrs for a pg_restore?  (Jeff Davis <pgsql@j-davis.com>)
Responses Re: 7 hrs for a pg_restore?  (Jeff Davis <pgsql@j-davis.com>)
List pgsql-performance
On Tuesday 19 February 2008 13:23:23 Jeff Davis wrote:
> On Tue, 2008-02-19 at 13:03 -0500, Douglas J Hunley wrote:
> > I spent a whopping seven hours restoring a database late Fri nite for a
> > client. We stopped the application, ran pg_dump -v -Ft -b -o $db >
> > ~/pre_8.3.tar on the 8.2.x db, and then upgrading the software to 8.3. I
> > then did a pg_restore -v -d $db ./pre_8.3.tar and watched it positively
> > crawl. I'll grant you that it's a 5.1G tar file, but 7 hours seems
> > excessive.
>
> Are there lots of indexes on localized text attributes? If you have a
> big table with localized text (e.g. en_US.UTF-8), it can take a long
> time to build the indexes. If the file is 5GB compressed, I wouldn't be
> surprised if it took a long time to restore.
>
> Keep in mind, if you have several GB worth of indexes, they take up
> basically no space in the logical dump (just the "CREATE INDEX" command,
> and that's it). But they can take a lot of processor time to build up
> again, especially with localized text.
>

that could be a factor here. It is a UNICODE db, and we do a lot of text-based
indexing for the application

--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net

Be courteous to everyone, friendly to no one.

pgsql-performance by date:

Previous
From: Douglas J Hunley
Date:
Subject: Re: 7 hrs for a pg_restore?
Next
From: Dave Cramer
Date:
Subject: Re: 7 hrs for a pg_restore?