Greetings, * Zwettler Markus (OIZ) (Markus.Zwettler@xxxxxxxxxx) 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
Attachment:
signature.asc
Description: PGP signature
