Dump/Reload pg_statistic to cut time from pg_upgrade?

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Dump/Reload pg_statistic to cut time from pg_upgrade?
Date: 2013-07-08 19:20:44
Message-ID: 874nc4yi2b.fsf@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Admins;

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?

The assumption is that the DB will not have churned very much between
then and when the real upgrade is performed and the stats saved from
a few hours earlier might be good enough to go live with.

Once the real upgrade is done we'd populate the stats table of it
using the saved stats. I realize that there may be rows in the
reloaded stats table corresponding to temporary objects that may not
exist then, and would have to deal with same.

I'd immediately begin analyzing the tables as to have the best stats
once this is complete but being able to turn out a working production
instance in like 10 minutes rather than 2 hours is the goal here.

Anyway, perhaps there are other good reasons I should *not* attempt
this but it turns out that the stats table can't be reloaded with it's
own dump so this of course is a show-stopper.

psql:d:456: ERROR: cannot accept a value of type anyarray
CONTEXT: COPY pg_statistic, line 1, column stavalues1: "{_assets,_income,_liabilities,assets,income,liabilities}"

Setting allow_system_table_mods to 'on' is required also prior to
attempting the import.

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

Thanks

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Scott Ribe 2013-07-10 00:02:54 Re: PG wiki updated with query to show unindexed foreign keys
Previous Message Colin S 2013-07-08 12:57:16 Re: search_path update weirdness