.::: Install PostgreSQL ODBC Driver on Linux & test DSN or odbcins.ini and odbc.ini Access PostgreSQL using ODBC :::.

1. install odbc postgres
yum install unixODBC unixODBC-devel postgresql-odbc -y

[root@teguhth data]# rpm -qa | grep -i odbc
msodbcsql18-18.3.2.1-1.x86_64
postgresql-odbc-09.03.0100-2.el7.x86_64
unixODBC-devel-2.3.11-1.rh.x86_64
unixODBC-2.3.11-1.rh.x86_64
[root@teguhth data]#
 

-bash-4.2$ cat postgresql.conf | grep -i listen_a
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses ='*'                                   # comma-separated list of addresses;
-bash-4.2$
-bash-4.2$ cat pg_hba.conf | grep -i admin
host   all   admin 10.0.0.0/8 md5
-bash-4.2$


2. Configure the PostgreSQL ODBC Driver in the Odbcinst.ini File

[root@teguhth ~]# cat /etc/odbcinst.ini
[PostgreSQL]
Description=ODBC for PostgreSQL
#Driver=/usr/lib/psqlodbcw.so
Driver=/usr/lib64/psqlodbc.so
#Setup=/usr/lib/libodbcpsqlS.so
#Driver64=/usr/lib64/psqlodbcw.so
#Setup64=/usr/lib64/libodbcpsqlS.so
#FileUsage=1
UsageCount=1


[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1

[root@teguhth ~]#

3. Test ODBC Driver or check list odbc

[root@teguhth ~]# odbcinst -q -d
[PostgreSQL]
[MySQL]
[ODBC Driver 18 for SQL Server]
[root@teguhth ~]#

4. Configure Our ODBC Connections in /etc/odbc.ini

[root@teguhth ~]# cat /etc/odbc.ini
[edbdsn]
Description         = PostgreSQL ODBC Database
Driver              = PostgreSQL
Database            = teguhth
Servername          = 10.10.10.8
Port                = 5432
Protocol            = TCP/IP
ReadOnly            = No

[edbdb]
Description         = PostgreSQL connection to SallyDB
Driver              = PostgreSQL
Database            = teguhth
Servername          = 10.10.10.8
UserName            = admin
Password            = admin
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
#ConnSettings        =


[mariadb]
Description         = MariaDB ODBC Database
Driver              = MySQL
Database            = teguhth
Servername          = 10.10.10.8
UserName            = admin
Password            = admin
Port                = 3306
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
#ConnSettings        =
[root@teguhth ~]#

6. Connect to PostgreSQL with ODBC isql Command

[root@teguhth ~]# isql edbdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from barang;
+------------+--------------------------+---------------------+------------+
| kode_barang| nama_barang              | satuan_barang       | stok_barang|
+------------+--------------------------+---------------------+------------+
| ELK-01     | RICE COOKER              | BUAH                | 20         |
| ELK-02     | LEMARI ES                | UNIT                | 8          |
| ELK-03     | TELEVISI                 | UNIT                | 30         |
| ELK-04     | RADIO/TAPE               | BUAH                | 35         |
| ELK-05     | KOMPUTER                 | UNIT                | 28         |
| ELK-06     | KIPAS ANGIN              | BUAH                | 38         |
+------------+--------------------------+---------------------+------------+
SQLRowCount returns 6
6 rows fetched
SQL> SELECT datname FROM pg_database;
+-----------------------------------------------------------------+
| datname                                                         |
+-----------------------------------------------------------------+
| postgres                                                        |
| teguhth                                                         |
| template1                                                       |
| template0                                                       |
+-----------------------------------------------------------------+
SQLRowCount returns 4
4 rows fetched
SQL>