Odbc драйвер postgresql linux

Установка unixODBC и подключение PostgreSQL в Debian

unixODBC это программный интерфейс (API) доступа к базам данных. С помощью него можно унифицировать подключение к разным база данных. Он используется как дополнительный уровень абстракции в различных приложениях которым требуется база данных (например в Asterisk).

Как установить и настроить PostgreSQL описывается в статье Установка PostgreSQL в Debian.

Устанавливаем ODBC:

apt-get install unixodbc odbc-postgresql

После окончания установки, открываем файл /etc/odbcinst.ini и смотрим драйвера.
Нас интересует драйвера:
PostgreSQL ANSI – если требуется кодировка ANSI;
PostgreSQL Unicode – если требуется кодировка Unicode.

В случае отсутствия записей, создаем их самостоятельно:

Для ANSI:

[PostgreSQL ANSI] Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Для Unicode:

[PostgreSQL Unicode] Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Теперь нужно создать подключение к базе данных:
Открываем файл /etc/odbc.ini и записываем в него параметры подключения к базе данных:

[PostgreSQL-connector] Description = PostgreSQL connection
Driver = PostgreSQL Unicode
Database = user_db
Servername = localhost
UserName = user
Password = MyPassword1
Port = 5432
Protocol = 9.6
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =

user_db – имя базы данных;
MyPassword1 – пароль.

Проверить подключение можно с помощью утилиты isql:

echo «select version()» | isql -v PostgreSQL-connector

Если подключение установлено, то вернется информация о версии PostgreSQL.

Заказать создание и поддержку безопасной IT-инфраструктуры любой сложности

Быть уверенным в своей IT-инфраструктуре – это быть уверенным в завтрашнем дне.

Источник

Install ODBC connector for PostgreSQL on Ubuntu Server 12.04

I try to install the ODBC driver for PostgreSQL on Ubuntu Server 12.04. The purpose is to connect R (which is also running on the Server)to the database. I somehow got stock because I did not find a very comprehensive manual on how to do this. So the first thing I did was to install the MySQL Database ODBC Driver with

as described here. Now I think I would need to enable ODBC in PostgreSQL. So normally if you want to achieve this you have to do it when manually building PostgreSQL. Since my database is already built and everything set up I wanted to do it afterwards. PostgreSQL-Manual states that:

To install the ODBC you simply need to supply the —enable-odbc option to the configure script when you are building the entire PostgreSQL distribution. The library will then automatically be built and installed with the rest of the programs. If you forget that option or want to build the ODBC driver later you can change into the directory src/interfaces/odbc and do make and make install there.

Ok. So Source doesn’t come with the program if you install it with apt-get so I downloaded the source from here. I followed the steps described on the website to create the directory. Now, inside this directory src/interfaces/ there is no subfolder called odbc. So I also can’t make the driver. Now I googled this but I did not find any post of somebody that had a similar issue. So can anybody here please help me? I’m no database expert I only wan’t to set this up to Work with R and PostgreSQL. Thx in advance.

Источник

Install PostgreSQL ODBC Driver on Linux

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

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

# 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

-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

# 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

Источник