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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJd57UXAAoJEO1sijiDR2RV55wP/iZTcvcyZQtWOA4ETLG4xbMC
LjJ/LQaYRTVyzSik0Br1ZQBZM7TbMyBjOqdcpIoKbr1D6TvkAcBFCa838V0715oD
sFFjCpo5G2mbry6XAlLaioArGDFnjsu3oEaKe1kpBvgrCxxudKPX8ERv3X1QM8ga
Sd2emegLn9czPfDTqnWltdzJkmkcgJPNfq56PWphn5S7QnKPozIwYJU0fE3ty0OQ
i/EqlSTuqhUnM5G1HGgMZaujkg4QPAasMMpBLPSv3pQYSwTcVssVwXricjW0gj4V
8VGqYrLIuOXh98cC9y3ov3Dpd/yMccwsNB23zyFZUphlApf23aDm0M44fK2rATaN
+M84RpeOGubVA7HrqZ+nCvu1ZRNOX8xyxQy2BSlYy/jNhzFoeT2+LdqgcCu3C863
XCEupP/hjo01BVwA/Wd16BErVh36D2rI8x1iqI0V/X6cj8jkFR2AMydVVpiphQRP
E0Xtztnep/RAbPjaMUJ0ipyqg8K83RbQixFhOexSc76aM70pEx41EgvTGINyVJzI
OQq96/3hW0KChE9NAQppKZFKsRlUBr3LRKsHxg40arkcwzsrg/uhPVTH7uMgMjN7
pBmI8LcTwwl+wig5AvsDsLk51Yn6lPUC+iLeiaZIRFfnAWPneX9SuJdaccI3l7m5
2XccwdT0ttQLP8w2REgs
=3Dci
-----END PGP SIGNATURE-----