Does PostgreSQL support JDBC and ODBC connectivity using unix domain socket instead of TCP/IP connec

 

Does PostgreSQL support JDBC and ODBC connectivity using unix domain socket instead of TCP/IP connection

 SOLUTION 已验证 - 已更新 2017年十二月26日03:22 - 

English 

环境

• Red Hat Enterprise Linux 4
• Red Hat Enterprise Linux 5
• Red Hat Enterprise Linux 6
• Red Hat Enterprise Linux 7

问题

• Need to connect PostgreSQL database using JDBC and ODBC driver with UNIX domain socket instead of TCP/IP connection.

决议

• PostgreSQL supports Unix domain socket with ODBC driver but not with JDBC driver.
• Packages need - unixODBC and postgresql-odbc

PostgreSQL with ODBC Unix domain socket configuration

Raw

# cat /etc/odbcinst.ini

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver      = /usr/lib64/psqlodbc.so
Setup       = /usr/lib64/libodbcpsqlS.so
FileUsage   = 1

Note: In order to get Unix domain Socket working never use server name or port number.

Raw

# cat /etc/odbc.ini 
[postgres]
Description         = PostgreSQL connection
Driver              = PostgreSQL
Database            = postgres
UserName            = postgres
Password            = postgres
Protocol            = 9.3
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ConnSettings        =

Raw

# cat /var/lib/pgsql/data/pg_hba.conf 
# "local" is for Unix domain socket connections only
local   all             all                                     trust

根源

• Java does not support Unix Sockets. The PostgreSQL server must be configured to allow TCP/IP connections. PostgreSQL official documentation

What is Unix Domain Socket

诊断步骤

Testing

Raw

# isql postgres
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

Raw

SQL> select * from company;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------------------------------------+--------------+
| id         | name                                                                                                                                                                                                                                                                                                        | age        | address                                           | salary       |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------------------------------------+--------------+
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------------------------------------------------+--------------+
SQLRowCount returns 0

SQL> quit

TCPDUMP CAPTURE

No packets captured on port 5432

Raw

# tcpdump -i any port 5432 -nnnvvvSex
tcpdump: listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes