backup_method: postgres vs. rsync

[Alessandro Dentella]

Hi,

While fine-tuning our backup strategy I realized that if using

  backup_method = postgresql

you won't get the configution files while restoring.

On the contrary all conf files are restore if using method rsync.

Surely this makes me prefer 'rsync'. Is there some other reason that should 

make me reconsider this question?

Which are the advantagest of the 'postgres' method?

thanks in advance

sandro

*:-)

[Gabriele Bartolini]

Hi Alessandro,

2018-04-10 19:49 GMT+02:00 Alessandro Dentella <sandro....@gmail.com>:

While fine-tuning our backup strategy I realized that if using

  backup_method = postgresql

Just a clarification: it should be 'postgres'.

you won't get the configution files while restoring.

It depends on whether configuration files are inside PGDATA or not. Barman relies on pg_basebackup, hence it inherits pros and cons of this tool. pg_basebackup indeed copies only files inside PGDATA and tablespace directories. PGDATA normally contains configuration files on RedHat and CentOS distros, while Debian/Ubuntu based ones store them in /etc. In this case, pg_basebackup cannot do anything about it and DBAs/sysadmins must take care of that.

 

On the contrary all conf files are restore if using method rsync.

Correct, that's because we have more control (as we can execute commands on the remote system, while with postgres method we simply use the streaming replication channel). 

Surely this makes me prefer 'rsync'. Is there some other reason that should 

make me reconsider this question?

It is your choice. I myself prefer rsync method on a general basis, as it also allows me to use parallel and incremental backup. But postgres method has got its good reasons to be used - there are definitely use cases for it.

Which are the advantagest of the 'postgres' method?

Security-wise, postgres method allows you not to open SSH access to the system, and control everything over streaming replication.

Also, you can use it to backup Postgres on Docker or Windows.

These are the main ones. Hope this helps! :)

Ciao,

Gabriele