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 <skre...@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, > > Scot Kreienkamp > skre...@la-z-boy.com > > -----Original Message----- > From: Tom Lane [mailto:t...@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" <skre...@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 >
