After migrating from SAS Viya 3.5 on Windows Server 2016 to SAS Viya 3.5 on Linux, PostgreSQL connections using SAS/ACCESS Interface to ODBC can fail even though the same LIBNAME statement and DSN configuration work successfully in the Windows environment.
Symptoms might include the following error:
ERROR: CLI error trying to establish connection:
[unixODBC] connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed
ERROR: Error in the LIBNAME statement.
In these situations, the PostgreSQL database is typically configured as a remote database server, but the PostgreSQL ODBC driver attempts to connect to the default local PostgreSQL socket on port 5432 instead of the intended remote PostgreSQL server.
This issue occurs in SAS Viya 3.5 Linux environments using PostgreSQL ODBC drivers.
This issue is typically caused by an incorrect or incomplete ODBC DSN configuration in the Linux environment.
When the PostgreSQL ODBC driver cannot read the expected DSN values, it might fall back to default PostgreSQL connection behavior and attempt to connect to the local PostgreSQL socket rather than the intended remote PostgreSQL server:
/run/postgresql/.s.PGSQL.5432
The path, /run/postgresql/.s.PGSQL.5432, is the default PostgreSQL Unix socket used for local database connections on Linux systems. If this path occurs in the error message when a remote PostgreSQL server is expected, it is a strong indication that the ODBC driver is not using the intended DSN configuration.
Common causes include the following:
In these situations, the PostgreSQL ODBC driver might attempt to connect to the default local PostgreSQL listener on port 5432 instead of the intended remote PostgreSQL database server. The issue is typically resolved by correcting the DSN definition in the Linux odbc.ini file and verifying that the driver is reading the expected configuration settings.
Review the DSN configuration defined in the Linux odbc.ini file and verify that it matches the intended PostgreSQL connection settings.
Validate the following entries:
Confirm that the DSN references the intended remote PostgreSQL server and does not rely on default PostgreSQL connection behavior.
After updating the configuration, retest the connection from SAS.
odbcinst -j
odbcinst -q -d
odbcinst -q -s
isql -v CHHS
Testing outside of SAS helps determine whether the problem exists within the following:
If the DSN fails when tested with isql, you should investigate the issue at the ODBC configuration layer before further completing further SAS troubleshooting.
The following error is a strong indicator that the PostgreSQL ODBC driver is not using the intended DSN configuration:
ERROR: CLI error trying to establish connection:
[unixODBC] connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed
When a remote PostgreSQL server is expected but the error references the local PostgreSQL socket, review the ODBC DSN configuration and odbc.ini settings first.