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

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.klaver@gmail.com