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?
-
Why differential rather than incremental?jjanes– jjanes2019年01月04日 14:36:18 +00:00Commented Jan 4, 2019 at 14:36
-
Let's call them differential incremental backupsuser1263746– user12637462019年01月05日 18:58:28 +00:00Commented Jan 5, 2019 at 18:58
-
Have a look at pgBarman pgbarman.orguser1822– user18222019年01月20日 09:51:14 +00:00Commented Jan 20, 2019 at 9:51
-
A detailed canonical answer is required, can you please post the steps involved in setting up pgBarman.user1263746– user12637462019年01月20日 14:39:13 +00:00Commented Jan 20, 2019 at 14:39
-
2There is already a detailed canonical answer: it's calle the manual - why should I duplicate it?user1822– user18222019年01月20日 15:00:14 +00:00Commented Jan 20, 2019 at 15:00
1 Answer 1
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 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.