Install PostgreSQL ODBC Driver on Linux - Uptime Through Simplicity

We had an interesting little project the other day. One of our statisticians needed to be able to connect to his PostgreSQL database using ODBC. The client was a CentOS 6 box, which already had PostgreSQL 9.3 installed on it. If you need instructions on installing PostgreSQL on a CentOS, Fedora, or Redhat server, you can find instructions in this earlier article: CentOS Install Postgres. Installing PostgreSQL isn’t required for installing the PostgreSQL ODBC drivers, but you will want to begin by installing the PostgreSQL repository.

Download the Postgresql Repository

The repository for postgresql makes installing their database much easier. Download the Postgres 9.3 repository and install it using rpm using the following steps:

# wget http://yum.postgresql.org/9.3/redhat/rhel-6.4-x86_64/pgdg-centos93-9.3-1.noarch.rpm
# rpm -ivh ./pgdg-centos93-9.3-1.noarch.rpm

That will install the PostgreSQL repository which will make it easier to install the ODBC drivers.

Install PostgreSQL drivers with Yum

You should now be able to install your unixODBC rpm and PostgreSQL drivers with yum.

yum install postgresql93-odbc.x86_64 postgresql93-odbc-debuginfo.x86_64 unixODBC

If your repository is for a slightly different version of PostgreSQL, then you can use yum search to find the exact rpm to install:

# yum search postgre | grep odbc
postgresql-odbc.x86_64 : PostgreSQL ODBC driver
postgresql93-odbc.x86_64 : PostgreSQL ODBC driver

Configure the PostgreSQL ODBC Driver in the Odbcinst.ini File

Our next task is to edit the /etc/odbcinst.ini file to configure the PostgreSQL driver. This tells unixODBC where it can find the files specific to connecting to PostgreSQL database servers. The following is the portion of my /etc/odbcinst.ini file. Make a similar section in your file:

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

Test Your ODBC Driver

Now that your driver is installed, we can test that the driver is installed using this command:

# odbcinst -q -d
[PostgreSQL]
[MySQL]

The above example shows us that we have ODBC drivers installed on this machine for connecting to both MySQL and PostgreSQL.

Configure Our ODBC Connections in /etc/odbc.ini

Next, we configure the connection information specfic to our PostgreSQL database. If you configure the /etc/odbc.ini file, it will work for all users on this machine. If we configure the $HOME/.odbc.ini file, it will be configured for our specific user only. This is a security feature that allows you to allow connections for only those users that merit it.

[sallydb]
Description         = PostgreSQL connection to SallyDB
Driver              = PostgreSQL
Database            = sallydb
Servername          = 10.2.3.4
UserName            = sally
Password            = sallypassword
Port                = 5432
Protocol            = 9.3
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ConnSettings        =

Connect to PostgreSQL with ODBC isql Command

Now, that we have our configuration complete, we can check to see if we can connect. unixODBC will have installed the isql command that allows us to login to our database using the ODBC configuration we just completed.

# isql sallydb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Success! We were able to connect successfully. I can now query the database directly by typing my SQL at the SQL> prompt.

unixODBC – isql Command Usage

We can use isql with the following usage and options:

isql Usage:

isql DSN [UID [PWD]] [options]

isql Options:

 -b         batch.(no prompting etc)        
 -dx        delimit columns with x          
 -x0xXX     delimit columns with XX, where x is in hex, ie 0x09 is tab     
 -w         wrap results in an HTML table   
 -c         column names on first row.  (only used when -d)    
 -mn        limit column display width to n 
 -v         verbose.                        
 -lx        set locale to x                 
 -q         wrap char fields in dquotes     
 -3         Use ODBC 3 calls                
 -n         Use new line processing         
 --version  version

Other Linux ODBC Commands

Find out which files are used by unixODBC:

# odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.

Other Resources on unixODBC or PostgreSQL ODBC

There are many resources on the internet that can give you further information on PostgreSQL ODBC or unixODBC. Below are several links:

http://www.unixodbc.org/odbcinst.html

http://asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/installing_configuring_odbc.html

http://www.postgresql.org/message-id/3C68E705.5060806@itek.norut.no

http://www.flatmtn.com/article/setting-postgresql-odbc

Bio
Latest Posts

Jeff Staten

Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on Google+ or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Configure Your HP Procurve Switch with SNTP - May 5, 2015
Configuring HP Procurve 2920 Switches - May 1, 2015
Troubleshooting Sendmail - November 28, 2014