We have a number of production systems (of varying versions 13 to 15) setup with warm spares doing nightly backups and restores. It seems that the default behavior (for FreePBX Restores) is for the CDR database to be restored so that it clears the current tables during the import of the SQL backup file.
This is desirable in most situations, but I wonder if it might be possible to alter the restore script for particular situations so that the SQL tables of CDR data are appended to during the import rather than overwritten ?
There is nothing that does this now. There is no “incremental” backup/restore function. It’s all or nothing. If you want something like this, you’ll have to write it.
The problem is that a nuke in pave takes a decent amount of time when you have sometimes over a million rows. To merge would take an exponentially longer amount of time and would likely be impractical. on something like a warm spare there should generally be no independent data. If the goal is to speed things up the fastest way is to do the overwrite.
We generally trim the CDR DB on a cron job so they don’t get too large, and we run the warm spare restores in the middle of the night so the impact and time taken does not matter too much.
The reason for wanting to do this has come up a couple of times recently. One example was where we had a data center that was having network connectivity issues only to particular peers so some of the clients were running fine and others were not. We moved across to the warm spares with a DNS change but during the (very short) TTL for the clients to pickup the new destination of the IP address the working clients made more calls while other clients were already making calls on the warm spare. Therefore to keep all call data we need to (at some point - perhaps over night) merge the cdrdb from both servers. I’m sure there are lots of other situations where this merging might be useful?
So at the moment its seems we should not include the CDR DB in the warm spare ‘backup’ job and instead we
could sqldump the cdr data out, ssh it across to the warm spare and then do an sql import job that doesn’t drop the current table. Of course, we would need to do the same onto the primary server before we switch the clients back to using it.
I will say 80% of users do NOT trim their CDR’s
In the future there could be a merge driver or something but that isn’t even on the radar. Currently I would exclude the CDR/CEL.
You could do what I do for clients that want something like this but are concerned about their CDRs being overwritten or knowing which calls went to which machines.
You can also do the backup yourself using options on the mysqldump command. I can’t get at the exact options right now, but you want to include “Ignore duplicates” and “Do Not Destroy the old database”. There is also an option that allows you to replace records (instead of an insert) so that, if you have records that have been updated in the live data, you get the full effect of the database update from the incremental add.
I don’t really want an external database server for the CDRs if I can help it. I would have to have 2 servers for redundancy so I would probably be back with this issues again ! I already have nightly backups of the CDR database anyway and I don’t use them for billing so they are not business critical they are just needed by the end users to find voice recordings and to see activity.
Thanks everyone for the comments - all very helpful and helped me think through the subject a bit better.
