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?