Asterisk cdr pgsql
Created by: jametrel,Last modification on Fri 06 of Jun, 2008 [23:37 UTC] by JustRumours
Asterisk can store CDR records in a Postgresql database, as an alternative to CSV text files and other database formats.
I had to modify the above file manually
If you have the Postgresql libraries installed (the postgresql-devel rpm), they will be detected when you build asterisk and cdr_pgsql.o will be compiled and put in your module path with 'make install'.
You may have to modify the MODS line in the cdr/Makefile to include cdr_pgsql.so.
MODS=cdr_pgsql.so cdr_csv.so cdr_manager.so
When using 1.4.X :
you have to compile asterisk with the cdr_pgsql.o module, for this follow the steps:
Configure asterisk with postgresql support:
Then issue the command:
in the menu select 2.Call Detail Recording -> then check cdr_pgsql
build asterisk
Install it
Then add, in the file modules.conf, the line:
Sample cdr_pgsql.conf file
Create the database
Create the CDR table
Create the user
Grant insert access to the userl:
Now reload/restart asterisk and loggin should work, if it doesn't work check for error messages in the asterisk log.
Bug #4909 - Adds support for a "spool file"
Add the following line to your cdr_pgsql.conf file:
spool=pgsql.spool
If the connection can not be made to the sql server, it will spool insert commands to pgsql.spool in /var/log/asterisk until it can reconnect
Installation of PostgresQL on Fedora Core 4
yum -y install postgresql postgresql-server php-pgsqlI had to modify the above file manually
Compiling cdr_pgsql.so
When using 1.2.X:If you have the Postgresql libraries installed (the postgresql-devel rpm), they will be detected when you build asterisk and cdr_pgsql.o will be compiled and put in your module path with 'make install'.
You may have to modify the MODS line in the cdr/Makefile to include cdr_pgsql.so.
MODS=cdr_pgsql.so cdr_csv.so cdr_manager.so
When using 1.4.X :
you have to compile asterisk with the cdr_pgsql.o module, for this follow the steps:
Configure asterisk with postgresql support:
./configure --with-postgres=<dir where postgresql is installed>
Then issue the command:
make menuconfig
in the menu select 2.Call Detail Recording -> then check cdr_pgsql
build asterisk
make
Install it
sudo make install
Then add, in the file modules.conf, the line:
load => cdr_pgsql.so
Create the database
Create a database named 'asterisk' using the Postgresql schema described in Asterisk cdr odbc.Edit the configuration file
A sample cdr_pgsql.conf file is included with Asterisk - you should find it in /etc/asterisk. Uncomment out the necessary lines and ensure that they point to your Postgresql database.Sample setup
Make sure postmaster is running with the -i flag to enable tcp connections. Also the user that asterisk will use to connect needs to have insert permission on the cdr table.Sample cdr_pgsql.conf file
[global]
hostname=localhost
port=5432
dbname=asterisk
password=cdrstuff
user=asterisk
hostname=localhost
port=5432
dbname=asterisk
password=cdrstuff
user=asterisk
Create the database
createdb asterisk
Create the CDR table
You can find the script for this in the Asterisk source folder, in contrib/scripts. It's called "postgres_cdr.sql". Just paste it in the pgsql command line.
Create the user
createuser -A -D -P asterisk
Enter password for new user:
Enter it again:
CREATE USER
Enter password for new user:
Enter it again:
CREATE USER
Grant insert access to the userl:
grant INSERT on cdr to asterisk;
Now reload/restart asterisk and loggin should work, if it doesn't work check for error messages in the asterisk log.
Bug #4909 - Adds support for a "spool file"
Add the following line to your cdr_pgsql.conf file:
spool=pgsql.spool
If the connection can not be made to the sql server, it will spool insert commands to pgsql.spool in /var/log/asterisk until it can reconnect
See also
- Asterisk PGSQL: Issue Postgres SQL statements from within the dialplan
Comments
333cdr table: additional permissions in PostgreSQL 8.1.X
Apr 14 16:49:59 ERROR[6661]: cdr_pgsql.c:154 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
Apr 14 16:49:59 ERROR[6661]: cdr_pgsql.c:155 pgsql_log: cdr_pgsql: Reason: ERROR: permission denied for sequence cdr_acctid_seq.
In such cases, you need to add additonal permissions to the user asterisk for the sequence "cdr_acctid_seq" that is created upon the creation of the "AcctId BIGSERIAL PRIMARY KEY" counter in the table "cdr". You only need to grant UPDATE to asterisk for this sequence by typing
GRANT UPDATE ON cdr_acctid_seq TO asterisk;
at e.g. the psql prompt (as user postgres) and asterisk resumes logging.
333Re: Table creation script
333Table creation script
You can find it in the Asterisk source folder, in contrib/scripts. It's called "postgres_cdr.sql".
333Filename Correction