DELETE CDR and Call Recording from DB?

Status
Not open for further replies.
T

Tuly

Member
Sep 6, 2017
42
1
8
39
can anyone post a scrip that will DELETE all CDR older from x days from the DB, and also delete call recordings from the DB older then x days, (i know how to delete the files itself)



Thank you!
 
Code: 
delete from v_xml_cdr where start_stamp < (CURRENT_DATE - INTERVAL '90 day')::date;
 
Personally I use these in a cron job which can be adapted to suit:
Code: 
10 0 * * * /usr/bin/find /var/lib/freeswitch/recordings/*/archive -type f -mtime +180 -exec rm -rf {} \;
15 0 * * * /usr/bin/sudo -u postgres psql fusionpbx -c "DELETE FROM v_xml_cdr WHERE to_timestamp(start_epoch) < NOW() - INTERVAL '365 days';" > /dev/null

So the first deletes recordings older than six months, the second cdrs over a year.
 
  • Like
Reactions: ZPM and Starblazer
when I
DigitalDaz said:
Personally I use these in a cron job which can be adapted to suit:
Code: 
10 0 * * * /usr/bin/find /var/lib/freeswitch/recordings/*/archive -type f -mtime +180 -exec rm -rf {} \;
15 0 * * * /usr/bin/sudo -u postgres psql fusionpbx -c "DELETE FROM v_xml_cdr WHERE to_timestamp(start_epoch) < NOW() - INTERVAL '365 days';" > /dev/null

So the first deletes recordings older than six months, the second cdrs over a year.
Click to expand...
When I input psql then it ask password. I typing password find in file /etc/fusionpbx/config.php then not accep. why? help me
 
Have you installed from the standard installer script? There should be no need for a password using the above.
 
DigitalDaz said:
Have you installed from the standard installer script? There should be no need for a password using the above.
Click to expand...

Thank Daz.
I do installed from web fusionpbx.com
It worked. But I use form :

export PGPASSWORD='password'; psql -h 'server name' -U 'user name' -d 'base name' -c 'command'

with information in file /etc/fusionpbx/config.php
 
I will reiterate this just one more time, using the details in the script, it is not needed. It uses sudo.
 
Using your info above ( and I thank you very much) I was able to set up the cron job for recordings and it works nicely.

However when I set up a 2nd cron job to delete the call logs I got this error message:

sudo: unable to resolve host SAFEHOSTNAME: Name or service not known

Also, anyone know why it says SAFEHOSTNAME? I didn't choose that
 
What script are you using? SAFEHOSTNAME sounds like a placeholder for your real host name as specified in the entries in your sudoers file. (visudo).

Be careful when picking up information from old threads, this one was started in 2017, a lot has changed since then. Old threads are a great resource but don't blindly use code without checking compatibility with your current system.

The FusionPBX installer now puts a couple of maintenance jobs in /etc/cron.daily/ Have a look at them.

I generally delete the jobs in /etc/cron.daily/ because I prefer to run my own jobs and at specific times of the day, but the default jobs work well and are a good reference source for those new to Fusion.
 
Last edited:
Status
Not open for further replies.
Top Bottom