Asterisk 11.6 and PostgreSQL 13: column d.adsrc does not exist
cdr_pgsql.c: Failed to query database columns: ERROR: column d.adsrc does not exist" and no CDRs will be logged.
Do you work with
asterisk 11.6 and
Have you decided to upgrade PostgreSQL to version
13 and now Asterisk gives you bad errors at startup and most important no longer saves any lines in the CDRs?
ERROR res_config_pgsql.c: PostgreSQL RealTime: Query Failed: SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc, a.atttypmod FROM (((pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND c.relname = 'sipfriends' AND n.nspname = current_schema()) INNER JOIN pg_catalog.pg_attribute a ON (NOT a.attisdropped) AND a.attnum > 0 AND a.attrelid = c.oid) INNER JOIN pg_catalog.pg_type t ON t.oid = a.atttypid) LEFT OUTER JOIN pg_attrdef d ON a.atthasdef AND d.adrelid = a.attrelid AND d.adnum = a.attnum ORDER BY n.nspname, c.relname, attnum
ERROR res_config_pgsql.c: PostgreSQL RealTime: Query Failed because: ERRORE: the d.adsrc column does not exist
ERROR res_config_pgsql.c: Failed to query database columns for table sipfriends
ERROR cdr_pgsql.c: Failed to query database columns: ERRORE: the d.adsrc column does not exist
I found myself in this situation and I want to tell you how I got out of it.
Googling I found the problem: some asterisk files (
res/res_config_pgsql.c) query various system tables (such as
pg_attribute) including in the select also the column
pg_attrdef table) but PostgreSQL 12 finally removed column adsrc from table pg_catalog.pg_attrdef (column default values), which has been deprecated since version 8.0.
The problem is well described:
- in the post on this mailing list: cdr_pgsql: accesses obsolete (and finally removed) column
- in the reference asterisk Jira task
As described in the task on gerrit: fortunately, the same information which once was stored in adsrc is accessable via column adbin and function pg_catalog.pg_get_expr, which is the recommended way since PostgreSQL 8.0. So here is the patch: https://gerrit.asterisk.org/c/asterisk/+/13027/
So for me the possible ways were mainly 3:
- upgrade Asterisk to the latest version, hoping (or rather, reading all the changelogs) that the problem would not occur there;
- downgrade PostgreSQL to a version earlier than 11;
- recompile Asterisk 11.6 with the patch indicated in the asterisk task.
Of course, the best thing is to always have up-to-date systems, maybe not at the latest version but at the latest stable version; so thinking of keeping Asterisk 11.6 or downgrading PostgreSQL are not the best choices.
At the same time, it is true that jumping from Asterisk 11.6 to Asterisk 18 is not easy and before putting the system into production with Asterisk 18 I would have had to do many tests; instead I had little time and I needed to restore the full functionality of the voice system.
I decided to opt for the third choice recompile asterisk 11.6 with the patch indicated in the asterisk task and here I will describe the steps I have successfully followed.
The steps that I followed are easy and with more or less 0 downtime for the customers
# I git clone the asterisk source code in tmp folder
# and checkout the 11.6 version
git clone https://github.com/asterisk/asterisk.git
git checkout certified/11.6
# I install the needed packages
# You can also use the install_prereq (take a look at Linkography section)
apt-get install ncurses-dev libxml2-dev sqlite3 libsqlite3-dev libpq-dev
# I configure the installation
make menuselect # remember to select all the module and functions you need
# I ran the patch by editing these files
vim cdr/cdr_pgsql.c # https://gerrit.asterisk.org/c/asterisk/+/13027/2/cdr/cdr_pgsql.c
vim cel/cel_pgsql.c # https://gerrit.asterisk.org/c/asterisk/+/13027/2/cel/cel_pgsql.c
vim res/res_config_pgsql.c # https://gerrit.asterisk.org/c/asterisk/+/13027/2/res/res_config_pgsql.c
# I lunch the make
# I remove the actual asterisk installation
apt-get remove asterisk asterisk asterisk-config asterisk-core-sounds-en asterisk-core-sounds-en-gsm asterisk-moh-opsound-gsm asterisk-modules
# and in the end I install the compiled and patched version
If your system consists of several asterisk nodes, one clone of the other (i.e. with the same OS), you do not need to compile asterisk on each node to update any additional asterisk nodes, but you can follow these steps
go to the asterisk node where you recompiled and create an archive of the asterisk folder
cd /tmp tar -cvzf asterisk11-6-patched.tar.gz asterisk
tar.gzon the asterisk node to be patched
tar.gz, remove the current version of asterisk and install the patched one
cd /tmp tar -xvzf asterisk11-6-patched.tar.gz cd asterisk apt-get install build-essential apt-get remove asterisk asterisk-config asterisk-core-sounds-en asterisk-core-sounds-en-gsm asterisk-moh-opsound-gsm asterisk-modules make install
Start asterisk, make a call and check your CDR. You will see that the data is there now!
- Compile asterisk
- Install the needed software for asterisk compiling
- Mailinglist post
- Asterisk Jira task
- Asterisk patch commit
- PostgreSQL remove adsrc commit