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