Re: 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(at)matrix(dot)gatewaynet(dot)com> writes:
>
>> 2) From now on, can we assume a monotonic relation between file names and creation times in order to base our maintenance scripts 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(at)pgmasters(dot)net