We are finally planning to move from Freepbx 16 to 17. The big problem we are facing is that since we have to use the same server we are trying to minimize downtime. The server has many years of CDR info and the database dump is over 10GB. While restoring on a test server it takes over 2 hours to restore the CDR. Is there a faster way to import the database? What would happen if we only restore configs, start operations again and then start restoring the CDR from the dump file?
Any pointers?
Do you need to keep all of the historic CDR, or can older records be pruned?
The CDR can be backed up and restored separately at a less critical time, but I would test that process carefully first. The main risk is importing historic CDR after the new system is live and accidentally overwriting or conflicting with current CDR data.
Do you only need to restore CDR, or do you also need CEL? Are you using the standard Asterisk schema?
In my opinion, restoring only the FreePBX configuration first, starting operations on the new PBX, and importing the historical CDR afterwards can be a valid approach to minimize downtime. CDR is usually the easier part, especially if the historical data belongs to a date range before the new PBX starts generating new records.
CEL needs more care. The cel table normally has an id primary key with AUTO_INCREMENT. If the historical dump explicitly contains the old id values, importing it directly after the new PBX has already generated CEL rows can cause primary-key collisions. The safer CEL approach is to import without the id column and let the new database generate new IDs.
uniqueid and linkedid (especially) are used to correlate records. Since they are generally based on timestamp and incremental values, the risk of collision should be low if the old and new systems do not have overlapping time and date.
In our test environment, we have been able to restore both CDR and CEL after the new PBX was already running, and reporting still worked correctly. That said, we are still in a preliminary validation phase, so I would test this carefully.
We would be interested in hearing your view, as we are also working on a similar topic.
EDIT: I’m assuming you are doing those imports without the FreePBX’s “Backup and Restore” module..
Since this server runs a contact center we need to have at least 3 years of records immediately available for reporting and compliance. We can prune records older than that but that still leaves millions of CDR entries. We can do without the CEL records fortunately as they are not used at the moment.
What we are testing is trying to import the SQL dump of the CDR database after restarting operations to see if there are any records that will cause conflicts with the newer data. Since the CDR table does not have a Primary Key there should not be any possible duplicates.
That makes sense. I wrote this last week in case you needed it. It’s draft documentation and needs more testing, so please only try it out with a dev system first. I’m not even sure if I finished it, can’t remember now! I’ll look at it again when I have some time next week.