Could you have two of these non-production databases? Prepare one in the background, including an analyze and then make it the 'live' non-production database then use the offline / alternative database for the next load prepare that and then switch it on when ready.
In this scenario you'd need twice the disk space I guess but the 'downtime' would be a lot less. I'd imagine you could have databases on different ports and switch them at the roll over point, or maybe even just different database names.
Just an alternative idea to throw out there.
Bryan.
On Tue, Mar 9, 2010 at 8:47 AM, Scot Kreienkamp
<SKreien@la-z-boy.com> wrote:
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,
-----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
--
Sent via pgsql-general mailing list (
pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general