pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOff-site storage for PITR logs
7+ messages / 5 participants
[nested] [flat]
* Off-site storage for PITR logs
@ 2010-03-12 16:40 Nicos Panayides <nicos@magneta.com.cy>
0 siblings, 3 replies; 7+ messages in thread
From: Nicos Panayides @ 2010-03-12 16:40 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am planning an off-site backup solution for a fairly busy
(mostly-write) 8.3 database. The database is currently about 200GB in
size. I though about using log shipping and cold standby since it's easy
in terms of administration and also offers point in time recovery.
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!
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?
Thanks.
--
Regards,
Nicos Panayides
IT Manager
Magneta Technologies Ltd
Tel: +357 22721919, 22317400
Fax: +357 22721917
Web: http://www.magneta.eu
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Off-site storage for PITR logs
@ 2010-03-12 17:11 Bruce Momjian <bruce@momjian.us>
parent: Nicos Panayides <nicos@magneta.com.cy>
2 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2010-03-12 17:11 UTC (permalink / raw)
To: Nicos Panayides <nicos@magneta.com.cy>; +Cc: pgsql-admin; koichi.szk@gmail.com
Nicos Panayides wrote:
> Hi,
> I am planning an off-site backup solution for a fairly busy
> (mostly-write) 8.3 database. The database is currently about 200GB in
> size. I though about using log shipping and cold standby since it's easy
> in terms of administration and also offers point in time recovery.
>
> 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!
>
> 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?
I would use pg_lesslog to reduce the size of the WAL files:
http://pgfoundry.org/projects/pglesslog/
However, there is a bug in pg_lesslog so I wouldn't use it until that is
fixed:
http://archives.postgresql.org/pgsql-announce/2010-02/msg00005.php
Koichi, do you have any update on this? I don't see that a new version
has been uploaded, and I also see no mention on the pgfoundry site about
the bug.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Off-site storage for PITR logs
@ 2010-03-12 17:20 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Nicos Panayides <nicos@magneta.com.cy>
2 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-03-12 17:20 UTC (permalink / raw)
To: Nicos Panayides <nicos@magneta.com.cy>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Off-site storage for PITR logs
@ 2010-03-13 00:53 Koichi Suzuki <koichi.szk@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 7+ messages in thread
From: Koichi Suzuki @ 2010-03-13 00:53 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Nicos Panayides <nicos@magneta.com.cy>; pgsql-admin
Sorry for that. The bug was incorrect calculation of XNOOP record
size in GiST WAL when incremental log is created from the full backup.
Because the bug impact is so serious, I'm rebuilding the test
environment which is taking long. I'll report the cause of the bug
to hackers and bugs.
If GiST is not used, WAL compression works correctly but I'm not
comfortable to use lesslog with known bug.
----------
Koichi Suzuki
2010/3/13 Bruce Momjian <bruce@momjian.us>:
> Nicos Panayides wrote:
>> Hi,
>> I am planning an off-site backup solution for a fairly busy
>> (mostly-write) 8.3 database. The database is currently about 200GB in
>> size. I though about using log shipping and cold standby since it's easy
>> in terms of administration and also offers point in time recovery.
>>
>> 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!
>>
>> 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?
>
> I would use pg_lesslog to reduce the size of the WAL files:
>
> http://pgfoundry.org/projects/pglesslog/
>
> However, there is a bug in pg_lesslog so I wouldn't use it until that is
> fixed:
>
> http://archives.postgresql.org/pgsql-announce/2010-02/msg00005.php
>
> Koichi, do you have any update on this? I don't see that a new version
> has been uploaded, and I also see no mention on the pgfoundry site about
> the bug.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Off-site storage for PITR logs
@ 2010-03-14 08:50 Simon Riggs <simon@2ndQuadrant.com>
parent: Nicos Panayides <nicos@magneta.com.cy>
2 siblings, 1 reply; 7+ messages in thread
From: Simon Riggs @ 2010-03-14 08:50 UTC (permalink / raw)
To: Nicos Panayides <nicos@magneta.com.cy>; +Cc: pgsql-admin
On Fri, 2010-03-12 at 18:40 +0200, Nicos Panayides wrote:
> I am planning an off-site backup solution for a fairly busy
> (mostly-write) 8.3 database. The database is currently about 200GB in
> size. I though about using log shipping and cold standby since it's easy
> in terms of administration and also offers point in time recovery.
>
> 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!
>
> 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?
Consider turning off full_page_writes which will permanently minimise
the amount of WAL traffic, as well as being an integrated and
fully-supported option with Postgres. You will need additional
non-volatile cache on your disks to ensure that is a safe option, though
that will yield performance advantages also.
--
Simon Riggs www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Off-site storage for PITR logs
@ 2010-03-15 09:27 Nicos Panayides <nicos@magneta.com.cy>
parent: Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 7+ messages in thread
From: Nicos Panayides @ 2010-03-15 09:27 UTC (permalink / raw)
To: Simon Riggs <simon@2ndQuadrant.com>; +Cc: pgsql-admin
Hi Simon,
thanks for the suggestion. What kind of space savings should I expected
from turning off full_page_writes? The servers have battery-backed
write-caches for the disk controllers, so it should be safe to give this
a try. Does turning off full_page_writes have any other effect on the
database?
Regards,
Nicos Panayides
IT Manager
Magneta Technologies Ltd
Tel: +357 22721919, 22317400
Fax: +357 22721917
Web: http://www.magneta.eu
On 14/03/2010 10:50, Simon Riggs wrote:
> On Fri, 2010-03-12 at 18:40 +0200, Nicos Panayides wrote:
>
>> I am planning an off-site backup solution for a fairly busy
>> (mostly-write) 8.3 database. The database is currently about 200GB in
>> size. I though about using log shipping and cold standby since it's easy
>> in terms of administration and also offers point in time recovery.
>>
>> 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!
>>
>> 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?
>
> Consider turning off full_page_writes which will permanently minimise
> the amount of WAL traffic, as well as being an integrated and
> fully-supported option with Postgres. You will need additional
> non-volatile cache on your disks to ensure that is a safe option, though
> that will yield performance advantages also.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Off-site storage for PITR logs
@ 2010-03-15 09:57 Simon Riggs <simon@2ndQuadrant.com>
parent: Nicos Panayides <nicos@magneta.com.cy>
0 siblings, 0 replies; 7+ messages in thread
From: Simon Riggs @ 2010-03-15 09:57 UTC (permalink / raw)
To: Nicos Panayides <nicos@magneta.com.cy>; +Cc: pgsql-admin
On Mon, 2010-03-15 at 11:27 +0200, Nicos Panayides wrote:
> thanks for the suggestion. What kind of space savings should I expected
> from turning off full_page_writes?
Substantial, though you should measure it and see, since it is workload
dependent.
--
Simon Riggs www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-03-15 09:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-12 16:40 Off-site storage for PITR logs Nicos Panayides <nicos@magneta.com.cy>
2010-03-12 17:11 ` Bruce Momjian <bruce@momjian.us>
2010-03-13 00:53 ` Koichi Suzuki <koichi.szk@gmail.com>
2010-03-12 17:20 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-03-14 08:50 ` Simon Riggs <simon@2ndQuadrant.com>
2010-03-15 09:27 ` Nicos Panayides <nicos@magneta.com.cy>
2010-03-15 09:57 ` Simon Riggs <simon@2ndQuadrant.com>
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