This topic describes how to connect to an AnalyticDB for PostgreSQL instance by using tools that support the message-based protocol of PostgreSQL.

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.

DMS

DMS allows you to manage relational, online transaction processing (OLTP), online analytical processing (OLAP), and NoSQL database services. Relational database services include ApsaraDB RDS for MySQL, ApsaraDB RDS for SQL Server, ApsaraDB RDS for PostgreSQL, and ApsaraDB RDS for PPAS. OLTP database services include Distributed Relational Database Service (DRDS). OLAP database services include AnalyticDB for PostgreSQL and Data Lake Analytics (DLA). NoSQL database services include ApsaraDB for MongoDB and ApsaraDB for Redis. DMS offers an integrated solution to manage data, schemas, and servers. You can also use DMS to authorize 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.

  1. Log on to the AnalyticDB for PostgreSQL console.
  2. 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.

  3. In the upper-left corner of the console, select the region where the instance resides.
  4. Find the instance that you want to manage and click its ID.
  5. Optional:Create a database account. For more information, see Create a database account.

    If an initial account has already been created, you can use this account to log on to the instance.

  6. In the upper-right corner, click Login Database. Log on to an instance
  7. In the Login instance dialog box, specify the Database Account and Database password parameters, and click Login.
    Note AnalyticDB for PostgreSQL

For more information about DMS, see Overview.

psql

psql is a common command-line tool 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 decompress the tool package:

After you download the client tool package, you must decompress the package and install the tools. This section describes how to install the client tools for AnalyticDB for PostgreSQL V6.0 on CentOS 7.

  1. Run the following command to decompress the client tool package in its directory:
    tar -xzvf adbpg_client_package.el7.x86_64.tar.gz
  2. Switch to the bin directory and run the following command:
    cd adbpg_client_package/bin
  3. 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:

  1. 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

      Parameter description:

      • -h: the host address.
      • -p: the port number used to connect to the instance.
      • -d: the name of the database. The default value is postgres.
      • -U: the account used to connect to the instance.
      Note You can run the psql --help command to view more options. You can also run the \? command to view the commands supported in psql.
  2. 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.

  1. Download and run the installation package of DBeaver Community Edition. For more information about the download link, visit the DBeaver official website.
  2. Start DBeaver and choose Database > New Database Connection.
  3. 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.
  4. On the Main tab of the Connection Settings page, set the following parameters.
    Parameter Description Example
    Host The endpoint and port number used to connect to the AnalyticDB for PostgreSQL instance. To obtain this information, perform the following steps:
    1. Log on to the AnalyticDB for PostgreSQL console.
    2. In the upper-left corner of the console, select the region where the instance resides.
    3. Find the instance that you want to manage and click its ID.
    4. On the Basic Information page, view Internal Endpoint and Internal Port or Public Address and Public Port in the Database Connection section. View the endpoint and port number
    gp-bp1g*************-master.gpdbmaster.rds.aliyuncs.com
    Port 5432
    Database The name of the database. postgres
    Username The database account 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 used to connect to the AnalyticDB for PostgreSQL instance. PassW0rd
  5. 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.
  6. 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:

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 PostgreSQL JDBC Interface.

Python

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

  1. 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
  2. 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.

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:

  1. Run the following commands to install the ODBC driver:
    yum install -y unixODBC.x86_64  
    yum install -y postgresql-odbc.x86_64
  2. 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.
  3. 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
  4. 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 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.

References