Re: WAL retention

On Tue, Jun 21, 2016 at 11:26 AM, Slavcho Trnkovski <strnkovski(at)gmail(dot)com>
wrote:

> Hi,
>
> I use version 9.4.
> If I create physical replication slot, I'll need standby server to use it.
> I can use pg_receivexlog but it will not prevent removing WAL files, it
> will just stream it to some other location.
> Do you maybe have an idea how backup can use replication slot (without
> standby)?
>
> Regards,
> Slavcho
>
> On Mon, Jun 20, 2016 at 3:52 PM, Bert <biertie(at)gmail(dot)com> wrote:
>
>> Hello,
>>
>> Which version of PostgreSQL are you using?
>> If you are using version 9.4 or newer you can use the concept of
>> 'replication slots' to block the removal or archiving of WAL files.
>> But do not forgot to remove the slot again, else PostgreSQL will never
>> remove the wall files again!
>>
>> Bert
>>
>> On Mon, Jun 20, 2016 at 10:38 AM, Slavcho Trnkovski <strnkovski(at)gmail(dot)com
>> > wrote:
>>
>>> Hi Guys,
>>>
>>> Is there a way to set retention policy for removing WAL files in
>>> hours/days instead of number of WAL files (wal_keep_segments)?
>>>
>>> I have a problem when backup is running, it complains that some files
>>> are missing. The problem is that i have situation when data load is running
>>> and a lot of WAL files are generated.
>>>
>>> Regards,
>>> Slavcho
>>>
>>>
>>
>>
>> --
>> Bert Desmet
>> 0477/305361
>>
>
>
If you're using pg_basebackup, be sure and use the --xlog-method=stream
(-Xs) option. This ensures that all the WAL files that are needed for a
consistent backup are kept as part of the base backup and it does them
real-time as the WALs are generated. This avoids the problem you're
encountering where, by the end of the backup, the WAL files you need have
already been recycled. It opens up a secondary replication connection to
the database to do this, so ensure you have max_wal_senders set high enough
to allow two connections per backup running.

Keith