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