Re: [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>, "Jeff Davis" <pgsql(at)j-davis(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>,<apoc9009(at)yahoo(dot)de>
Subject: Re: [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
Date: 2007-09-06 21:15:06
Message-ID: 46E02789.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>>> On Thu, Sep 6, 2007 at 3:25 PM, in message
<1189110327(dot)9243(dot)17(dot)camel(at)dogma(dot)ljc(dot)laika(dot)com>, Jeff Davis <pgsql(at)j-davis(dot)com>
wrote:
> On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote:
>> With file-based log shipping, you can get down to 1 second, by using the
>> archive_timeout setting. It will produce a lot of log files with very
>> little content in them, but they will compress well.
>
> I tried doing a couple pg_switch_xlog() in quick succession, and the WAL
> files that represent less than a second of actual data don't seem much
> more compressible than the rest.

Agreed. We kick out a WAL file once per hour (if they don't fill first)
and have found that a compressed WAL file during normal activity averages
a little over 4 MB. During the nightly VACUUM ANALYZE we get a few over
10 MB. The interesting thing is that about the time that these WAL files
would get reused, even when the system is idle, they are at the VACUUM
ANALYZE size again. Note how the first three 18:00 file sizes echo at the
next morning, before the users are in and working.

5293110 2007-08-22 17:14 000000010000001800000044.gz
5205720 2007-08-22 17:14 000000010000001800000045.gz
5104595 2007-08-22 17:14 000000010000001800000046.gz
3747524 2007-08-22 17:14 000000010000001800000047.gz
3118762 2007-08-22 17:14 000000010000001800000048.gz
3342918 2007-08-22 17:14 000000010000001800000049.gz
4600438 2007-08-22 17:14 00000001000000180000004A.gz
2715708 2007-08-22 17:15 00000001000000180000004B.gz
2865803 2007-08-22 17:15 00000001000000180000004C.gz
10111700 2007-08-22 18:00 00000001000000180000004D.gz
11561630 2007-08-22 18:00 00000001000000180000004E.gz
11619590 2007-08-22 18:00 00000001000000180000004F.gz
7165231 2007-08-22 18:00 000000010000001800000050.gz
4012164 2007-08-22 18:00 000000010000001800000051.gz
4590502 2007-08-22 18:00 000000010000001800000052.gz
3617203 2007-08-22 18:01 000000010000001800000053.gz
3552210 2007-08-22 18:01 000000010000001800000054.gz
4006261 2007-08-22 18:01 000000010000001800000055.gz
3538293 2007-08-22 18:01 000000010000001800000056.gz
4291776 2007-08-22 18:02 000000010000001800000057.gz
4837268 2007-08-22 18:02 000000010000001800000058.gz
3948408 2007-08-22 19:02 000000010000001800000059.gz
2714635 2007-08-22 20:02 00000001000000180000005A.gz
4989698 2007-08-22 21:02 00000001000000180000005B.gz
5290729 2007-08-22 22:02 00000001000000180000005C.gz
5203306 2007-08-22 23:02 00000001000000180000005D.gz
5101976 2007-08-23 00:02 00000001000000180000005E.gz
3745125 2007-08-23 01:02 00000001000000180000005F.gz
3119218 2007-08-23 02:02 000000010000001800000060.gz
3340691 2007-08-23 03:02 000000010000001800000061.gz
4599279 2007-08-23 04:02 000000010000001800000062.gz
10110899 2007-08-23 05:02 000000010000001800000063.gz
11555895 2007-08-23 06:02 000000010000001800000064.gz
11587646 2007-08-23 07:02 000000010000001800000065.gz

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Mielke 2007-09-06 21:17:12 Re: Hash index todo list item
Previous Message Magnus Hagander 2007-09-06 21:07:55 Re: Some more msvc++ 8.2.4 build feedback