How to keep the database connection alive?

Hi all,

Asterisk (16.8-cert5) is configured to communicate with PostgreSQL with ODBC. Asterisk is installed on a CentOS 7 machine, and the database is a cluster ( behind a virtual ip.

I am trying to find out how to keep the connection with my PostgreSQL database alive at all times. What happens is that, after Asterisk not having performed any db queries in a while (basically idle, it hasn’t sent/received any keepalive messages either), the first query fails with the following error(s):

WARNING[24123]: res_odbc.c:538 ast_odbc_print_errors: SQL Execute returned an error: HY000: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Error while preparing parameters
WARNING[24123]: res_odbc.c:433 ast_odbc_prepare_and_execute: SQL Execute error -1!

Installed packages and versions:
unixODBC.x86_64 2.3.1-14.el7 @base
unixODBC-devel.x86_64 2.3.1-14.el7 @base
postgresql12.x86_64 12.5-1PGDG.rhel7 @pgdg12
postgresql12-devel.x86_64 12.5-1PGDG.rhel7 @pgdg12
postgresql12-libs.x86_64 12.5-1PGDG.rhel7 @pgdg12
postgresql12-odbc.x86_64 13.00.0000-1PGDG.rhel7 @pgdg12


;; odbc setup file
ODBCINI => /etc/odbc.ini

enabled => yes
dsn => asterisk
schema => asterisk
username => asterisk
password => password
pre-connect => yes
max_connections => 20
pooling => yes
limit => 20
idlecheck => 10
connect_timeout => 10
logging => yes
sanitysql => select id from asterisk.ps_endpoints limit 1
negative_connection_cache => 1


[ODBC Data Sources]
asterisk = PostgreSQL ODBC driver

Description = PostgreSQL asterisk
;Driver = /usr/lib64/
Driver = /usr/pgsql-12/lib/
Database = flexnet_test
Servername = servername
UserName = asterisk
Password = password
Port = 5000


Description 	= ODBC for PostgreSQL12
Driver		= /usr/pgsql-12/lib/
Setup		= /usr/lib64/
Driver64	= /usr/pgsql-12/lib/
Setup64		= /usr/lib64/
Pooling		= Yes
CPTimeout	= 120

What kind of settings are necessary to keep the connection alive between Asterisk and PostgreSQL over ODBC?

Don’t pass the connection through a router. If you want high availability, on Asterisk, using a database, you need to have a local replica of the database.

Thanks for your reply David,

Indeed, this was never an issue in the past when I used a less involved database backend.

My dream is that someone knows a setting for any of the above-mentioned configuration files à la ‘keep_database_connection_alive_at_all_times=yes thx’.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.