Re: Trimming transaction logs after extended WAL archive failures

On Mar 25, 2014, at 4:02 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

> On 03/25/2014 03:54 PM, Steven Schlansker wrote:
>>
>> On Mar 25, 2014, at 3:52 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>>> On 03/25/2014 01:56 PM, Steven Schlansker wrote:
>>>> Hi everyone,
>>>>
>>>> I have a Postgres 9.3.3 database machine.  Due to some intelligent work on the part of someone who shall remain
nameless,the WAL archive command included a ‘> /dev/null 2>&1’ which masked archive failures until the disk entirely
filledwith 400GB of pg_xlog entries. 
>>>>
>>>> I have fixed the archive command and can see WAL segments being shipped off of the server, however the xlog
remainsat a stable size and is not shrinking.  In fact, it’s still growing at a (much slower) rate. 
>>>
>>> So what is wal_keep_segments set at in postgresql.conf?
>>>
>>
>> 5000.  There are currently about 18000 WAL segments in pg_xlog.
>
> I guess what I should have also asked previously is what exactly are you doing, are you streaming as well as
archiving?

Yes, we have both enabled.  Here’s some hopefully relevant configuration stanzas and information:

checkpoint_segments = 1024                # in logfile segments, min 1, 16MB each
checkpoint_timeout = 10min              # range 30s-1h
checkpoint_completion_target = 0.9     # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 8min               # 0 disables


archive_mode = on             # allows archiving to be done
archive_command = 'rsync -q %p pgbackup@d0028.nessops.net:./wal/prd-db1a/%f'           # command to use to archive a
logfilesegment 
archive_timeout = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables


# These settings are ignored on a standby server

max_wal_senders = 10            # max number of walsender processes
wal_keep_segments = 5000          # in logfile segments, 16MB each; 0 disables
vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is delayed

wal_sender_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
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

[root@prd-db1a data]# ls -1 pg_xlog | wc -l
20168

I have verified that WAL segments are being archived to the archive destination, and that the slave is connected and
receivingsegments. 

Thanks!