Re: autovacuum question

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(at)la-z-boy(dot)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
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>