通过ODBC连接PostgreSQL和Greenplum
背景
以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