Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.klaver@xxxxxxxxx> 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.klaver@xxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux