AW: secure deletion of archived logs

> Greetings,
>
> * Zwettler Markus (OIZ) (Markus(dot)Zwettler(at)zuerich(dot)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