Re: pg_xlog is getting bigger

On 12/19/2012 01:16 PM, Kevin Grittner wrote:
> AI Rumman wrote:
>> Kevin Grittner <kgrittn(at)mail(dot)com> wrote:
>>> AI Rumman wrote:
>>>> Kevin Grittner <kgrittn(at)mail(dot)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
>> checkpoint_warning | 1h
>> default_statistics_target | 250
>> escape_string_warning | off
>> fsync | on
>> lc_collate | en_US.UTF-8
>> lc_ctype | en_US.UTF-8
>> listen_addresses | *
>> log_destination | stderr
>> log_directory | pg_log
>> log_filename | postgresql-%a.log
>> log_line_prefix | %t [%p]: [%l-1] host=%h user=%u,db=%d
>> log_min_duration_statement | 4s
>> log_rotation_age | 1d
>> log_rotation_size | 0
>> log_truncate_on_rotation | on
>> logging_collector | on
>> maintenance_work_mem | 1GB
>> max_connections | 500
>> max_stack_depth | 2MB
>> port | 5432
>> server_encoding | UTF8
>> shared_buffers | 512MB
>> TimeZone | US/Eastern
>> wal_sync_method | fdatasync
>> work_mem | 256MB
>> (31 rows)
>>
>> Please let me know if you find anything wrong here.
>> Thanks.
>
> I don't see anything obvious. Putting this back on the list, where
> it should have stayed all along. Maybe someone else has an idea;
> I've only seen such behavior when there were archiving problems
> which were showing up in the server log.

Well just for grins and to go to the source, look in the postgresql.conf
file itself.

In particular what is the settings for:

wal_keep_segments

Also what does querying pg_stat_activity as the postgres user show?

Looking for queries that have been held open a long time.

>
> -Kevin
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com