From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Nicos Panayides <nicos@magneta.com.cy>
To: pgsql-admin@postgresql.org
Subject: Re: Off-site storage for PITR logs
Date: Fri, 12 Mar 2010 11:20:07 -0600
Message-ID: <4B9A2367020000250002FCEC@gw.wicourts.gov> (raw)
In-Reply-To: <4B9A6E8B.4050303@magneta.com.cy>
References: <4B9A6E8B.4050303@magneta.com.cy>
Nicos Panayides <nicos@magneta.com.cy> wrote:
> The database generates about 3 PITR log files per minute. If my
> calculations are correct the sites need to be connected with a
> 7MBit connection and the logs will need about 68GB of storage per
> day!
I'd have put the minimum line speed at 8Mb/second, but I figure 10
bits per byte to allow for parity and packet overhead, and the
assumption that I might want to use the line for something else
(like monitoring) at the same time; but yeah, those numbers are in
the right ballpark.
> Does anyone have any suggestions on how to significantly reduce
> the volume of log files or recommend another off-site backup
> solution that would require less bandwidth and storage?
We stream the WAL files through gzip in our archive script. That
reduces them to 4MB to 8MB during normal usage, and 12MB to 15MB
during our routine database vacuums. If you have relatively idle
periods during which the log is pushed by archive_timeout, you
should filter through pg_clearxlogtail or pglesslog before using
gzip; the former yields a 16kB file during idle periods. (I'm not
sure about the other, but it should be capable of getting even
smaller.)
You'll need to determine how far back it's valuable to have the
"point in time recovery" capabilities. We do weekly base backups
and keep WAL files to restore forward from the earlier to current,
but then keep monthly "archival" backups, where each base backup is
kept with just the WAL files needed to successfully restore it.
-Kevin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, nicos@magneta.com.cy
Subject: Re: Off-site storage for PITR logs
In-Reply-To: <4B9A2367020000250002FCEC@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox