Re: Dump/Reload pg_statistic to cut time from pg_upgrade?

Jerry Sievers <gsievers19(at)comcast(dot)net> wrote:

> Planning to pg_upgrade some large (3TB) clusters using hard link
> method.  Run time for the upgrade itself takes around 5 minutes.
> Nice!!  Origin version 8.4 and destination version 9.1.
>
> Unfortunately the post-upgrade analyze of the entire cluster is going
> to take a minimum of 1.5 hours running several threads to analyze all
> tables.  This was measured in an R&D environment.
>
> Got to thinking, what if we make a SAN snapshot a few hours prior to
> upgrade time, upgrade that... then analyze it and then dump the stats
> table?

> Has anyone else attempted anything similar?  Any feedback is
> appreciated.

I certainly understand the motivation, and it may be a good option
if you test carefully beforehand.

What I have done in a similar situation, to minimize down time, is
to run a database ANALYZE with a very small target.  I forget the
particular value we used -- it may have been 3.  Then ran an
ANALYZE with the normal default target on a few key tables
(identified in testing to be the ones eating most of the scan time
with no statistics), and let users in.  The database ANALYZE with
the normal default target was done while normal production hit the
database, without too much of a performance hit.  With this
technique we were able to let users in with near-normal performance
with 10 or 15 minutes of down time rather than hours.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company