CDR Archive Server
Note: This feature is on version 4.5+ and requires PostgreSQL ver 9.5+
Move the Records
touch /etc/cron.daily/db_copy.sh
chmod +x /etc/cron.daily/db_copy.sh
nano /etc/cron.daily/db_copy.sh
#!/bin/sh
#copy the data from the fusion db to a local csv file
psql --host=x.x.x --username=fusionpbx -c "\copy (SELECT * FROM v_domains) TO '/tmp/domains.csv' WITH CSV"
psql --host=x.x.x --username=fusionpbx -c "\copy (SELECT * FROM v_fax_logs) TO '/tmp/fax_logs.csv' WITH CSV"
psql --host=x.x.x --username=fusionpbx -c "\copy (SELECT * FROM v_xml_cdr) TO '/tmp/xml_cdr.csv' WITH CSV"
psql --host=x.x.x --username=fusionpbx -c "\copy (SELECT * FROM v_conference_sessions) TO '/tmp/conference_sessions.csv' WITH CSV"
psql --host=x.x.x --username=fusionpbx -c "\copy (SELECT * FROM v_conference_session_details) TO '/tmp/conference_session_details.csv' WITH CSV"
#Insert the data into the cdr server
# - create a temp tables
# - copy the csv data to the temp tables
# - insert data from the temp table to the real tables
# - delete the temp tables
# - remove the json data from the cdrs. too much space
psql --host=x.x.x.x --username=fusionpbx << EOF
CREATE TEMP TABLE tmp_domains AS SELECT * FROM v_domains WITH NO DATA;
CREATE TEMP TABLE tmp_fax_logs AS SELECT * FROM v_fax_logs WITH NO DATA;
CREATE TEMP TABLE tmp_xml_cdr AS SELECT * FROM v_xml_cdr WITH NO DATA;
CREATE TEMP TABLE tmp_conference_sessions AS SELECT * FROM v_conference_sessions WITH NO DATA;
CREATE TEMP TABLE tmp_conference_session_details AS SELECT * FROM v_conference_session_details WITH NO DATA;
COPY tmp_domains FROM '/tmp/domains.csv' DELIMITER ',' CSV HEADER;
COPY tmp_fax_logs FROM '/tmp/fax_logs.csv' DELIMITER ',' CSV HEADER;
COPY tmp_xml_cdr FROM '/tmp/xml_cdr.csv' DELIMITER ',' CSV HEADER;
COPY tmp_conference_sessions FROM '/tmp/conference_sessions.csv' DELIMITER ',' CSV HEADER;
COPY tmp_conference_session_details FROM '/tmp/conference_session_details.csv' DELIMITER ',' CSV HEADER;
INSERT INTO v_domains SELECT DISTINCT ON (domain_uuid) * FROM tmp_domains ON CONFLICT DO NOTHING;
INSERT INTO v_fax_logs SELECT DISTINCT ON (fax_log_uuid) * FROM tmp_fax_logs ON CONFLICT DO NOTHING;
INSERT INTO v_xml_cdr SELECT DISTINCT ON (xml_cdr_uuid) * FROM tmp_xml_cdr ON CONFLICT DO NOTHING;
INSERT INTO v_conference_sessions SELECT DISTINCT ON (conference_session_uuid) * FROM tmp_conference_sessions ON CONFLICT DO NOTHING;
INSERT INTO v_conference_session_details SELECT DISTINCT ON (conference_session_detail_uuid) * FROM tmp_conference_session_details ON CONFLICT DO NOTHING;
DROP TABLE tmp_domains;
DROP TABLE tmp_fax_logs;
DROP TABLE tmp_xml_cdr;
DROP TABLE tmp_conference_sessions;
DROP TABLE tmp_conference_session_details;
UPDATE v_xml_cdr SET json = NULL;
EOF
#remove the csv files
rm /tmp/domains.csv
rm /tmp/fax_logs.csv
rm /tmp/xml_cdr.csv
rm /tmp/conference_sessions.csv
rm /tmp/conference_session_details.csv
crontab -e
15 0 * * * bash /etc/cron.daily/db_copy.sh
Note: In this example I remove the json data from the records. You will need to comment out the “SET json = NULL” line if you want to keep the call variables.
CDR
FusionPBX menu Apps > CDR
Setup your live server to connect to the archive database.
Default Setting Subcategory |
Default Setting Name |
Default Setting Value |
Setting Enabled |
Default Setting Description |
---|---|---|---|---|
archive_database_driver |
text |
pgsql |
TRUE |
Archive Database Driver |
archive_database_host |
text |
x.x.x.x |
TRUE |
IP/Hostname of Archive Database |
archive_database_password |
text |
somethingSecret |
TRUE |
Archive Database Password |
archive_database_port |
text |
5432 |
TRUE |
Archive Database Port |
archive_database_username |
text |
fusionpbx |
TRUE |
Archive Database Username |
archive_database |
boolean |
TRUE |
FALSE |
Enable Dedicated CDR Database Access |
archive_database_name |
text |
fusionpbx |
FALSE |
Archive Database Name |