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 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
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 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.
-----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 filesOn 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 the backup 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 to the 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 filesOn 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 filesOn 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