Reliable way to take out copy from barman to offsite

[Albert Kivi]

Hi

We have many small-medium environments, where we use postgres and barman (same hosting provider). 
We needed to add disaster recovery to offsite option to every these sites.

Just in case i mention that, barman of barman is not an valid option in our case.
So, idea was to use post_backup_script.

post_backup_script logic (light view)
get relevant files of backup: barman list-files ${BARMAN_SERVER} ${BARMAN_BACKUP_ID}
tar these list of files
encrypt tar file
trigger transfer of this encrypted file to offsite location

At first we stumbled to a problem where post_backup_script is started before backup is fully completed and end WAL file is not yet writen to wals directory. This triggers non valid backup and last WAL is not present in tar file and we are not able to restore from this single offiste copy...

Solution ẃas to use --wait flag. At first glance it seems to be working. 

Is there any potential issues with --wait flag?
Is there possibility to make --wait as a permanent config option?
Is there any other valid and trustworthy method for our requirement?

[Mike Wallace]

Hi Albert,

Using a post backup hook script to tar, encrypt and copy the backup
offsite seems like a good idea to me.

The only behaviour change involved in using `--wait` is that the
backup process will always wait for the last WAL required by the
backup before completing - this is only a potential issue if WAL
archiving is backlogged, in which case `barman backup` processes will
be waiting around when they could otherwise have completed. The reason
for `--wait` being off by default is so that backups can complete
independently of WAL archiving and then the `barman cron` process can
update the backup state to DONE later, when the required WALs have
been copied into Barman's archive.

I can think of a couple of potential alternatives to the approach you
used, though neither of them fully meet your requirements:

1. Using barman-cloud-backup as a post backup hook script [1], so that
the backups are pushed to an object store in either AWS, Azure or
Google Cloud Platform (this would also require setting up a pre
archive script to also copy WALs into the object store). This would
automatically combine the files into tarballs, split them into chunks
to stay within cloud provider object size limits, and allow the use of
cloud-provider-managed keys to encrypt the objects. If a cloud object
store was an acceptable offsite location then this could work for you.

2. Using geo-redundancy [2] to sync the backups and WALs to a remote
Barman server. Unfortunately this approach does not provide an easy
way of also encrypting the backup before syncing to the passive Barman
server, so I don't think it would help here.

Best regards,

Mike

[1]: https://docs.pgbarman.org/release/3.5.0/#barman-cloud-hook-scripts
[2]: https://docs.pgbarman.org/release/3.5.0/#geographical-redundancy

> --
> --
> You received this message because you are subscribed to the "Barman for PostgreSQL" group.
> To post to this group, send email to pgba...@googlegroups.com
> To unsubscribe from this group, send email to
> pgbarman+u...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/pgbarman?hl=en?hl=en-GB
>
> ---
> You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
> To view this discussion on the web, visit https://groups.google.com/d/msgid/pgbarman/97d10495-f52c-4f47-a24f-a4f125ae7dcdn%40googlegroups.com.

[Albert Kivi]

Mhmm...

barman list-files is acting weird. It should list all files and directories needed for restore, but it won't.

barman list-files lists only directories where are files inside, it won't list empty directories, what are essential for postgres after restore.

For example if you execute barman list-files SERVER BACKUPID
You probably have these folders missing from output: pg_notify pg_tblspc pg_replslot pg_twophase pg_stat pg_snapshots pg_commit_ts pg_dynshmem pg_serial pg_stat_tmp pg_subtrans pg_logical/snapshots pg_logical/mappings

These files are initially present in base folder...