edit-icon download-icon

Connect to a HybridDB for PostgreSQL database

Last Updated: Jun 25, 2018

Cloud Database HybridDB for PostgreSQL is fully compatible with the message protocols of PostgreSQL 8.2 and allows direct access to tools that support the PostgreSQL 8.2 message protocols such as libpq, JDBC, ODBC, psycopg2, pgadmin III, and so on.

Greenplum also provides an installation package that includes JDBC, ODBC, and libpq, which can be easily installed and used by users. For more information please see the Greenplum official documentation.

GUI Tools

HybridDB for PostgreSQL users can use Greenplum-supported graphical client tools directly, such as [SQL Workbench] (http://www.sql-workbench.net/), [Navicat Premium] (https://www.navicat.com/ Download/navicat-premium, [Navicat For PostgreSQL] (https://www.navicat.com/download/navicat-for-postgresql), [pgadmin III (1.6.3)] (https://www.postgresql.com) Org/ftp/pgadmin/pgadmin3/v1.6.3/) and so on.

The following content takes pgAdmin III as an example to illustrate the use of graphical client tools.

pgAdmin III

pgAdmin III is a GUI client of PostgreSQL, which can be used directly to connect to HybridDB for PostgreSQL. For more information, see paAdmin official page.

You can download pgAdmin III 1.6.3 from PostgreSQL official website . pgAdmin III 1.6.3 supports a variety of platforms, such as Windows, MacOS, and Linux.

Note: HybridDB for PostgreSQL supports PostgreSQL 8.2 version, and you must use the matching pgAdmin version. The matching version is pgAdmin III 1.6.3 or earlier versions.

Procedure

  1. Download and install pgAdmin III 1.6.3 or an earlier version.

  2. Select File > Add Server to go to the New Server Registration page.

  3. Enter the Properties as shown in the following figure:

    new server registration

  4. Click OK to connect to the HybridDB for PostgreSQL database.

Command Line tools

Users can also use following several command line tools to connect to HybridDB for PostgreSQL instance’s database.

psql

psql is a common command line client tool for HybridDB for PostgreSQL. For RHEL (Red Hat Enterprise Linux) 6 or RHEL 7 and CentOS 6 or CentOS 7, Alibaba Cloud provides compressed software packages that can be used directly after decompression. .

  • For RHEL 6 or CentOS 6 platforms, click download.

  • For RHEL 7 or CentOS 7 platforms, click download.

For other Linux platforms, users need to download the source code and use it after compiling and installation. The compiling methods is as follows:

1. To get the source code, the following methods are available:

  • Get the git directory directly(make sure that you have installed the git tool).

    1. git clone https://github.com/greenplum-db/gpdb.git
    2. cd gpdb
    3. git checkout 5d870156
  • Directly download codes.

    1. wget https://github.com/greenplum-db/gpdb/archive/5d87015609abd330c68a5402c1267fc86cbc9e1f.zip
    2. unzip 5d87015609abd330c68a5402c1267fc86cbc9e1f.zip
    3. cd gpdb-5d87015609abd330c68a5402c1267fc86cbc9e1f

2. You need the GCC or other compilers to compile the code and install the software.

  1. ./configure
  2. make -j32
  3. make install

After the installation the path of psql is as follows:

  1. psql: `/usr/local/pgsql/bin/psql`

Enter the preceding directory, and use psql to connect to HybridDB for PostgreSQL instance’s database following to the procedure:

  1. Use one of the following methods to connect to the database:

    • Concatenate strings

      1. psql "host=yourgpdbaddress.gpdb.rds.aliyuncs.com port=3568 dbname=postgres user=gpdbaccount password=gpdbpassword"
    • Specify parameters

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

      Parameter descriptions:

    • -h: specifies the host address.
    • -p: specifies the port number.
    • -d: specifies the database (the default database is postgres).
    • -U: specifies the connected user.

      You can view more parameters by performing psql-- help. And in the psql prompt, you can view more supported psql commands by performing ‘\?’.

  2. Enter the password to go to the psql shell interface. The psql shell is as follows:

    1. postgres=>

Reference

JDBC

Users can use JDBC to connect to HybridDB for PostgreSQL instance’s database. Here are two ways to get this tool:

Providing the follow code example as a reference, and the users can modify it according to the practices.

Code example:

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.ResultSet;
  4. import java.sql.SQLException;
  5. import java.sql.Statement;
  6. public class gp_conn {
  7. public static void main(String[] args) {
  8. try {
  9. Class.forName("org.postgresql.Driver");
  10. Connection db = DriverManager.getConnection("jdbc:postgresql://mygpdbpub.gpdb.rds.aliyuncs.com:3568/postgres","mygpdb","mygpdb");
  11. Statement st = db.createStatement();
  12. ResultSet rs = st.executeQuery("select * from gp_segment_configuration;");
  13. while (rs.next()) {
  14. System.out.print(rs.getString(1));
  15. System.out.print(" | ");
  16. System.out.print(rs.getString(2));
  17. System.out.print(" | ");
  18. System.out.print(rs.getString(3));
  19. System.out.print(" | ");
  20. System.out.print(rs.getString(4));
  21. System.out.print(" | ");
  22. System.out.print(rs.getString(5));
  23. System.out.print(" | ");
  24. System.out.print(rs.getString(6));
  25. System.out.print(" | ");
  26. System.out.print(rs.getString(7));
  27. System.out.print(" | ");
  28. System.out.print(rs.getString(8));
  29. System.out.print(" | ");
  30. System.out.print(rs.getString(9));
  31. System.out.print(" | ");
  32. System.out.print(rs.getString(10));
  33. System.out.print(" | ");
  34. System.out.println(rs.getString(11));
  35. }
  36. rs.close();
  37. st.close();
  38. } catch (ClassNotFoundException e) {
  39. e.printStackTrace();
  40. } catch (SQLException e) {
  41. e.printStackTrace();
  42. }
  43. }
  44. }

For more detailed information, see PostgreSQL JDBC Interface.

Python

Users can also use Python to connect to HybridDB for PostgreSQL instance’s database. Python uses the psycopg2 library to connect to Greenplum and PostgreSQL. The procedure for using the tool is described as follows:

  1. Install psycopg2. In CentOS, three methods are available:

    • Perform yum -y install python-psycopg2

    • Perform pip install psycopg2

    • Install from the source code.

      1. yum install -y postgresql-devel*
      2. wget http://initd.org/psycopg/tarballs/PSYCOPG-2-6/psycopg2-2.6.tar.gz
      3. tar xf psycopg2-2.6.tar.gz
      4. cd psycopg2-2.6
      5. python setup.py build
      6. sudo python setup.py install
  2. After the installation, set the PYTHONPATH environment variable before using the tool. For example,

    1. import psycopg2
    2. sql = 'select * from gp_segment_configuration;'
    3. conn = psycopg2.connect(database='gpdb', user='mygpdb', password='mygpdb', host='mygpdbpub.gpdb.rds.aliyuncs.com', port=3568)
    4. conn.autocommit = True
    5. cursor = conn.cursor()
    6. cursor.execute(sql)
    7. rows = cursor.fetchall()
    8. for row in rows:
    9. print row
    10. conn.commit()
    11. conn.close()

    A result similar to the following is returned.

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

libpq

Libpq is the C language interface of PostgreSQL database. You can access a PostgreSQL database in a C program through libpq to manipulate database. After Greenplum or PostgreSQL is installed, you can find its static libraries and dynamic libraries under the lib directory.

For related cases, see libpq Example Programs.

ODBC

PostgreSQL ODBC is an open-source version based on the LGPL (GNU Lesser General Public License) protocol. You can download it from the official website of PostgreSQL.

Procedure

  1. Install the driver.

    1. yum install -y unixODBC.x86_64
    2. yum install -y postgresql-odbc.x86_64
  2. Check the driver’s configuration.

    1. cat /etc/odbcinst.ini
    2. # Example driver definitions
    3. # Driver from the postgresql-odbc package
    4. # Setup from the unixODBC package
    5. [PostgreSQL]
    6. Description = ODBC for PostgreSQL
    7. Driver = /usr/lib/psqlodbcw.so
    8. Setup = /usr/lib/libodbcpsqlS.so
    9. Driver64 = /usr/lib64/psqlodbcw.so
    10. Setup64 = /usr/lib64/libodbcpsqlS.so
    11. FileUsage = 1
    12. # Driver from the mysql-connector-odbc package
    13. # Setup from the unixODBC package
    14. [MySQL]
    15. Description = ODBC for MySQL
    16. Driver = /usr/lib/libmyodbc5.so
    17. Setup = /usr/lib/libodbcmyS.so
    18. Driver64 = /usr/lib64/libmyodbc5.so
    19. Setup64 = /usr/lib64/libodbcmyS.so
    20. FileUsage = 1
  3. Configure DSN as the following codes. Change **** in the codes to the actual connection information.

    1. [mygpdb]
    2. Description = Test to gp
    3. Driver = PostgreSQL
    4. Database = ****
    5. Servername = ****.gpdb.rds.aliyuncs.com
    6. UserName = ****
    7. Password = ****
    8. Port = ****
    9. ReadOnly = 0
  4. Test the connectivity.

    1. echo "select count(*) from pg_class" | isql mygpdb
    2. +---------------------------------------+
    3. | Connected! |
    4. | |
    5. | sql-statement |
    6. | help [tablename] |
    7. | quit |
    8. | |
    9. +---------------------------------------+
    10. SQL> select count(*) from pg_class
    11. +---------------------+
    12. | count |
    13. +---------------------+
    14. | 388 |
    15. +---------------------+
    16. SQLRowCount returns 1
    17. 1 rows fetched
  5. After ODBC is connected to the instance, connect applications to ODBC. For more information, see PostgreSQL ODBC driver and psqlODBC HOWTO - C#.

Windows and other platforms

Go to Pivotal Greenplum Client for download links of other client tools for Windows and other platforms.

Reference

Thank you! We've received your feedback.