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 :

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

And I am considering the below approaches :

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

Which is the most efficient or even possible from above? Any other recommendation?

  • Why differential rather than incremental? – jjanes Jan 4 '19 at 14:36
  • Let's call them differential incremental backups – user1263746 Jan 5 '19 at 18:58
  • Have a look at pgBarman pgbarman.org – a_horse_with_no_name Jan 20 '19 at 9:51
  • A detailed canonical answer is required, can you please post the steps involved in setting up pgBarman. – user1263746 Jan 20 '19 at 14:39
  • 2
    There is already a detailed canonical answer: it's calle the manual - why should I duplicate it? – a_horse_with_no_name Jan 20 '19 at 15:00

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!

  1. 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 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.