Thread: secure deletion of archived logs

Greetings,

* Zwettler Markus (OIZ) (Markus.Zwettler@zuerich.ch) wrote:
> with Oracle we use "backup archivelog all delete all input".
> this is a kind of atomic transaction.
> everything backuped for sure is deleted.
>
> with Postgres we archive to a local host directory

... how?  Do you actually sync the files after you copy them with an
fsync to be sure that they're durably stored there?  If not, then
there's a pretty good chance that you'll lose some WAL if a crash
happens because if your archive command returns successful, PG will
removed its copy of the WAL file.

Hint: using 'cp' as an archive command is a very bad idea.

> we do a Networker backup of this directory afterwards and delete the archived logs
> but this is not an atomic transaction
> so there is a small risk that something gets deleted which is not backuped

That would definitely be quite bad, particularly if a WAL file that was
needed for a backup to be consistent was removed or missed, as that
backup would no longer be valid then.

> how to you prevent this?

I would strongly recommend that you use a tool that's actually built for
the purpose of backing up PG systems, like pgbackrest or similar.
Writing your own custom code for managing WAL archives and backup sets
is likely to result in issues.

> Is there any backup tool which can do backups analogous Oracle?

There's quite a few different tools available for backing up PG systems,
with various features and performance- from simple things like
pg_basebackup (which you can set up to include all the WAL for the
backup to be consistent, though that doesn't do anything to help you
with managing WAL for PITR), to much more sophisticated tools like
pgbackrest, wal-g, and others that help with managing WAL and dealing
with expiring out backups and such.  The biggest thing is- don't try to
roll your own.

Thanks,

Stephen

> Greetings,
>
> * Zwettler Markus (OIZ) (Markus.Zwettler@zuerich.ch) wrote:
> > with Oracle we use "backup archivelog all delete all input".
> > this is a kind of atomic transaction.
> > everything backuped for sure is deleted.
> >
> > with Postgres we archive to a local host directory
>
> ... how?  Do you actually sync the files after you copy them with an fsync to be
> sure that they're durably stored there?  If not, then there's a pretty good chance
> that you'll lose some WAL if a crash happens because if your archive command
> returns successful, PG will removed its copy of the WAL file.
>
> Hint: using 'cp' as an archive command is a very bad idea.
>
> > we do a Networker backup of this directory afterwards and delete the
> > archived logs but this is not an atomic transaction so there is a
> > small risk that something gets deleted which is not backuped
>
> That would definitely be quite bad, particularly if a WAL file that was needed for a
> backup to be consistent was removed or missed, as that backup would no longer
> be valid then.
>
> > how to you prevent this?
>
> I would strongly recommend that you use a tool that's actually built for the purpose
> of backing up PG systems, like pgbackrest or similar.
> Writing your own custom code for managing WAL archives and backup sets is
> likely to result in issues.
>
> > Is there any backup tool which can do backups analogous Oracle?
>
> There's quite a few different tools available for backing up PG systems, with
> various features and performance- from simple things like pg_basebackup (which
> you can set up to include all the WAL for the backup to be consistent, though that
> doesn't do anything to help you with managing WAL for PITR), to much more
> sophisticated tools like pgbackrest, wal-g, and others that help with managing
> WAL and dealing with expiring out backups and such.  The biggest thing is- don't
> try to roll your own.
>
> Thanks,
>
> Stephen




We use "rsync" on XFS with "wsync" mount mode. I think this should do the job?


The tools mentioned will all do backup to disk.
We are required to do backup to tape.


Markus

Greetings,

* Zwettler Markus (OIZ) (Markus.Zwettler@zuerich.ch) wrote:
> We use "rsync" on XFS with "wsync" mount mode. I think this should do the job?

No, that just makes sure that namespace operations are executed
synchronously, that doesn't provide any guarantee that the data has
actually been written out and sync'd.

> The tools mentioned will all do backup to disk.
> We are required to do backup to tape.

Back up to disk first and then tar to tape.

Thanks,

Stephen

>
> Greetings,
>
> * Zwettler Markus (OIZ) (Markus.Zwettler@zuerich.ch) wrote:
> > We use "rsync" on XFS with "wsync" mount mode. I think this should do the job?
>
> No, that just makes sure that namespace operations are executed synchronously,
> that doesn't provide any guarantee that the data has actually been written out and
> sync'd.
>

What else? "rsync" on XFS mounted wsync + execute "sync" afterwards?


> > The tools mentioned will all do backup to disk.
> > We are required to do backup to tape.
>
> Back up to disk first and then tar to tape.
>
> Thanks,
>
> Stephen


Markus

Greetings,

* Zwettler Markus (OIZ) (Markus.Zwettler@zuerich.ch) wrote:
> > * Zwettler Markus (OIZ) (Markus.Zwettler@zuerich.ch) wrote:
> > > We use "rsync" on XFS with "wsync" mount mode. I think this should do the job?
> >
> > No, that just makes sure that namespace operations are executed synchronously,
> > that doesn't provide any guarantee that the data has actually been written out and
> > sync'd.
>
> What else? "rsync" on XFS mounted wsync + execute "sync" afterwards?

I don't really agree with it, but pitrery uses 'dd' with 'conv=fsync'.
As far as I know, there isn't a way to force 'rsync' to run an fsync()
at the end, and executing a 'sync' afterwards, while it should work,
seems likely to cause you a lot more troubles..

In the end, I wouldn't suggest trying to hack up your own scripts to do
any of this- use one of the existing tools.  It's a lot more complicated
than I think you're appreciating.

Thanks,

Stephen