Culling the database

[roger_roger]

The postgres database continuously grows and a lot of the data can be safely deleted. I run these scripts as part of my nightly maintenance and have cut the size of my postgres database by 70%.

Device logs can quickly take up a lot of space and can safely be removed. You can replace the interval amount with a value that works for you
psql --host=127.0.0.1 --username=fusionpbx -t -c "delete from v_device_logs where timestamp < NOW() - interval '10 days'";

If you are running the new SMS message queue, it will grow rapidly. Right now I have mine set to 1 day because I'm only testing.
psql --host=127.0.0.1 --username=fusionpbx -t -c "delete from v_message_queue where message_date < NOW() - interval '1 days'";

The JSON field in v_xml_cdr has useful data for debugging calls but after time, the data is stale and no longer useful.
psql --host=127.0.0.1 --username=fusionpbx -t -c "update v_xml_cdr set json=NULL WHERE start_stamp < NOW() - INTERVAL '10 days'";

Finally, run a vacuum to clean up the database.
psql --host=127.0.0.1 --username=fusionpbx -t -c "vacuum (full)";

 

[Adrian Fretwell]

@roger_roger Some useful one-liners, thank you for sharing. I like the idea of keeping the CDR record but removing the JSON content.

 

[roger_roger]

@roger_roger Some useful one-liners, thank you for sharing. I like the idea of keeping the CDR record but removing the JSON content.

I also use the .pgpass file so I don't have to include the password when running scripts.

In the /root directory, create a file called .pgpass with the following contents:

127.0.0.1:5432:fusionpbx:fusionpbx:your-fusionpbx-password

Then run chmod 600 on the .pgpass file and you're good to go.

 

[ad5ou]

I'll add a little tidbit for anyone who finds this. If you don't want to create .pgpass file, you can add the following to a script before the database commands and it will pull the password from the fusionpbx config file.

PGPASSWORD="$(grep db_password /etc/fusionpbx/config.php | cut -d "'" -f2)"
export PGPASSWORD

 

[DigitalDaz]

No need for the password at all:

00 3 * * * /usr/bin/sudo -u postgres psql -d fusionpbx -c "update v_xml_cdr set json=NULL WHERE start_stamp < NOW() - INTERVAL '10 days';" >/dev/null 2>&1

 

[ad5ou]

Multiple ways to skin a cat, but the cat doesn't like any of them