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