Re: autovacuum question

Wish I could Tom. I need a non-production, read-write copy of the
database that is updated every 1-2 hours from production. I don't set
this requirement, the business does. I just have to do it if it's
technically possible.

I found a way to do it very easily using LVM snapshots and WAL log
shipping, but the net effect is I'm bringing a new LVM snapshot copy of
the database out of recovery every 1-2 hours. That means I'd have to
spend 15 minutes, or one-quarter of the time, doing an analyze every
time I refresh the database. That's fairly painful. The LVM snap and
restart only takes 1-2 minutes right now.

If you have any other ideas how I can accomplish or improve this I'm all
ears.

Thanks,

Scot Kreienkamp
skreien(at)la-z-boy(dot)com

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Monday, March 08, 2010 10:32 PM
To: Scot Kreienkamp
Cc: Scott Mead; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] autovacuum question

"Scot Kreienkamp" <SKreien(at)la-z-boy(dot)com> writes:
>> Why not just add an 'analyze' as the last step of the restore job?

> Due to the amount of time it takes. The disks are slow enough to make
a
> database-wide analyze painful since I would have to repeat it every
1-2
> hours, IE every reload time.

You claimed that before. It didn't make any sense then and it doesn't
now. There is no way that an analyze is expensive compared to a
database reload.

Maybe what you need to be doing is rethinking the strategy that involves
reloading every couple of hours...

regards, tom lane