pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
AW: [Extern] Re: postgres restore & needed history files
3+ messages / 3 participants
[nested] [flat]

* AW: [Extern] Re: postgres restore & needed history files
@ 2023-01-12 16:50  Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
  0 siblings, 2 replies; 3+ messages in thread

From: Zwettler Markus (OIZ) @ 2023-01-12 16:50 UTC (permalink / raw)
  To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

> -----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 the backup retention?

Thanks, Markus




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: AW: [Extern] Re: postgres restore & needed history files
@ 2023-01-12 17:12  Ron <ronljohnsonjr@gmail.com>
  parent: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
  1 sibling, 0 replies; 3+ messages in thread

From: Ron @ 2023-01-12 17:12 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: AW: [Extern] Re: postgres restore & needed history files
@ 2023-01-13 10:25  Laurenz Albe <laurenz.albe@cybertec.at>
  parent: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
  1 sibling, 0 replies; 3+ messages in thread

From: Laurenz Albe @ 2023-01-13 10:25 UTC (permalink / raw)
  To: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-01-13 10:25 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-12 16:50 AW: [Extern] Re: postgres restore & needed history files Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
2023-01-12 17:12 ` Ron <ronljohnsonjr@gmail.com>
2023-01-13 10:25 ` Laurenz Albe <laurenz.albe@cybertec.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox