pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zwettler Markus (OIZ) <Markus.Zwettler@zuerich.ch>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: AW: [Extern] Re: postgres restore & needed history files
Date: Thu, 12 Jan 2023 16:50:24 +0000
Message-ID: <6a648ddf9d8747e7bc18fa06ac7f48a2@zuerich.ch> (raw)
In-Reply-To: <eead8d41fc9d9422902324cc6ded0a81cf66b730.camel@cybertec.at>
References: <0ce7906d55174fa6902c011c0aa0e884@zuerich.ch>
	<eead8d41fc9d9422902324cc6ded0a81cf66b730.camel@cybertec.at>

> -----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




view thread (3+ messages)  latest in thread

Message-ID: <6a648ddf9d8747e7bc18fa06ac7f48a2@zuerich.ch>
Permalink:  ../6a648ddf9d8747e7bc18fa06ac7f48a2@zuerich.ch/
Also on:    postgresql.org/message-id/6a648ddf9d8747e7bc18fa06ac7f48a2@zuerich.ch

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: Markus.Zwettler@zuerich.ch, laurenz.albe@cybertec.at
  Subject: Re: [Extern] Re: postgres restore & needed history files
  In-Reply-To: <6a648ddf9d8747e7bc18fa06ac7f48a2@zuerich.ch>
  References: <0ce7906d55174fa6902c011c0aa0e884@zuerich.ch> <eead8d41fc9d9422902324cc6ded0a81cf66b730.camel@cybertec.at> <6a648ddf9d8747e7bc18fa06ac7f48a2@zuerich.ch>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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