Postgresql odbc drivers linux

Installing the ODBC drivers for PostgreSQL

This article is part of a series that includes SQLite, MariaDB, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
The postgresql drivers can be installed from the platform’s default package repositories using the following steps as root. As the postgresql service was missing on this test environment, let’s install it too:

A systemd service has been created and started, check it:

# systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Mon 2021-08-23 19:04:20 CEST; 1min 15s ago Main PID: 10528 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 4659) Memory: 0B CPU: 0 CGroup: /system.slice/postgresql.service

# ps -ef | grep postgres postgres 682 1 0 Oct16 ? 00:00:02 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf postgres 807 682 0 Oct16 ? 00:00:00 postgres: 13/main: checkpointer postgres 808 682 0 Oct16 ? 00:00:04 postgres: 13/main: background writer postgres 809 682 0 Oct16 ? 00:00:04 postgres: 13/main: walwriter postgres 810 682 0 Oct16 ? 00:00:01 postgres: 13/main: autovacuum launcher postgres 811 682 0 Oct16 ? 00:00:01 postgres: 13/main: stats collector postgres 812 682 0 Oct16 ? 00:00:00 postgres: 13/main: logical replication launcher

The processes run under the postgres account.
Install the ODBC drivers:

# apt install odbc-postgresql

# odbcinst -q -d . [PostgreSQL ANSI] [PostgreSQL Unicode]

Two drivers have been installed, one for the ANSI character encoding and one for Unicode; check the Unicode one:

# odbcinst -q -d -n 'PostgreSQL Unicode' [PostgreSQL Unicode] Description=PostgreSQL ODBC driver (Unicode version) Driver=psqlodbcw.so Setup=libodbcpsqlS.so Debug=0 CommLog=1 UsageCount=1

Verify that the default native administration tool, psql, is there:

# psql -V # psql (PostgreSQL) 13.3 (Debian 13.3-1)

Switch to the postgres account and create the database:

# su – postgres $ createdb sampledb

Launch psql and create the tables for postgresql using the scripts here:
As those are small files, copying and pasting their content into psql will do just fine.
Also, populate the tables for postgresql using the data here and test:

$ psql sampledb sampledb=# SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN'); Output: country_name | country_id | country_id | street_address | city --------------------------+------------+------------+-------------------------------+----------- United States of America | US | US | 2014 Jabberwocky Rd | Southlake United States of America | US | US | 2011 Interiors Blvd | South San Francisco United States of America | US | US | 2004 Charade Rd | Seattle United Kingdom | UK | UK | 8204 Arthur St | London United Kingdom | UK | UK | Magdalen Centre, The Oxford | Oxford | | | Science Park | China | CN | | | (6 rows)

To test ODBC, first edit ~/.odbc.ini and add the postgresql database details:

$ vi ~/.odbc.ini [mypostgresqldb] Description=My Postgresql sample database Driver=PostgreSQL Unicode Database=sampledb

Verify that the edition was successful:

$ odbcinst -q -s -n mypostgresqldb [mypostgresqldb] Description=My Postgresql sample database Driver=PostgreSQL Unicode Database=sampledb

Next, launch isql against that database:

$ export LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu/odbc:$LD_LIBRARY_PATH $ isql -v mypostgresqldb +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> help +------------------+-------------+-------------+------------+---------+ | TABLE_QUALIFIER | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | REMARKS | +------------------+-------------+-------------+------------+---------+ | sampledb | public | countries | TABLE | | | sampledb | public | departments | TABLE | | | sampledb | public | dependents | TABLE | | | sampledb | public | employees | TABLE | | | sampledb | public | jobs | TABLE | | | sampledb | public | locations | TABLE | | | sampledb | public | regions | TABLE | | +------------------+-------------+-------------+------------+---------+ SQLRowCount returns 7 7 rows fetched SQL> quit

The ODBC connectivity to postgresql is confirmed for the postgres account; this is fine for that user but we want the database to be usable by the debian test account too. To this effect, use the following steps from with psql:

sampledb=# CREATE ROLE debian superuser; CREATE ROLE sampledb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO debian; GRANT sampledb=# ALTER ROLE debian with login; ALTER ROLE sampledb=# q

Back as root, become debian and test the accessibility of the sampledb:

# su - debian $ psql sampledb sampledb=# select count(*) from employees; count ------- 40 (1 row)

$ isql -v mypostgresqldb +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN') +---------------------------+-------------+-----------+-----------------------+----------------------+ | country_name | country_id | country_id| street_address | city | +---------------------------+-------------+-----------+-----------------------+----------------------+ | United States of America | US | US | 2014 Jabberwocky Rd | Southlake | | United States of America | US | US | 2011 Interiors Blvd | South San Francisco | | United States of America | US | US | 2004 Charade Rd | Seattle | | United Kingdom | UK | UK | 8204 Arthur St | London | | China | CN | | | | +---------------------------+-------------+-----------+-----------------------+----------------------+ SQLRowCount returns 6 6 rows fetched

Note that the order of the result set may differ in other data sources; the SQL standard does not define the result set’s order and the ORDER BY clause should be used to enforce one if needed.
Finally, let’s verify the ODBC connectivity through pyodbc:

$ python3 import pyodbc Python 3.9.2 (default, Feb 28 2021, 17:03:44) [GCC 10.2.1 20210110] on linux Type "help", "copyright", "credits" or "license" for more information. import pyodbc # connect directly using the DRIVER definition, no DSN; cnxn = pyodbc.connect('DRIVER=;Direct=True;Database=sampledb;String Types= Unicode') # using the DSN is OK too: # cnxn = pyodbc.connect('DSN=mypostgresqldb') cursor = cnxn.cursor() cursor.execute("""SELECT . c.country_name, . c.country_id, . l.country_id, . l.street_address, . l.city . FROM . countries c . LEFT JOIN locations l ON l.country_id = c.country_id . WHERE . c.country_id IN ('US', 'UK', 'CN')""") row = cursor.fetchone() while row: . print (row) . row = cursor.fetchone() . Output: ('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake') ('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco') ('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle') ('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London') ('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford') ('China', 'CN', None, None, None)

Note that we used the PostgreSQL Unicode driver, not the ANSI one because the latter gives the error below:

pyodbc.Error: ('07006', '[07006] Received an unsupported type from Postgres. (14) (SQLGetData)')

postgreslq databases can now be used locally by any ODBC client application, e.g. any python program with the pyodbc module, or a desktop application such as LibreOffice. In the case of python, many native modules for postgresql are available but they require ad hoc function calls whereas ODBC lets one use the same statements with any database target, which simplifies the maintenance of ODBC applications.
Instructions for the other data sources can be accessed through the following links:
SQLite
HSQLDB
MariaDB
Firebird
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Источник

psqlODBC — PostgreSQL ODBC driver

psqlODBC is the official PostgreSQL ODBC Driver. It is released under the Library General Public Licence, or LGPL.

Downloads are available in source and binary formats at the PostgreSQL downloads site

psqlODBC Documentation

The following documents contain various bits of useful information. Please send any additional documentation, or report errors/omissions to pgsql-odbc@postgresql.org

psqlODBC HOWTOs

The following HOWTOs have been contributed by various people. If you wish to add to the collection, please send your contribution to pgsql-odbc@postgresql.org

psqlODBC Development

psqlODBC is developed and supported through the pgsql-odbc@postgresql.org mailing list.

You can browse the source code at the psqlODBC git repository at git.postgresql.org. The source for this website is at the psqlodbc-www git repository

A backup of the old CVS repository can be found here.

psqlODBC History

The source code for this driver was originally adopted from version 0.20 of PostODBC. The authors at that time were Christian Czezatke and Dan McGuirk. Later it was maintained by Julie Ann Case.

As part of a commercial research project, the developers at Insight Distributions System overhauled the driver. Their goal was to make the driver commercially viable. In keeping with the spirit with which the original source was acquired, Insight published their work at their website. Some time later and after some discussion with members of the PostgreSQL organization, psqlODBC was adapted as the part of the PostgreSQL source distribution. The driver continued to be maintained by Byron Nikolaidis, the developer at Insight who overhauled the driver for some time.

The driver is currently maintained by a number of contributors to the PostgreSQL project.

Acknowledgements

OpenSSL

This product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit (http://www.openssl.org/).

MIT Kerberos

This product includes Kerberos software developed by the Massachusetts Institute of Technology (http://web.mit.edu/kerberos/):

Copyright 1992-2004 by the Massachusetts Institute of Technology. All rights reserved.

THIS SOFTWARE IS PROVIDED «AS IS», AND M.I.T. MAKES NO REPRESENTATIONS OR WARRANTIES, EXPRESS OR IMPLIED. By way of example, but not limitation, M.I.T. MAKES NO REPRESENTATIONS OR WARRANTIES OF MERCHANTABILITY OR FITNESS FOR ANY PARTICULAR PURPOSE OR THAT THE USE OF THE LICENSED SOFTWARE OR DOCUMENTATION WILL NOT INFRINGE ANY THIRD PARTY PATENTS, COPYRIGHTS, TRADEMARKS OR OTHER RIGHTS.

Источник

Установка 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-инфраструктуре – это быть уверенным в завтрашнем дне.

Источник