Connect to PostgreSQL using ODBC - AnalyticDB

Connect to an AnalyticDB for PostgreSQL instance using Open Database Connectivity (ODBC).

Prerequisites

Before you begin, make sure that you have:

The internal or public endpoint of your AnalyticDB for PostgreSQL instance
- Internal endpoint: use this if your client runs on an Elastic Compute Service (ECS) instance in the same region and network type as the AnalyticDB for PostgreSQL instance. Find the endpoint on the Basic Information page of the AnalyticDB for PostgreSQL console, under Database Connection Information
- Public endpoint: use this if your client is on an ECS instance in a different region or network type, or outside Alibaba Cloud. See Manage public endpoints to apply for one
The client IP address added to an IP address whitelist of the instance. See Configure an IP address whitelist
The yum package manager available on your Linux host

Connect via ODBC

Step 1: Install the ODBC driver

The PostgreSQL ODBC driver is open source, licensed under the GNU Lesser General Public License (LGPL). Run the following commands to install it:

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

Step 2: Configure the data source

Open /etc/odbc.ini for editing:

vim /etc/odbc.ini

Add a connection section with your instance details. The following is an example:

[mygpdb]
Description = Test to gp
Driver      = PostgreSQL
Database    = <your-database>
Servername  = <your-instance-endpoint>
UserName    = <your-username>
Password    = <your-password>
Port        = 5432
ReadOnly    = 0

Replace the placeholders with your actual values:

Parameter	Description	Example
`[mygpdb]`	Data source name (DSN). Use this name when connecting.	`[mygpdb]`
`Driver`	Driver name.	`PostgreSQL`
`Database`	Name of the database to connect to.	`mydb`
`Servername`	Endpoint of your AnalyticDB for PostgreSQL instance.	`gp-bp1k1b8683t****.gpdb.rds.aliyuncs.com`
`UserName`	Database username.	`myuser`
`Password`	Database user password.	`MyPassword12!`
`Port`	Port number.	`5432`
`ReadOnly`	Whether the connection is read-only.	`0`

Step 3: Test the connection

Run the following command to verify the connection:

echo "select count(*) from pg_class" | isql mygpdb

A successful connection produces output similar to:

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

Step 4: Connect your application

After the ODBC driver connects successfully, configure your application to use the driver. See the following resources for language-specific guidance:

Additional information

The Greenplum official website provides a tool package that includes JDBC, ODBC, and libpq. See the Greenplum documentation for installation and usage details.

AnalyticDB:ODBC