Re: Fastest way to restore a database

On Friday 12 September 2008 15:55:46 Tom Lane wrote:
> Scott Ribe <scott_ribe(at)killerbytes(dot)com> writes:
> >> The worry expressed upthread about the transaction being "too large" is
> >> unfounded, btw. Unlike some other DBs, PG doesn't have a finite-size
> >> undo log.
> >
> > Sure, it won't fail. But would there be some point at which it would
> > become slower than multiple transactions? Or is it always faster (or at
> > least as fast)?
>
> I can't think of any reason it would be slower.
>
> There are certainly issues you could run into with very long
> transactions, like vacuum not being able to remove bloat elsewhere.
>

Which reminds me (and not seeing it elsewhere), on full restores you will
probably want to disable autovacuum entirely, as it will compete for
reasources and can lead to locking issues as well. Note, this can sometimes
apply to more narrow restore scenarios, but it isnt as cut and dried. (Ie,
with multiple database in a cluster, you dont want to disable it for all
databases, though it'd be nice to disable it for the one you're restoring)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL