Re: pg_xlog size

On Wed, Mar 16, 2011 at 12:09 AM, Tech Madhu <technimadhu(at)gmail(dot)com> wrote:
> hi all,
>
> Setup:
> Sparc, Solaris 10, Postgres 9.0.2, using streaming replication and hot
> standby. 1 master 1 slave
>
> Everything works fine (w.r.t replication), but the pg_xlog size grows
> continuously, though i had no operations going on. Also the archiving to the
> other side filled up the other side FS.
> ls -l /var/postgres/data/pg_xlog | wc -l
> 103
> At start, there were only 15 files. The max_wal_segments is 32, but not sure
> why iam seeing 103 files. Also the archiving dir size doubled (w.r.t number
> of files archived). and filled up the filesystem.
> I manually logged into postgres and run checkpoint; did not see any file
> reduction
>
> Pasting some of the relevant conf values
> The checkpoint_segments is commented out, so default to 3 segments right?
>
> postgresql.conf
> -----------------------
> wal_level = hot_standby
> checkpoint_warning = 30s
> #checkpoint_segments = 3                # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min              # range 30s-1h
> #checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 -
> 1.0
>
> archive_mode = on               # allows archiving to be done
>                                 # (change requires restart)
> archive_command = 'cp %p /var/postgres/walfiles/%f'             #  this dir
> is NFS mounted dir on the slave node
> archive_timeout = 30

Since the setting "archive_timeout" makes the server create new WAL
segment file for each 30 seconds (even if there is no write transaction),
the size of pg_xlog directory continuously grows up.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center