Re: Curiosity: what is PostgreSQL doing with data when "nothing" is happening? - Mailing list pgsql-novice

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