通过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