Postgres 9.6 - Taking differenctial backup I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet.

What is the best approach to take differential backup? I am considering below points :


Central Backup Server may not be up 24/7.
Internet Link may fail.
Bandwidth Consumption on the DB server.


And I am considering the below approaches :


Directly Rsync the Postgres data directory.
Some kind of log archive, to be read by Postgres.
Best of open source solution such as the pitrtool or pgpool2.


Which is the most efficient or even possible from above? Any other recommendation?
Please don't re-invent the wheel! There are so many traps in backup up and recovering the probability you fall into them is pretty high!


  
  Directly Rsync the Postgres data directory.
  


Please don't! You'll copy unconsistent data...

I think you should consider one of these 3 tools :


barman 
backrest
probackup


Barman and probackup have real differential backups and will check backup consistency before restoring a backup. That's why I prefer one of those tools.

You can find a complete comparison in Anastasia Lubennikova's excellent conf talk Advanced PostgreSQL backup and recovery methods slides.