Обсуждение: postgres restore & needed history files
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?
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
currentpg_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
deleteddue 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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
> On Jan 5, 2023, at 21:28, Laurenz Albe <laurenz.albe@cybertec.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
currentpg_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
deleteddue 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
logicof what files you need to restore a particular backup are not easily mapped onto "delete after x days" rules.
> -----Ursprüngliche Nachricht-----
> Von: Laurenz Albe <laurenz.albe@cybertec.at>
> Gesendet: Freitag, 6. Januar 2023 06:28
> An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql-
> general@lists.postgresql.org
> Betreff: [Extern] Re: postgres restore & needed history files
>
> 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.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
What would you do in case of a disaster when all history files in pg_wal are gone and also deleted in the backup due to
thebackup retention?
Thanks, Markus
On 1/12/23 10:50, Zwettler Markus (OIZ) wrote: [snip] > What would you do in case of a disaster when all history files in pg_wal are gone and also deleted in the backup due tothe backup retention? Yet another reason why you should not roll your own PITR backup solution. Use something like pgBackRest (what I use) or BarMan. -- Born in Arizona, moved to Babylonia.
On Thu, 2023-01-12 at 16:50 +0000, Zwettler Markus (OIZ) wrote: > What would you do in case of a disaster when all history files in pg_wal are gone > and also deleted in the backup due to the backup retention? Easy. Take a new base backup immediately and don't try to restore a backup that was taken on an earlier timeline. Yours, Laurenz Albe
> -----Ursprüngliche Nachricht----- > Von: Laurenz Albe <laurenz.albe@cybertec.at> > Gesendet: Freitag, 13. Januar 2023 11:25 > An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql- > general@lists.postgresql.org > Betreff: Re: AW: [Extern] Re: postgres restore & needed history files > > On Thu, 2023-01-12 at 16:50 +0000, Zwettler Markus (OIZ) wrote: > > What would you do in case of a disaster when all history files in > > pg_wal are gone and also deleted in the backup due to the backup retention? > > Easy. Take a new base backup immediately and don't try to restore a backup that > was taken on an earlier timeline. > > Yours, > Laurenz Albe Hope I got you right. You meant the latest backup within the latest timeline is always working even if the latest history files are gone. Thanks, Markus
On Fri, 2023-01-13 at 11:23 +0000, Zwettler Markus (OIZ) wrote: > > -----Ursprüngliche Nachricht----- > > Von: Laurenz Albe <laurenz.albe@cybertec.at> > > Gesendet: Freitag, 13. Januar 2023 11:25 > > An: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql- > > general@lists.postgresql.org > > Betreff: Re: AW: [Extern] Re: postgres restore & needed history files > > > > On Thu, 2023-01-12 at 16:50 +0000, Zwettler Markus (OIZ) wrote: > > > What would you do in case of a disaster when all history files in > > > pg_wal are gone and also deleted in the backup due to the backup retention? > > > > Easy. Take a new base backup immediately and don't try to restore a backup that > > was taken on an earlier timeline. > > Hope I got you right. > > You meant the latest backup within the latest timeline is always working even if the latest history files are gone. Yes. Yours, Laurenz Albe