Re: HELP!!! The WAL Archive is taking up all space

Hi all,

Thank you for all of your responses.
Meanwhile, I will repost this at pgsql-general(at)postgresql(dot)org

Regards,
Fattah

On 09/12/2015, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Tue, Dec 8, 2015 at 4:43 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Tue, Dec 8, 2015 at 3:33 AM, FattahRozzaq <ssoorruu(at)gmail(dot)com> wrote:
>>
>>> Hi all,
>>>
>>> Please help...
>>>
>>> I have 1 master PostgreSQL and 1 standby PostgreSQL.
>>> Both servers has the same OS Linux Debian Wheezy, the same hardware.
>>>
>>> Both server hardware:
>>> CPU: 24 cores
>>> RAM: 128GB
>>> Disk-1: 800GB SAS (for OS, logs, WAL archive directory)
>>> Disk-2: 330GB SSD (for PostgreSQL data directory, except WAL archive
>>> and except pg_log)
>>>
>>> The part of the configuration are as below:
>>> checkpoint_segments = 64
>>> checkpoint_completion_target = 0.9
>>> default_statistics_target = 10
>>> maintenance_work_mem = 1GB
>>> effective_cache_size = 64GB
>>> shared_buffers = 24GB
>>> work_mem = 5MB
>>> wal_buffers = 8MB
>>> wal_keep_segments = 4096
>>> wal_level = hot_standby
>>> max_wal_senders = 10
>>> archive_mode = on
>>> archive_command = 'cp -i %p /home/postgres/archive/master/%f'
>>>
>>>
>>> The WAL archive is at /home/postgres/archive/master/, right?
>>> This directory consume more than 750GB of Disk-1.
>>> Each segment in the /home/postgres/archive/master/ is 16MB each
>>> There are currently 47443 files in this folder.
>>>
>>> I want to limit the total size use by WAL archive to around 200-400 GB.
>>>
>>> Do I set the segment too big?
>>> wal_keep_segments = 4096
>>> checkpoint_segments = 64
>>>
>>> What value should I set for it?
>>>
>>
>> In which case you need to calculate how long it takes to accumulate that
>> much archive data and then perform a base backup roughly that often after
>> which point any WAL older that the point at which you began the backup
>> can
>> be removed.
>>
>> You cannot just limit how large the WAL archive is since removing any WAL
>> file will pretty much make any attempt at restoration fail.​
>>
>> David J.
>>
>>
> ​While valid I missed that you have a streaming replica on the other end
> that should be removing files as they are loaded pending the retention
> setting...see Michael's response for better information.
>
> David J.​
>