Re: [ADMIN] Strange times in WAL files in archive directory (9.3)

Hi Achilleas,

On 1/24/17 8:29 AM, Tom Lane wrote:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>
>> 2) From now on, can we assume a monotonic relation between file names and creation times in order to base our
maintenancescripts on this? 
>
> I wouldn't.  You risk deleting a file just as it starts to be used.
> You could possibly look into pg_control to see the current LSN and
> avoid touching any files >= that point, but of course this complicates
> the maintenance logic quite a bit.
>
> The larger issue here is that you're confusing the function of an archive
> area with that of the active WAL directory.  The server will prune what is
> in the active WAL directory and does not want your help.  In an archive
> directory, I'd expect the files to have monotonic timestamps corresponding
> to the times you copied them over to the archive, so you could rely on
> the timestamp sequence there.

Agreed, that should work.

However, this just the beginning of the complexities you will encounter
when dealing with WAL archiving and expiration.  You should consider
using a mature third-party solution like pgBackRest or Barman.

--
-David
david@pgmasters.net