Re: log files - Mailing list pgsql-novice

Kevin Grittner wrote:
> jjurban <jjurban@attglobal.net> wrote:
>
>> Amit Langote wrote:
>>
>
>
>> There are over 5.7 Gigabytes of these files.  This cannot be
>> correct.
>>
>
> That depends entirely on your configuration.  In some environments
> that would be excessive; in others it might be just right.
>


Here's what I did - I checked those pg_xlog files and discovered that
the last 2 entries in the pg_subtrans directory had dates of 4/12/2013
and 5/10/2013 - are these considered "checkpoints?"

Then I deleted ALL of the 16+ MB files in pg_xlog with dates BEFORE
4/12/2013.

My system seems to run OK now.

But size-wise, the total disk storage required is only 800MB now,
whereas before I deleted all those pg_xlog files, the size was 6.5 GB. I
realize that you can get terabyte drives now, but I still believe that
one shouldn't waste computer resources if possible.

I read in the reference you provided

"Any changes made to data files before that point [the last checkpoint?]
are guaranteed to be already on disk. Hence, after a checkpoint, log
segments preceding the one containing the redo record are no longer
needed and can be recycled or removed. (When WAL archiving is being
done, the log segments must be archived before being recycled or removed.)"

So I assume that my deletion did no harm.

By the way, I  am running version 8.0 on eComStation.  I know that
everyone will jump on me and demand that I upgrade to the latest, but
8.0 has always worked fine for me and I don't have dlls to enable Rexx -
PGS interface for later versions.

I still am not clear on what I have to do to avoid generating these big
files that I really don't need.  I don't want to do archiving and I
always back up by simply zipping my PGSData folder.

Thanks again,

John




>
>> Which of these can I remove?
>>
>
> It is a bad idea to remove any of them yourself.  If you configure
> PostgreSQL to keep fewer of them, the unneeded ones according to
> your new configuration should get cleaned up fairly quickly.
>
>
>>> Those are Write Ahead Log (WAL) file segments (16MB each) and
>>> are part of PostgreSQL's REDO mechanism.
>>> PostgreSQL official documentation on WAL:
>>>
>>> http://www.postgresql.org/docs/9.2/static/wal.html
>>>
>
>
>> What can I do to avoid generating these monstrous files?
>>
>
> Read the referenced documentation.  Primarily you will be
> interested in checkpoint_segments, wal_keep_segments, and settings
> related to archiving -- since if archiving is turned on these files
> must be kept in the pg_xlog directory until successfully archived.
>
> --
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
>