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?