Em 12/12/2017 10:14, marcelo escreveu:
Hi Sam
You are right, and here are the reason behind my question: The server
where postgres will be installed is not on 24/7. It
turns on in the morning and goes off at the end of the day.
The idea is that, as part of the shutdown process, a local
backup is made. The next day, that backup will
be copied to the cloud.
In order not to lengthen the shutdown process, we are
trying to limit pg_dump to the databases that have had some
change, not so much in their schema as in their data.
Of course, to add a trigger for every table and CUD
operation on every database is not an option.
On 11/12/17 23:23, Sam Gendler wrote:
I think there's a more useful question, which is
why do you want to do this? If it is just about conditional
backups, surely the cost of backup storage is low enough, even
in S3 or the like, that a duplicate backup is an afterthought
from a cost perspective? Before you start jumping through
hoops to make your backups conditional, I'd first do some
analysis and figure out what the real cost of the thing I'm
trying to avoid actually is, since my guess is that you are
deep into a premature optimization here,
where either the cost of the duplicate backup isn't
consequential or the frequency of duplicate backups is
effectively 0. It would always be possible to run some kind
of checksum on the backup and skip storing it if it matches
the previous backup's checksum if you decide that there truly
is value in conditionally backing up the db. Sure, that would
result in dumping a db that doesn't need to be dumped, but if
your write transaction rate is so low that backups end up
being duplicates on a regular basis, then surely you can
afford the cost of a pg_dump without any significant impact on
performance?
Hi, there are plenty of options for secure and optimized bacup
solutions for your scenario.
Since you want to backup to the cloud, why not to use pgBarman with
diferential backup + log (wal) shipping?
It is perfect, you will have zero downtime, and you can shutdown
your database anytime you want.
In my experience, diferential backups (with rsync and symlinks)
provide excellent performance and reduced storage (avoid
duplications), and works perfectly well, as it also provides
automatic rotation for old backups (you define the rules from a set
of options).
I've been using pgBarman since 1.4, and I'm very satisfied with it.
Just my 2c,
Edson Richter
|
