Re: Pg_xlog increase due to postgres crash (disk full)

Hello, thanks for your response.

The xlogs that are filling up are in the original data directory and the
wal_keep_segments is commented out on my master and slave postgresql.conf

Cliff

On 22 January 2013 14:48, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:

> On 01/22/2013 03:12 AM, Cliff de Carteret wrote:
>
>> My database crashed a couple of days ago during an upgrade several
>> seconds after committing a large transaction to the database. Eventually
>> we found out that this was due to the disk being full as the transaction
>> had created several gigs of data. A day or so later the disk is full
>> again and PostgreSQL crashes due to the pg_xlog file taking up all of
>> the disk space. I have cleaned up the drive to have so extra space which
>> allows PostgreSQL to start again but the xlogs are still increasing. I
>> have two errors in my pg_log:
>>
>> "WARNING: transaction log file "00000001000000A800000078" could not be
>> archived: too many failures" and
>>
>> "LOG: archive command failed with exit code 1
>> DETAIL: The failed archive command was: test ! -f
>> /opt/postgres/remote_pgsql/**wal_archive/**00000001000000A800000078 && cp
>> pg_xlog/**00000001000000A800000078
>> /opt/postgres/remote_pgsql/**wal_archive/**00000001000000A800000078"
>>
>
> I am not seeing it below, but just to be complete is wal_keep_segments set
> to something greater than 0?
>
>
> Just to be clear which xlogs are filling up, the ones in the original data
> directory or in the archive directory?
>
>
>
>
>> Postgres version 9.0.3 conf:
>>
>>
>> *
>>
>>
>> wal_level = hot_standby
>> archive_mode = true
>> archive_command = 'test ! -f
>> /opt/postgres/remote_pgsql/**wal_archive/%f && cp %p
>> /opt/postgres/remote_pgsql/**wal_archive/%f' # command to use to
>> archive a logfile segment
>> archive_timeout = 1800
>> max_wal_senders = 1
>> max_standby_archive_delay = 900s
>> max_standby_streaming_delay = 900s
>> default_statistics_target = 50 # pgtune wizard 2010-11-18
>> maintenance_work_mem = 480MB # pgtune wizard 2010-11-18
>> constraint_exclusion = on # pgtune wizard 2010-11-18
>> checkpoint_completion_target = 0.9 # pgtune wizard 2010-11-18
>> effective_cache_size = 5632MB # pgtune wizard 2010-11-18
>> work_mem = 48MB # pgtune wizard 2010-11-18
>> wal_buffers = 8MB # pgtune wizard 2010-11-18
>> checkpoint_segments = 16 # pgtune wizard 2010-11-18
>> shared_buffers = 1920MB # pgtune wizard 2010-11-18
>> max_connections = 80 # pgtune wizard 2010-11-18
>>
>>
>>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>