Re: pg_xlog volume question

Thanks for quick response.

I should have added that the 'recovery' site is really no more than a
Second copy where reporting and ETL work is to be done - so we need
The database to be available at least 6 -8 hours during the day.
I've tried to wrestle with PG_STANDBY to allow daily availability
And then to place back in 'recovery mode' for the next nights set of
Logs But it doesn't lend itself to this type of application.

Mark Steben│Database Administrator│
@utoRevenue-R- "Join the Revenue-tion"
95 Ashley Ave. West Springfield, MA., 01089
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
@utoRevenue is a registered trademark and a division of Dominion Enterprises

-----Original Message-----
From: Joshua D. Drake [mailto:jd(at)commandprompt(dot)com]
Sent: Friday, February 06, 2009 1:13 PM
To: Mark Steben
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] pg_xlog volume question

On Fri, 2009-02-06 at 12:52 -0500, Mark Steben wrote:
> Hello listers,
>
> I'm designing a PITR backup / recovery scenario where I'm log-shipping to
> another location and recovering using a tar backup and log roll forward.
> The plan is to save a week's worth of logs and restore nightly using the
> accumulated logs to that point. A daily full backup is too
> expensive and time consuming - it takes over 36 hours to transport it over
> the network to the recovery site. So we're going with the weekly backup
and
> saving the weeks worth of logs.
>
> As you well know, I will require a LOT of storage for the xlogs. I'm
> currently creating about 2GB of logs every hour. Is there a config
> parameter to reduce the amount that Xlog takes up?

No. I would suggest using pg_standby or pitrtools (which requires
pg_standby) to manage this. I would also suggest just restoring as the
logs become available. If you can't process 2G in an hour over the
network do it at night.

Joshua D. Drake

>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997