Postgresql in Asterisk

WARNING: this is an automatic post retrieved from the Asterisk-Users Mailing List, not an authored post Mailing-list Collector February 22, 2012 Asterisk-Users 3 Comments

Tags: , , , , , ,

Hello,

I install asterisk an postgresql 9.1 in gentoo, I already did the
configuration in both asterisk and postgresql, in fact If I make a call
and asterisk log it to CDR table, my question is:

how can I make a function like the ones in func_odbc.conf for
postgresql, if I am using res_pgsql.conf instead of res_odbc.conf?

I also configure odbc and it connects with echo “select 1″ | isql -v
asterisk-connector with out problems, but when I try an odbc function
or restart asterisk it logs:

Error SQLConnect=-1 errno=101 [unixODBC]Could not connect to the server;
Could not connect to remote socket.

and the command

CLI> odbc show

ODBC DSN Settings

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

3 Responses to “Postgresql in Asterisk”

  1. Sergio Basurto said:

    Feb 22, 12 at 8:54 am

    I make a typo mistake I mean If I make a call asterisk already log it
    into CDR table.

  2. Jonathan Rose said:

    Feb 24, 12 at 3:16 pm

    You need to make sure ODBC is actually getting a connection made with your database.

    What you should see under ODBC DSN settings:

    Name: asterisk
    DSN: asterisk-connector
    Last connection attempt: WHATEVER
    Pooled: No/Yes
    Connected: Yes

    Connected: Yes is the important part.

    Remember, you need to have an account in postgres that can be logged into. I made one on my machine with the following:

    name = asterisk
    password = secret

    And in /etc/odbc.ini, I have the following connector established:
    [asterisk-connector]
    Description = PostgreSQL connection to ‘asterisk’ database
    Driver = PostgreSQL
    Database = asterisk
    Servername = localhost
    UserName = asterisk
    Password = secret
    Port = 5432
    Protocol = 8.1
    ReadOnly = No
    RowVersioning = No
    ShowSystemTables = No
    ShowOidColumn = No
    FakeOidIndex = No
    ConnSettings =

    While my res_odbc.conf looks like this:

    [asterisk]
    enabled => yes
    dsn => asterisk
    pre-connect => yes

    In addition to having a connector defined, you need to have an ODBC adapter for postgres. I think this might come with ODBC byd efault though. When I was using mysql, I had to get a separate adapter to make it work and set the path to it in Driver. I don’t think that is the case with pgsql though.

    Go ahead and post your extconfig.conf. I’m guessing that the reason you are able to post CDRs in spite of not having the Connected status show up in your ODBC show is because you are connecting with res_pgsql.conf instead of odbc.

  3. Sergio Basurto said:

    Feb 27, 12 at 3:49 pm

    Thank you Jonathan,

    I already do the steps you mention, my configuration is:

    in res_odbc.conf

    enabled => yes
    dsn => asterisk-connector
    pre-connect => yes

    in odbc.ini

    [asterisk-connector]
    Description = PostgreSQL connection to ‘asterisk’ database
    Driver = PostgreSQL
    Database = db_asterisk
    Servername = localhost
    UserName = asterisk
    Password = secret
    Port = 5432
    Protocol = 9.1
    ReadOnly = No
    RowVersioning = No
    ShowSystemTables = No
    ShowOidColumn = No
    FakeOidIndex = No
    ConnSettings =

    in odbcinst.ini

    [PostgreSQL]
    Description = ODBC for PostgreSQL
    Driver = /usr/lib/libodbcpsql.so
    Setup = /usr/lib/libodbcpsql.so
    FileUsage = 1

    if I run with root:

    #echo “select 1″ | isql -v asterisk-connector

    returns

    +—————————————+
    | Connected! |
    | |
    | sql-statement |
    | help [tablename] |
    | quit |
    | |
    +—————————————+
    SQL> select 1
    +————+
    | ?column? |
    +————+
    | 1 |
    +————+
    SQLRowCount returns 1
    1 rows fetched

    This show me that it can connect, the thing is that in the asterisk logs
    it returns:

    res_odbc.c: Connecting asterisk
    res_odbc.c: res_odbc: Error SQLConnect=-1 errno=101 [unixODBC]Could not
    connect to the server;
    Could not connect to remote socket
    res_odbc.c: Failed to connect to asterisk
    res_odbc.c: Registered ODBC class ‘asterisk’ dsn->[asterisk-connector]
    res_odbc.c: res_odbc loaded.

    I notice that if I run the isql command with other user than root, it
    returns

    [S1000][unixODBC]Could not connect to the server;
    Could not connect to remote socket.
    [ISQL]ERROR: Could not SQLConnect

    I guess is an extra configuration for ODBC that I am missing, what you
    think?

    Regards,