Thread: Restore times

Hi

I had a converstion yesterday suggesting Postgres to an organisation
who are evaluating databases for a rewrite of their internal admin
applications. One issue which is very important to them which I don't
have any useful stats on is time taken to restore backups. Their main
database is in the 200-300GB size range and I just don't have anything
that size in production. Does anyone have stats on the time taken to
restore a database in that size range which they are willing to share?

Thanks

Bell.

On Wed, 2010-09-08 at 11:05 +0200, Alastair Turner wrote:
> Their main database is in the 200-300GB size range and I just don't
> have anything that size in production. Does anyone have stats on the
> time taken to restore a database in that size range which they are
> willing to share?

Depends on the # of indexes. Ours take 2-3 hours on a really nice SAN
storage.
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> Their main database is in the 200-300GB size range and I just don't
>> have anything that size in production. Does anyone have stats on the
>> time taken to restore a database in that size range which they are
>> willing to share?
>
> Depends on the # of indexes. Ours take 2-3 hours on a really nice SAN
> storage.

Yeah, it *really* depends on the indexes. In my experience, most of
the time of restoring a database that size is the index and constraint
creation phase. The restore times are fairly linear however, so you should
be able to restore a smaller similar database on the same hardware and
extrapolate from there. Remember to boost maintenance_work_mem and
use the new pg_restore -j flag if you can.

As far as a time guess, I'd say 2-3 hours is probably on the low end of
most restores of that size, assuming a somewhat well-indexed system
consisting of a few score tables.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201009080757
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkyHe0AACgkQvJuQZxSWSsh7jgCeLVDEpeXCfMEKR15Arm8gRkBw
izIAoPKMBPY/9r0vVZMNgLjyPtg3OKQ6
=bqlQ
-----END PGP SIGNATURE-----