Postgres ODBC connection issue from Ubuntu VM

Question

I am attempting to use a Zabbix server running on an Ubuntu virtual machine to monitor the Postgres database in our application running on the same host machine (not a VM). To be clear, I am trying to connect from a Linux Ubuntu virtual machine on my computer to Postgres also running not in a VM on the same computer. Zabbix makes use of ODBC, so a preliminary step in the process is to get the ODBC connection to Postgres working correctly. However, I am having a problem.

Steps I have taken:

installed unixODBC via sudo apt-get install unixodbc unixodbc-dev
installed unixODBC driver for Postgres via sudo apt-get install odbc-postgresql
configured odbc.ini to the following:

[test]
Description = test database
Driver      = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
Server      = 192.168.240.1
User        = postgres
Password    =
Port        = 5432
Database    = mydb

Yet when I test the connection via:

isql test -v

I get the following error:

[08001][unixODBC]could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

More notes:

I can successfully connect to Postgres from the admin running on the local (non VM) machine
port 5432 has been completely opened from Windows Firewall on the local machine
telnet to 192.168.240.1 (the network IP of the local machine) on port 5432 succeeds

This all implies that the problem has to do with the ODBC configuration in the Ubuntu VM. I spent several hours searching and trying various things but to no avail. If I can get isql to work correctly, I should be in business, as Zabbix basically sits right on top of ODBC for its database monitoring functions.

Thanks in advance for your help.

Michael Koenig · Accepted Answer · 2016-08-21 09:22:53Z

2

I think your configuration options are a little off. Try this:

[test]
Driver      = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Setup       = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
Database    = mydb
Servername  = 192.168.240.1
UserName    = postgres
Password    = 
Port        = 5432
Protocol = 7.4

Using Servername instead of Server might be sufficient.

answered Aug 21, 2016 at 9:22

Michael Koenig

3642 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sauron Over a year ago

And UserName instead of User.

Joseph Van Riper · Accepted Answer · 2018-09-19 13:41:01Z

I'd recommend the following steps to getting ODBC and PostgreSQL to play together (ignoring the apt install steps, since you already did these):

sudo odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template
sudo odbcinst -i -s -l -n test -f /usr/share/doc/odbc-postgresql/examples/odbc.ini.template
sudo nano /etc/odbc.ini

Here's what these do:

Sets up your odbcinst.ini file with the files in the right places.
Sets up your odbc.ini file (for the system).
Edits the system odbc.ini file you created in step 2, where you can replace options to match your needs.

If you do not want the odbc.ini file to be system-wide, you can also limit it to just the user if you call step #2 without the -l parameter. In that case, it'll create or modify a ~/.odbc.ini file, which you can edit for your needs.

The unixODBC folks seem to recommend using odbcinst for setting this stuff up, as it knows where to put the files. Unfortunately, to use it to great effect, you'd need to know where to find the drivers' template files for your driver. The paths I've provided here match the ones for the Ubuntu package.

Collectives™ on Stack Overflow

Postgres ODBC connection issue from Ubuntu VM

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related