Thread: What process clears the logs?

Greetings,

As you may be aware, we experienced a problem last week with pg_clogs
that had been deleted, through human error it appears.  What process
will clear or delete the pg_clogs?  I've been all over the
documentation and I'm not finding a reference to this.

Thanks, in advance,

Carol

Carol Walter wrote:
> Greetings,
>
> As you may be aware, we experienced a problem last week with pg_clogs
> that had been deleted, through human error it appears.  What process
> will clear or delete the pg_clogs?  I've been all over the
> documentation and I'm not finding a reference to this.
>
PostgreSQL takes care of this itself. I don't know where it is
documented but IIRC from old messages, the number of files in pg_xlog
should level out at about two-times your checkpoint_settings value.

I'd have to do some digging as I don't recall for sure, but I think that
if you are using archive_command setting to archive WAL files  the
archive command does not return "success", then the WAL file will not be
removed/reused.

Cheers,
Steve

Ah-h-h, that's exactly my question.  What part of Postgres "takes
care of this itself."  I'm asking because I had 86 pg_clog files
dated back to mid-May.  I got the impression from something Tom said
that backups should prune this directory.  Perhaps my "impression"
was wrong.  Most databases I've used in the past have gotten rid of
the transaction logs, etc, when a backup is done. The restore process
used that last backup and then applied the transaction logs to it.
Once another backup was completed the old transaction logs were no
longer needed.  I'm trying to understand what happens "under the
hood" so to speak.  What checkpoint_settings value are you referring to?

Thanks,
Carol

On Sep 30, 2008, at 3:26 PM, Steve Crawford wrote:

> Carol Walter wrote:
>> Greetings,
>>
>> As you may be aware, we experienced a problem last week with
>> pg_clogs that had been deleted, through human error it appears.
>> What process will clear or delete the pg_clogs?  I've been all
>> over the documentation and I'm not finding a reference to this.
>>
> PostgreSQL takes care of this itself. I don't know where it is
> documented but IIRC from old messages, the number of files in
> pg_xlog should level out at about two-times your
> checkpoint_settings value.
>
> I'd have to do some digging as I don't recall for sure, but I think
> that if you are using archive_command setting to archive WAL files
> the archive command does not return "success", then the WAL file
> will not be removed/reused.
>
> Cheers,
> Steve
>

Carol Walter wrote:
> Ah-h-h, that's exactly my question.  What part of Postgres "takes care
> of this itself."  I'm asking because I had 86 pg_clog files dated back
> to mid-May.  I got the impression from something Tom said that backups
> should prune this directory.  Perhaps my "impression" was wrong.  Most
> databases I've used in the past have gotten rid of the transaction
> logs, etc, when a backup is done. The restore process used that last
> backup and then applied the transaction logs to it.  Once another
> backup was completed the old transaction logs were no longer needed.
> I'm trying to understand what happens "under the hood" so to speak.
> What checkpoint_settings value are you referring to?
Sorry. I had a brain/fingers disconnect. I meant the
"checkpoint_segments" setting. Anyway, answers to a number of your
questions regarding write-ahead logging may be found here:
http://www.postgresql.org/docs/8.3/interactive/wal-configuration.html

Cheers,
Steve