Re: postgres restore & needed history files

> On Jan 5, 2023, at 21:28, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Tue, 2023-01-03 at 16:03 +0000, Zwettler Markus (OIZ) wrote:
>> We are using a DIY Postgres backup:
>> ---
>> psql -c "select pg_start_backup ('Full');"
>> save -s "${NSR_SERVER}" -g "${NSR_POOL}" "${PGDATA}"
>> psql -c "select pg_stop_backup();"
>> ---
>> The pg_wal directory is not saved with it because it is a linked directory.
>>
>>
>> After some time, we had to perform a point-in-time recovery of 1 of the 5 databases to a point in time 7 days in the past.
>> We tried to perform a point-in-time restore on another host, which did not work until we copied the contents of the current pg_wal directory.
>> The current pg_wal directory included 8 history files: 00000002.history to 00000009.history.
>> The point-in-time restore worked smoodly after it had all these history files.
>>
>>
>> Afaik, all necessary history files should also be restored by the restore_command.
>> I had a look at our archived wal backups and found that 00000002.history to 00000008.history files already had been deleted due to our NSR backup retention of 30 days.
>>
>> Question: Is it necessary to retain all history files?
>
> Yes, the history files are an integral part of the database.
> You must not delete them from your WAL archive.

It's almost never a good idea to use storage retention rules to expire out files from a PostgreSQL backup archive. The logic of what files you need to restore a particular backup are not easily mapped onto "delete after x days" rules.