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: 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: 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 [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 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
[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?