This topic describes how to connect to an AnalyticDB for PostgreSQL instance. AnalyticDB for PostgreSQL is fully compatible with the message-based protocol of PostgreSQL. Therefore, you can connect to an AnalyticDB for PostgreSQL instance by using tools that support the protocol, such as psql, libpq, Java Database Connectivity (JDBC), Open Database Connectivity (ODBC), and psycopg2. You can also use graphical user interface (GUI) tools such as pgAdmin, DBeaver, and Navicat. AnalyticDB for PostgreSQL V4.3 supports only pgAdmin III 1.6.3 and earlier. AnalyticDB for PostgreSQL V6.0 supports pgAdmin 4 and earlier. You can use Data Management (DMS) to manage and develop AnalyticDB for PostgreSQL instances.

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

DMS console

DMS allows you to manage relational databases such as MySQL, SQL Server, PostgreSQL, Postgres Plus Advanced Server (PPAS), and Petadata, online analytical processing (OLAP) databases such as Distributed Relational Database Service (DRDS), 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 authorize users, audit security, view BI charts and data trends, track data, and optimize performance.

This section describes how to use the DMS console to connect to an AnalyticDB for PostgreSQL instance.

  1. Log on to the AnalyticDB for PostgreSQL console.
  2. Create an instance. For more information, see Create an instance. If an instance is created, find the instance and click the instance ID.
  3. Create an account. For more information, see Configure an account.
    Note This account is used to log on to the DMS console. A single account can be created for each instance.
  4. In the upper-right corner, click Login Database.
  5. On the Login instance page, enter the required information about the database, and click Login.

psql

psql is a command line tool used together with Greenplum, and provides a variety of commands. The binary files of psql are located in the bin directory of Greenplum. To use psql to connect to an AnalyticDB for PostgreSQL instance, perform the following steps:

  1. Connect to the instance by using one of the following methods:

    • Connection string

      psql "host=yourgpdbaddress.gpdb.rds.aliyuncs.com port=3432 dbname=postgres user=gpdbaccount password=gpdbpassword"
    • Specified parameters

      psql  -h yourgpdbaddress.gpdb.rds.aliyuncs.com -p 3432 -d postgres -U gpdbaccount

      The following section describes the parameters.

      • -h: the host address.
      • -p: the port used to connect to the database.
      • -d: the name of the database. The default value is postgres.
      • -U: the account used to connect to the database.
      • 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 shell interface. The following example describes the psql shell interface:
    postgres=>

References

  • For more information about how to use psql, visit psql in the Greenplum database documentation.

  • AnalyticDB for PostgreSQL also supports psql commands for PostgreSQL. Take note of the differences between psql commands in PostgreSQL and psql commands in AnalyticDB for PostgreSQL. For more information, visit psql in the PostgreSQL documentation.

Download client tools

The download URL varies based on your operating system version:

Use client tools

After you download the client tool package, you can use the client tools. The following example shows how to use a client tool to connect to an AnalyticDB for PostgreSQL V6.0 instance when you use CentOS 7.

  1. Go to the directory where the client tool package is downloaded and decompress the package.

    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 command based on the reference documentation of each tool.

    • For information about how to use psql to connect to an AnalyticDB for PostgreSQL instance, visit psql.

    • pg_dump is a logical backup tool for PostgreSQL. For information about how to use pg_dump, visit pg_dump.

You can use the Docker toolkit for AnalyticDB for PostgreSQL, as shown in the following sample code. For more information about how to install Docker, visit Get Started with Docker.

# Run the tool image corresponding to the AnalyticDB for PostgreSQL V 4.x.
docker run -idt --name=adbpgcli aliadbpg/adbpgcli:v4.3.0  
docker exec -it adbpgcli /bin/bash -l

# Run the tool image corresponding to the AnalyticDB for PostgreSQL V 6.x.
docker run -idt --name=adbpgcli aliadbpg/adbpgcli:v6.3.0
docker exec -it adbpgcli /bin/bash -l
[root@adbpgcli /]# psql --help

pgAdmin

pgAdmin is a GUI client tool for PostgreSQL and can be used to connect to an AnalyticDB for PostgreSQL instance. For more information, visit pgAdmin. AnalyticDB for PostgreSQL V4.3 is based on the PostgreSQL 8.3 kernel. Therefore, you must use pgAdmin III 1.6.3 or earlier to connect to an AnalyticDB for PostgreSQL V4.3 instance. AnalyticDB for PostgreSQL V4.3 does not support pgAdmin 4. AnalyticDB for PostgreSQL V6.0 is based on the PostgreSQL 9.4 kernel and supports pgAdmin 4 or earlier.

You can download pgAdmin III 1.6.3 or pgAdmin 4 from the PostgreSQL official website. pgAdmin is compatible with various operating systems such as Windows, macOS, and Linux. For more GUI client tools, see GUI client tools.

Procedure

  1. Download and install the supported version of pgAdmin.

  2. Choose File > Add Server.

  3. In the New Server Registration dialog box, set parameters.

  4. Click OK to connect to the AnalyticDB for PostgreSQL instance.

JDBC

You must use the official PostgreSQL JDBC driver. 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:3432/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, visit The PostgreSQL JDBC Interface.

Python

You can use psycopg2 to connect to Greenplum or PostgreSQL for the Python programming language. 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 and then import psycopg2:
     import psycopg2
     sql = 'select * from gp_segment_configuration;'
     conn = psycopg2.connect(database='gpdb', user='mygpdb', password='mygpdb', host='mygpdbpub.gpdb.rds.aliyuncs.com', port=3432)
     conn.autocommit = True
     cursor = conn.cursor()
     cursor.execute(sql)
     rows = cursor.fetchall()
     for row in rows:
         print row
     conn.commit()
     conn.close()

    The system displays information similar to the following example:

    (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 application programmer's interface to PostgreSQL. You can use libpq libraries to access 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, visit Example Programs.

For more information about libpq, visit PostgreSQL 9.4.10 Documentation - Chapter 31. 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 it from the official PostgreSQL website.

Procedure

  1. Install the driver.

    yum install -y unixODBC.x86_64  
    yum install -y postgresql-odbc.x86_64
  2. View the driver configuration.

    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
  3. Configure the DSN. Replace **** in the following code with actual information.

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

    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
  5. After ODBC is connected to the instance, connect your application to ODBC. For more information, visit PostgreSQL ODBC driver and psqlODBC HOWTO - C#.

Other client tools

GUI client tools

You can use the following GUI client tools to connect to AnalyticDB for PostgreSQL instances:

Greenplum client tools

The official Greenplum website provides a tool package, which includes JDBC, ODBC, and libpq. The package is easy to install and use. For more information, visit Greenplum Database 4.3 Connectivity Tools for UNIX.

References