Re: wal files stay in the pg_xlog dir

On 08/28/2015 01:07 PM, kingl wrote:
> To whom it may concern:
>
> We have a 2 nodes postgres cluster, postgres server v9.3.8 and repmgr is
> used to enable the cluster function. barman v1.4.1 is used to take backup of
> the master postgres node.
>
> everything seems to be working except the wal files in pg_xlog on node1
> keeps accumulating.

Well below you have:

wal_keep_segments = 2000

That means at least 2000 WAL files will be kept around.

How many files do you see in pg_xlog?

>
> there is a backup file everytime the backup happens under the pg_xlog
> directory.
>
> -rw------- 1 postgres postgres 327 Aug 28 02:33
> 000000230000000F00000075.00000028.backup
>
> pg_xlog/archive_status shows that archive is up to date.
>
> -rw------- 1 postgres postgres 16777216 Aug 28 15:46
> 000000230000000F0000009B
> drwx------ 2 postgres postgres 86016 Aug 28 15:47 archive_status
> -rw------- 1 postgres postgres 16777216 Aug 28 16:00
> 000000230000000F0000009C
>
> inside of archive_status the latest is 09B
> -rw------- 1 postgres postgres 0 Aug 28 15:46 000000230000000F0000009B.done
>
> it seems that wal files are not being removed after archive. i read
> archive_cleanup? do i need to set it up for wal files clean up? if so how to
> do that?
>
> Thank you in advance!
>
> postgresql conf file on master node
>
> /etc/postgresql/9.3/main/postgresql.conf
>
>
> #------------------------------------------------------------------------------
> # WRITE AHEAD LOG
> #------------------------------------------------------------------------------
>
> # - Settings -
>
> wal_level = 'hot_standby' # minimal, archive, or
> hot_standby
> # (change requires restart)
> #fsync = on # turns forced synchronization on or
> off
> #synchronous_commit = on # synchronization level; on, off, or
> local
> #wal_sync_method = fsync # the default is the first option
> # supported by the operating system:
> # open_datasync
> # fdatasync (default on Linux)
> # fsync
> # fsync_writethrough
> # open_sync
> #full_page_writes = on # recover from partial page writes
> wal_buffers = 16MB # min 32kB, -1 sets based on
> shared_buffers
> # (change requires restart)
> #wal_writer_delay = 200ms # 1-10000 milliseconds
>
> #commit_delay = 0 # range 0-100000, in microseconds
> #commit_siblings = 5 # range 1-1000
>
> # - Checkpoints -
>
> checkpoint_segments = 32 # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min # range 30s-1h
> checkpoint_completion_target = 0.7 # checkpoint target duration, 0.0 -
> 1.0
> #checkpoint_warning = 30s # 0 disables
>
> # - Archiving -
>
> archive_mode = on # allows archiving to be done
> # (change requires restart)
> archive_command = 'cd . && rsync -a %p
> barman(at)barman01:/var/lib/barman/postgresnode01/incoming/%f'
> # command to use to archive a logfile segment
> #archive_timeout = 0 # force a logfile segment switch after this
> # number of seconds; 0 disables
>
> #------------------------------------------------------------------------------
> # REPLICATION
> #------------------------------------------------------------------------------
>
> # - Master Server -
>
> # These settings are ignored on a standby server
>
> max_wal_senders = 10 # max number of walsender processes
> # (change requires restart)
> #wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds
> wal_keep_segments = 2000 # in logfile segments, 16MB each; 0 disables
> #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is
> delayed
> #replication_timeout = 60s # in milliseconds; 0 disables
> #synchronous_standby_names = '' # standby servers that provide sync rep
> # comma-separated list of application_name
> # from standby(s); '*' = all
>
> # - Standby Servers -
>
> # These settings are ignored on a master server
>
> hot_standby = on # "on" allows queries during
> recovery
> # (change requires restart)
> #max_standby_archive_delay = 30s # max delay before canceling queries
> # when reading WAL from archive;
> # -1 allows indefinite delay
> #max_standby_streaming_delay = 30s # max delay before canceling queries
> # when reading streaming WAL;
> # -1 allows indefinite delay
> #wal_receiver_status_interval = 10s # send replies at least this often
> # 0 disables
> #hot_standby_feedback = off # send info from standby to prevent
> # query conflicts
>
> barman conf :
>
> description = "postgresnode01"
> ssh_command = ssh postgres(at)postgresnode01
> conninfo = host=postgresnode01 user=postgres
> retention_policy = RECOVERY WINDOW OF 4 WEEKS
>
>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/wal-files-stay-in-the-pg-xlog-dir-tp5863782.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

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