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@la-z-boy.com
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 08, 2010 10:32 PM
To: Scot Kreienkamp
Cc: Scott Mead; pgsql-general@postgresql.org
Subject: Re: [GENERAL] autovacuum question
"Scot Kreienkamp" <SKreien@la-z-boy.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
