2 Recommended UnixODBC settings for PostgreSQL

Installation

  • Red Hat Enterprise Linux/CentOS:
  1. # yum install postgresql-odbc
  • Debian/Ubuntu:

Please refer to PostgreSQL documentation to download necessary database driver for the corresponding platform.

For some additional information please refer to: installing unixODBC.

Configuration

ODBC configuration is done by editing the odbcinst.ini and odbc.ini files. These configuration files can be found in /etc folder. The file odbcinst.ini may be missing and in this case it is necessary to create it manually.

Please consider the following examples:

odbcinst.ini

  1. [postgresql]
  2. Description = General ODBC for PostgreSQL
  3. Driver      = /usr/lib64/libodbcpsql.so
  4. Setup       = /usr/lib64/libodbcpsqlS.so
  5. FileUsage   = 1
  6. # Since 1.6 if the driver manager was built with thread support you may add another entry to each driver entry.
  7. # This entry alters the default thread serialization level.
  8. Threading   = 2

odbc.ini

  1. [TEST_PSQL]
  2. Description = PostgreSQL database 1
  3. Driver  = postgresql
  4. #CommLog = /tmp/sql.log
  5. Username = zbx_test
  6. Password = zabbix
  7. # Name of Server. IP or DNS
  8. Servername = 127.0.0.1
  9. # Database name
  10. Database = zabbix
  11. # Postmaster listening port
  12. Port = 5432
  13. # Database is read only
  14. # Whether the datasource will allow updates.
  15. ReadOnly = No
  16. # PostgreSQL backend protocol
  17. # Note that when using SSL connections this setting is ignored.
  18. # 7.4+: Use the 7.4(V3) protocol. This is only compatible with 7.4 and higher backends.
  19. Protocol = 7.4+
  20. # Includes the OID in SQLColumns
  21. ShowOidColumn = No
  22. # Fakes a unique index on OID
  23. FakeOidIndex  = No
  24. # Row Versioning
  25. # Allows applications to detect whether data has been modified by other users
  26. # while you are attempting to update a row.
  27. # It also speeds the update process since every single column does not need to be specified in the where clause to update a row.
  28. RowVersioning = No
  29. # Show SystemTables
  30. # The driver will treat system tables as regular tables in SQLTables. This is good for Access so you can see system tables.
  31. ShowSystemTables = No
  32. # If true, the driver automatically uses declare cursor/fetch to handle SELECT statements and keeps 100 rows in a cache.
  33. Fetch = Yes
  34. # Bools as Char
  35. # Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.
  36. BoolsAsChar = Yes
  37. # SSL mode
  38. SSLmode = Yes
  39. # Send tobackend on connection
  40. ConnSettings =