TAG 11

作者

digoal

日期

2016-05-24

标签

PostgreSQL , Greenplum , ODBC

背景

以Linux平台为例，如何通过ODBC连接PostgreSQL和Greenplum

正文

安装驱动

yum install -y unixODBC.x86_64 yum install -y postgresql-odbc.x86_64

查看驱动配置

```
cat /etc/odbcinst.ini

Example driver definitions

Driver from the postgresql-odbc package

Setup from the unixODBC package

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbcw.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbcw.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

Driver from the mysql-connector-odbc package

Setup from the unixODBC package

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
```

配置DSN

```
/etc/odbc.ini
[digoal]
Description = Test to Postgres
Driver = PostgreSQL
Database = postgres
Servername = xxxx.pg.rds.aliyuncs.com
UserName = xxxx
Password = xxxx
Port = 3433
ReadOnly = 0

[gp]
Description = Test to gp
Driver = PostgreSQL
Database = mygpdb
Servername = xxxx.gpdb.rds.aliyuncs.com
UserName = xxxx
Password = xxxx
Port = 3568
ReadOnly = 0
```

测试连通性

```
echo "select count() from pg_class"|isql gp
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count() from pg_class
+---------------------+
| count |
+---------------------+
| 388 |
+---------------------+
SQLRowCount returns 1
1 rows fetched

echo "select count() from pg_class"|isql digoal
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count() from pg_class
+---------------------+
| count |
+---------------------+
| 1330 |
+---------------------+
SQLRowCount returns 1
1 rows fetched
```

参考文档

http://blog.163.com/digoal@126/blog/static/16387704020119934923142

https://odbc.postgresql.org/docs/config.html

https://odbc.postgresql.org/docs/config-opt.html

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.