AW: secure deletion of archived logs

> Greetings,
> 
> * Zwettler Markus (OIZ) ([email protected]) 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