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 (https://github.com/vitabaks/postgresql_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

res_odbc.conf:

;; odbc setup file
[ENV]
ODBCSYSINI => /etc
ODBCINI => /etc/odbc.ini

[ast_cnf]
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.ini:

[ODBC Data Sources]
asterisk = PostgreSQL ODBC driver

[asterisk]
Description = PostgreSQL asterisk
;Driver = /usr/lib64/psqlodbc.so
Driver = /usr/pgsql-12/lib/psqlodbc.so
Database = flexnet_test
Servername = servername
UserName = asterisk
Password = password
Port = 5000
KeepAlive=1
KeepAliveIdle=5
KeepAliveCount=10
KeepAliveInterval=1

odbcinst.ini:

[PostgreSQL]
Description 	= ODBC for PostgreSQL12
Driver		= /usr/pgsql-12/lib/psqlodbcw.so
Setup		= /usr/lib64/libodbcpsqlS.so
Driver64	= /usr/pgsql-12/lib/psqlodbcw.so
Setup64		= /usr/lib64/libodbcpsqlS.so
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.