Re: Curiosity: what is PostgreSQL doing with data when "nothing" is happening?

Tom & Kevin

Thank you both for your inputs

On Sunday, November 25, 2012 at 06:36, Tom Lane wrote:

>If you're concerned about minimizing WAL traffic at idle, you might want
>to think about setting up a streaming replication arrangement instead of
>relying on archive segment switches. archive_timeout is really kind
>of a hack.

On Sunday, November 25, 2012 at 04:27, Kevin Grittner wrote:

>You might want to consider grabbing the source for pg_clearxlogtail
>from pgfoundry and piping through that followed by gzip in your
>archive script. An "empty" WAL file tends to be about 16KB that
>way, rather than (for us) ranging between 4MB and 8MB. That way you
>can keep the archive_timeout fairly short.
>

3096 -rw-------+ 1 postgres _postgres 1583556 Nov 26
13:46 0000000100000001000001B9.bz2
1216 -rw-------+ 1 postgres _postgres 619973 Nov 26
13:56 0000000100000001000001BA.bz2
736 -rw-------+ 1 postgres _postgres 375341 Nov 26
14:06 0000000100000001000001BB.bz2
80 -rw-------+ 1 postgres _postgres 37941 Nov 26
14:16 0000000100000001000001BC.bz2
88 -rw-------+ 1 postgres _postgres 42718 Nov 26
14:26 0000000100000001000001BD.bz2
48 -rw-------+ 1 postgres _postgres 21046 Nov 26
14:36 0000000100000001000001BE.bz2
584 -rw-------+ 1 postgres _postgres 298291 Nov 26
14:46 0000000100000001000001BF.bz2
240 -rw-------+ 1 postgres _postgres 120852 Nov 26
14:56 0000000100000001000001C0.bz2
3096 -rw-------+ 1 postgres _postgres 1583557 Nov 26
15:06 0000000100000001000001C1.bz2
8 -rw-------+ 1 postgres _postgres 161 Nov 26
15:26 0000000100000001000001C2.bz2
8 -rw-------+ 1 postgres _postgres 158 Nov 26
15:26 0000000100000001000001C3.bz2
8 -rw-------+ 1 postgres _postgres 162 Nov 26
15:36 0000000100000001000001C4.bz2
8 -rw-------+ 1 postgres _postgres 157 Nov 26
15:46 0000000100000001000001C5.bz2
8 -rw-------+ 1 postgres _postgres 163 Nov 26
15:56 0000000100000001000001C6.bz2
8 -rw-------+ 1 postgres _postgres 162 Nov 26
16:06 0000000100000001000001C7.bz2
8 -rw-------+ 1 postgres _postgres 165 Nov 26
16:16 0000000100000001000001C8.bz2
8 -rw-------+ 1 postgres _postgres 165 Nov 26
16:26 0000000100000001000001C9.bz2

PostgreSQL server idle throughout, and spitting out a WAL file
for archive every 10 minutes:
archive_timeout = 600 # force a logfile segment
switch after this

Impressive what happens when pg_clearxlogtail gets into the mix
halfway through! :)

Basically this resolves my original question about WAL output
when the system is idle... there isn't any. And Kevin's WAL tail
docker nicely isolates the good data from the rubbish. I can now
move forward exploring options for incremental uploads to an
internet (i.e., well off site) data repository, since the data
traffic will only carry stuff that actually matters.

Getting philosophical...

.... and taking Tom at his word that "archive_timeout is really
kind of a hack"
.... and noting Kevin's pg_clearxlogtail is something of the
counter hack,

Would the universe as we know it be upset if there was a
postgresql.conf option such as:

archive_zero_fill_on_segment_switch = on|off # default off

This would achieve the idle compression result much more
elegantly (I know, it's still a hack) for those who have the
need, without, as far as I can tell, breaking anything else.

Regards
Gavan Schneider