Re: pg_xlog is getting bigger

On 12/19/2012 01:16 PM, Kevin Grittner wrote:
> AI Rumman wrote:
>> Kevin Grittner <kgrittn@mail.com> wrote:
>>> AI Rumman wrote:
>>>> Kevin Grittner <kgrittn@mail.com> wrote:
>>>>> AI Rumman wrote:
>>>>>
>>>>>> I am working on a Postgresql 9.0 server. I have no replication and
>>>>>> archive mode setup. But I found that the pg_xlog is getting bigger
>>>>>> and bigger. Right now it is 20 GB.
>>>>>>
>>>>>> How should I recover these spaces?
>>>>>
>>>>> Do you have archiving turned on? Are you getting errors in the server
>>>>> log related to failures of the archiving?
>>>>
>>>> I don't have archiving turned on. Can I remove some old xlog files?
>>>
>>> No. You can corrupt your database if you delete from that directory
>>> directly. Please post the results from running the query on this
>>> page:
>>>
>>> http://wiki.postgresql.org/wiki/Server_Configuration
>
>>   version | PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu,
>> compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit
>>   bgwriter_delay | 300ms
>>   bgwriter_lru_maxpages | 100
>>   bgwriter_lru_multiplier | 2
>>   bytea_output | escape
>>   checkpoint_segments | 300

Aah, just noticed the above.

http://www.postgresql.org/docs/9.0/interactive/wal-configuration.html

"
Checkpoints are points in the sequence of transactions at which it is
guaranteed that the heap and index data files have been updated with all
information written before the checkpoint. At checkpoint time, all dirty
data pages are flushed to disk and a special checkpoint record is
written to the log file. (The changes were previously flushed to the WAL
files.) In the event of a crash, the crash recovery procedure looks at
the latest checkpoint record to determine the point in the log (known as
the redo record) from which it should start the REDO operation. Any
changes made to data files before that point 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.)...


...A checkpoint is created every checkpoint_segments log segments,...
"

Given that log segments are 16MB this means you will get 16MB * 300
segments of logs before they can start to be recycled. Does this
correspond with the directory size you are seeing in pg_xlog?  FYI the
default value is 3.


--
Adrian Klaver
adrian.klaver@gmail.com