Technical Tip: Restarting SQL rebuilds
Description
This article describes how to restart or check on the progress of SQL rebuilds in FortiAnalyzer.
In most cases, once a SQL rebuild has been initiated on a FortiAnalyzer, it is best to let it complete.
However, in some cases, it might be desirable to restart that SQL rebuild.
This can be useful to restart the rebuild from a much later start-time. In that case, the rebuild would be faster and the resulting database would be much smaller.
Scope
FortiAnalyzer.
Solution
In the example below, change the start-time from the default value. For example, if the analytics policy is set for 60 days, it is recommended to change the start-time to count 60 days before the current day.
config system sql
...
set start-time 10:00 2025/04/01 # <----- Change this to some later time & date.
Important: This command will set sql database start time to 10:00 2025/Apr/01!
Do you want to continue? (y/n)y
end
Current rebuild status:
diagnose sql status rebuild-db
Rebuilding log SQL database has been processed 40%.
Run the following command in order to remove the rebuild database flag (once performed, the task should be terminated and the rebuild can now be re-initiated)
diagnose sql remove rebuild-db-flag: This will terminate the rebuild by removing the rebuild database flag
exec sql-local rebuild-db: This will initiate the rebuild
After the resulting reboot, check the rebuild status again. Notice that it will reflect a lower %, indicating that the rebuild has restarted.
After a reboot:
diagnose sql status rebuild-db
Rebuilding log SQL database has been processed 5%
Note: Rebooting a FortiAnalyzer during the database rebuild process does not cancel the rebuild. The rebuild will resume from wherever it left off before reboot.
Alternate Approach: Using rebuild-adom.
It is also possible to request the rebuild of only a single ADOM. Again, re-issue the same command to do this.
Note: 'Rebuild-Adom' is not supported on '7.6' releases.
First, run the following:
execute sql-local rebuild-adom root
Rebuild log SQL database of ADOM 'root' has been requested.
This operation will remove the log SQL database for ADOM 'root' and rebuild from log data.
Do you want to continue? (y/n)y
Request to rebuild ADOM 'root' submitted successfully.
diagnose sql status rebuild-adom
...
root percent: 52% bg-rebuild:Yes start:"Mon () 2015_06_01 16:54:10" took:138(s) remain:127(s)...
Afterwards, run the following:
execute sql-local rebuild-adom root
...
diagnose sql status rebuild-adom
...
root percent: 0% bg-rebuild:Yes start:"Mon (1) 2015_06_01 16:56:41" took:1(s) remain:10(s)...
In this case, the time at which the rebuild request was submitted is displayed and shows as being later than the first attempt.
After upgrade FortiAnalyzer the database rebuild is performed using Archive logs. In this behavior, the Analytic logs will not be restored.
To check the status of the database rebuild after the upgrade, run diagnose sql status upgrade-db.
- diagnose sql status upgrade-db: Will show the rebuilt database after upgrade.
- diagnose sql status rebuild-db: Will show the rebuilt database, performed manually via the CLI.
If the diagnose sql status upgrade-db process gets stuck and shows the error 'PostgresSQL upgrade fail' in the dashboard after the FortiAnalyzer upgrade, rebuild the FortiAnalyzer database manually.
Related articles: