Use client tools to connect to an instance - AnalyticDB for PostgreSQL

AnalyticDB for PostgreSQL is fully compatible with the message-based protocol of PostgreSQL. You can connect to an instance by using tools that support the protocol.

Background information

AnalyticDB for PostgreSQL is fully compatible with the message-based protocol of PostgreSQL. You can connect to an AnalyticDB for PostgreSQL instance by using tools that support the protocol and GUI tools.

Tools that support the message-based protocol of PostgreSQL include psql, libpq, Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), and psycopg2.
GUI tools include Alibaba Cloud Data Management (DMS) and DBeaver 6.0.0 or later.

Note AnalyticDB for PostgreSQL V4.3 is built based on PostgreSQL 8.3. AnalyticDB for PostgreSQL V6.0 is built based on PostgreSQL 9.4.

Prerequisites

Before you connect to an AnalyticDB for PostgreSQL instance, you must perform the following operations:

Obtain the internal or public endpoint of the AnalyticDB for PostgreSQL instance.
- If your client is deployed on an Elastic Compute Service (ECS) instance that resides in the same region and has the same network type as the AnalyticDB for PostgreSQL instance, you can use the internal endpoint to connect to the AnalyticDB for PostgreSQL instance. Perform the following steps to view the internal endpoint:
  1. Log on to the AnalyticDB for PostgreSQL console.
  2. Find the instance that you want to manage and click the instance ID.
  3. In the Database Connection Information section of the Basic Information page, view the internal endpoint of the instance.
- If your client is deployed on an ECS instance that resides in a different region or has a different network type from your AnalyticDB for PostgreSQL instance or a system outside Alibaba Cloud, you must apply for a public endpoint and then use the public endpoint to connect to the AnalyticDB for PostgreSQL instance. For more information about how to apply for a public endpoint, see Manage public endpoints.
Obtain the IP address of your client and add the IP address to a whitelist of the AnalyticDB for PostgreSQL instance.
1. Obtain the IP address of your client.
  - ECS instances:
    - Linux instance
      Run the ifconfig command to view NIC information. You can view the IP addresses, subnet masks, gateways, Domain Name System (DNS) servers, and MAC addresses in the command output.
    - Windows instance
      In Command Prompt, run the ipconfig /all command to view NIC information. You can view the IP addresses, subnet masks, gateways, DNS servers, and MAC addresses in the command output.
  - On-premises devices or third-party clouds:
    - Linux operating system: Run the curl ipinfo.io |grep ip command on the on-premises device to obtain its public IP address.
    - Windows operating system: Visit ipinfo on the on-premises device to obtain its public IP address.
2. Add the IP address to a whitelist of the AnalyticDB for PostgreSQL instance. For more information, see Configure an IP address whitelist.
  Note
  - If your client is deployed on an ECS instance:
    If the ECS instance resides in the same virtual private cloud (VPC) as the AnalyticDB for PostgreSQL instance, you can add the internal IP address of the ECS instance to a whitelist and then connect to the AnalyticDB for PostgreSQL instance over an internal network.
    If the ECS instance does not reside in the same VPC as the AnalyticDB for PostgreSQL instance, you must add the public IP address of the ECS instance to a whitelist and then connect to the AnalyticDB for PostgreSQL instance over the Internet.
  - If your client is an on-premises system or a third-party cloud instance, you must add the public IP address of your client to a whitelist and then connect to the AnalyticDB for PostgreSQL instance over the Internet.

DMS

DMS allows you to manage relational databases such as MySQL, SQL Server, PostgreSQL, PPAS, and Petadata, online transaction processing (OLTP) databases such as PolarDB-X 1.0, online analytical processing (OLAP) databases such as AnalyticDB and Data Lake Analytics (DLA), and NoSQL databases such as MongoDB and Redis. DMS offers an integrated solution to manage data, schemas, and servers. You can also use DMS to grant permissions to users, audit security, view BI charts and data trends, track data, and optimize performance.

This section describes how to use DMS to log on to an AnalyticDB for PostgreSQL instance.

Log on to the AnalyticDB for PostgreSQL console.
Optional:Create an AnalyticDB for PostgreSQL instance. For more information, see Create an instance.
If an AnalyticDB for PostgreSQL instance has already been created, skip this step.
In the upper-left corner of the console, select the region where the instance resides.
Find the instance that you want to manage and click the instance ID.
Optional:Create a database account. For more information, see Create a database account.
If a privileged account has already been created, you can use this account to log on to the instance.
In the upper-right corner, click Log On to Database.
In the Log on to Database Instance dialog box, specify the Database Account and Database password parameters, and click Login.

For more information about DMS, see What is DMS?

psql

psql is a common command-line tool that is used together with Greenplum and provides a variety of commands. Its binary files are located in the bin directory of Greenplum.

Download client tools:

For Red Hat Enterprise Linux (RHEL) 6 or 7 and CentOS 6 or 7, you can download client tools from the following links and then decompress the tool package.


Engine version	System version	Download link
AnalyticDB for PostgreSQL V7.0	RHEL 7 or CentOS 7	adbpg7_client_package.el7.x86_64.tar.gz
AnalyticDB for PostgreSQL V6.0	RHEL 7 or CentOS 7	ADBPG_client_package_el7
AnalyticDB for PostgreSQL V6.0	RHEL 6 or CentOS 6	ADBPG_client_package_el6
AnalyticDB for PostgreSQL V4.3	RHEL 7 or CentOS 7	ADBPG_client_package_el7
AnalyticDB for PostgreSQL V4.3	RHEL 6 or CentOS 6	ADBPG_client_package_el6

After you download the client tool package, you must decompress the package and install the tools. Perform the following steps:

Run the following command to decompress the client tool package in its directory:
```
tar -xzvf <File name>
```
Example for decompressing an AnalyticDB for PostgreSQL V6.0 client tool package:
```
tar -xzvf adbpg_client_package.el7.x86_64.tar.gz
```
Switch to the bin directory and run the following command:
```
cd adbpg_client_package/bin
```
The bin directory includes client tools such as psql and pg_dump. Run the corresponding commands based on the reference documentation of each tool.
- For more information about how to use psql to connect to an AnalyticDB for PostgreSQL instance, see psql.
- pg_dump is a logical backup tool for PostgreSQL. For more information about how to use pg_dump, see pg_dump.

To use the psql tool to connect to an instance, perform the following steps:

Use one of the following methods to connect to an instance:
- Connection string
```
psql "host=yourgpdbaddress.gpdb.rds.aliyuncs.com port=5432 dbname=postgres user=gpdbaccount password=gpdbpassword"
```
- Specified parameters
```
psql  -h yourgpdbaddress.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount
```
  Parameters:
  - -h: the host address.
  - -p: the port number that is used to connect to the instance.
  - -d: the name of the database. The default value is postgres.
  - -U: the database account that is used to connect to the instance.
  Note You can run the psql --help command to view more options. You can also run \? to view the commands supported in psql.
Enter the password to go to the psql command interface.

References:

For more information about how to use psql in Greenplum, see Greenplum psql.
AnalyticDB for PostgreSQL also supports psql for PostgreSQL. Take note of the differences of psql commands between Greenplum and PostgreSQL. For more information, see PostgreSQL psql.

You can use the Docker toolbox for AnalyticDB for PostgreSQL, as shown in the following sample code. For more information about how to install Docker, visit the Docker official website.

Run the tool image corresponding to AnalyticDB for PostgreSQL V4.3.

docker run -idt --name=adbpgcli aliadbpg/adbpgcli:v4.3.0  
docker exec -it adbpgcli /bin/bash -l

Run the tool image corresponding to AnalyticDB for PostgreSQL V6.0.

docker run -idt --name=adbpgcli aliadbpg/adbpgcli:v6.3.0
docker exec -it adbpgcli /bin/bash -l

DBeaver

DBeaver can run on major operating systems such as Windows, macOS, and Linux. This section describes how to use DBeaver on Windows to connect to an AnalyticDB for PostgreSQL instance.

Download and run the installation package of DBeaver Community Edition. For more information about the download link, visit the DBeaver official website.
Start DBeaver and choose Database > New Database Connection.
In the Connect to a database dialog box, select the Greenplum database type and click Next.
Note The first time you connect to an instance, you must download the corresponding database driver file.

On the Main tab of the Connection Settings page, configure the parameters that are described in the following table.


Parameter	Description	Example
Host	The endpoint and port number that are used to connect to the AnalyticDB for PostgreSQL instance. To obtain this information, perform the following steps: Log on to the AnalyticDB for PostgreSQL console. In the upper-left corner of the console, select the region where the instance resides. Find the instance that you want to manage and click the instance ID. On the Basic Information page, view Internal Endpoint and Internal Port or Public Endpoint and Public Port in the Database Connection Information section.	gp-bp1g*************-master.gpdbmaster.rds.aliyuncs.com
Port		5432
Database	The name of the database.	postgres
Username	The database account that is used to connect to the AnalyticDB for PostgreSQL instance. For more information about how to create an account, see Create a database account.	testuser
Password	The password of the database account that is used to connect to the AnalyticDB for PostgreSQL instance.	PassW0rd

Click Test Connection. A Connected message indicates that the connection is established.
Note If an error message of org.postgresql.Driver is returned, you can perform the following operations to download or update the driver library of Greenplum:
1. On the Main tab of the Connection Settings page, click Edit Driver Settings.
2. On the Libraries tab, click Download/Update.
Click Finish.

JDBC

You can use the PostgreSQL or Greenplum JDBC driver to connect to AnalyticDB for PostgreSQL.

Use one of the following methods to download the JDBC driver:

Click PostgreSQL JDBC Driver to download the PostgreSQL JDBC driver and add the driver to an environment variable.
Obtain the Greenplum JDBC driver from the Greenplum official website. For more information, see Greenplum 6.3 Greenplum Client.

Sample code:

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
public class gp_conn {  
    public static void main(String[] args) {  
        try {  
            Class.forName("org.postgresql.Driver");  
            Connection db = DriverManager.getConnection("jdbc:postgresql://mygpdbpub.gpdb.rds.aliyuncs.com:5432/postgres","mygpdb","mygpdb");  
            Statement st = db.createStatement();  
            ResultSet rs = st.executeQuery("select * from gp_segment_configuration;");  
            while (rs.next()) {  
                System.out.print(rs.getString(1));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(2));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(3));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(4));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(5));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(6));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(7));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(8));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(9));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(10));  
                System.out.print("    |    ");  
                System.out.println(rs.getString(11));  
            }  
            rs.close();  
            st.close();  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
}

For more information about the JDBC driver, see the JDBC driver documentation.

Python

You can use psycopg2 to connect to Greenplum or PostgreSQL in Python. Perform the following steps:

Install psycopg2. If you use CentOS, you can use one of the following installation methods:
- Method 1: Run the yum -y install python-psycopg2 command.
- Method 2: Run the pip install psycopg2 command.
- Method 3: Use the source code.
```
yum install -y postgresql-devel*
wget  http://initd.org/psycopg/tarballs/PSYCOPG-2-6/psycopg2-2.6.tar.gz
tar xf psycopg2-2.6.tar.gz
cd psycopg2-2.6
python setup.py build
sudo python setup.py install
```

Set the PYTHONPATH environment variable for subsequent variable reference. Sample code:

 import psycopg2
 sql = 'select * from gp_segment_configuration;'
 conn = psycopg2.connect(database='gpdb', user='mygpdb', password='mygpdb', host='mygpdbpub.gpdb.rds.aliyuncs.com', port=5432)
 conn.autocommit = True
 cursor = conn.cursor()
 cursor.execute(sql)
 rows = cursor.fetchall()
 for row in rows:
     print row
 conn.commit()
 conn.close()

Sample command output:

(1, -1, 'p', 'p', 's', 'u', 3022, '192.168.2.158', '192.168.2.158', None, None)
(6, -1, 'm', 'm', 's', 'u', 3019, '192.168.2.47', '192.168.2.47', None, None)
(2, 0, 'p', 'p', 's', 'u', 3025, '192.168.2.148', '192.168.2.148', 3525, None)
(4, 0, 'm', 'm', 's', 'u', 3024, '192.168.2.158', '192.168.2.158', 3524, None)
(3, 1, 'p', 'p', 's', 'u', 3023, '192.168.2.158', '192.168.2.158', 3523, None)
(5, 1, 'm', 'm', 's', 'u', 3026, '192.168.2.148', '192.168.2.148', 3526, None)

libpq

libpq is the C language interface to PostgreSQL. You can use the libpq library to connect to and manage PostgreSQL databases in a C program. If Greenplum or PostgreSQL is deployed, you can find both the static and dynamic libraries of libpq in the lib directory.

For more information about examples of libpq, see Example Programs.
For more information about libpq, see libpq - C Library.

ODBC

The PostgreSQL ODBC driver is an open source tool licensed based on the GNU Lesser General Public License (LGPL) protocol. You can download the driver from the PostgreSQL official website.

To use the ODBC driver to connect to an AnalyticDB for PostgreSQL instance, perform the following steps:

Run the following commands to install the ODBC driver:

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

Run the following command to configure the data source:

vim /etc/odbc.ini

Add connection information. Example:

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

Note Replace **** in the sample code with the corresponding connection information.

Test the connectivity. Example:

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

After the ODBC driver is connected to an instance, connect your application to the driver. For more information, see PostgreSQL ODBC driver and psqlODBC HOWTO - C#.

Other client tools

GUI client tools
You can use other GUI client tools that are supported by Greenplum, such as DBeaver, to connect to an AnalyticDB for PostgreSQL instance.
Greenplum client tools
The Greenplum official website provides a tool package that includes JDBC, ODBC, and libpq. This package is easy to install and use. For more information, see the Greenplum documentation.