cancel
Showing results for 
Search instead for 
Did you mean: 

Backing up PostgreSQL BART server

elanmbx
Level 6

Does anyone have experience backing up PostgreSQL via a dedicated BART (Backup and Recovery Tool) server?

The issue seems to be deduplication - my DBA reports that PostgreSQL does compression *within* the database itself, so it would seem that deduplication is going to be an issue with PostgreSQL.

For reference - I am only getting ~65% dedup on a 1.1TB database.  Which, as you can guess, is gobbling up my MSDP really quite quickly.

I fear that I'm going to be stuck in an untenable situation with this.  Anyone have any experience with PostgreSQL backups like this?

1 REPLY 1

sdo
Moderator
Moderator
Partner    VIP    Certified

No experience with that particular combination.

With a 9.5TB SQL instance of multiple 1TB databases that DBAs inisited on dumping to disk in SQL compressed format and keeping their allowed three warm copies locally, we proved that it would be faster for SQL to dump without compression, and then we proved that the cost of huge amounts of back-end MSDP disk space for 20 copies of compressed dumps (8 x 8 week retention, plus 12 x 2 week retention) was far in excess of the cost of increased disk space usage at the front-end SQL side for three local warm copies.  Those databases were full of blobs of unique binary data (which did not compress at all) and so overall average database compression on the dumps had little to gain of around 15% to 20%, and the databases themselves were quite full with not much white space.  The difference for dedupe was incredible.  It was clear proof that the belief that some DBAs have of "database compression" *always* winning the day is wrong.  In this particular case the DBAs weren't even close.  MSDP dedupe won hands down, it won on cost, it won on performance.  Of course they won't all be like this, and this was a large database with unusual content.  But the lesson is, when databases get big, it may well be worth exploring the options.

In my experience, when things get this big - then it always comes down to cost.  Basically, you have a 12 hr backup window, so all we have to do is test it, prove it, size it, cost it, and meet that 12 hr backup window.  Remember, a 11:59 run time is still acceptable.  As long as you meet the backup run window (and will continue to meet the backup window in the future) then the best sweetest technical solution is not necessarily the one to be selected.