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 DSN [UID [PWD]] [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: