Connect to Apache Cloudberry

This document introduces how to connect to Apache Cloudberry.

Connection parameters

Users can connect to Apache Cloudberry using a PostgreSQL-compatible client program, such as psql. Users and administrators always connect to Apache Cloudberry through the coordinator. The segments cannot accept client connections.

To establish a connection to the Apache Cloudberry coordinator, you will need to know the following connection information and configure your client program accordingly.

Connection parameter	Description	Environment variable
Application name	The application name that is connecting to the database. The default value, held in the application_name connection parameter is psql.	$PGAPPNAME
Database name	The name of the database to which you want to connect. For a newly initialized system, use the postgres database to connect for the first time.	$PGDATABASE
Host name	The host name of the Apache Cloudberry coordinator. The default host is the local host.	$PGHOST
Port	The port number that the Apache Cloudberry coordinator instance is running on. The default is 5432.	$PGPORT
User name	The database user (role) name to connect as. This is not necessarily the same as your OS user name. Check with your Apache Cloudberry administrator if you are not sure what you database user name is. Note that every Apache Cloudberry system has one superuser account that is created automatically at initialization time. This account has the same name as the OS name of the user who initialized the Apache Cloudberry system (typically gpadmin).	$PGUSER

Connecting with psql provides example commands for connecting to Apache Cloudberry.

Supported client applications

Users can connect to Apache Cloudberry using various client applications:

• A number of Apache Cloudberry Client Applications are provided with your Apache Cloudberry installation. The psql client application provides an interactive command-line interface to Apache Cloudberry.
• Using standard Database Application Interfaces, such as ODBC and JDBC, users can create their own client applications that interface to Apache Cloudberry.
• Most client tools that use standard database interfaces, such as ODBC and JDBC, can be configured to connect to Apache Cloudberry.

Client utility applications

Apache Cloudberry comes installed with a number of client utility applications located in the $GPHOME/bin directory of your Apache Cloudberry coordinator host installation. The following are the most commonly used client utility applications:

Name	Usage
createdb	Creates a new database
createuser	Defines a new database role
dropdb	Removes a database
dropuser	Removes a role
psql	PostgreSQL interactive terminal
reindexdb	Reindexes a database
vacuumdb	Garbage-collects and analyzes a database

When using these client applications, you must connect to a database through the Apache Cloudberry coordinator instance. You will need to know the name of your target database, the host name and port number of the coordinator, and what database user name to connect as. This information can be provided on the command-line using the options -d, -h, -p, and -U respectively. If an argument is found that does not belong to any option, it will be interpreted as the database name first.

All of these options have default values which will be used if the option is not specified. The default host is the local host. The default port number is 5432. The default user name is your OS system user name, as is the default database name. Note that OS user names and Apache Cloudberry user names are not necessarily the same.

If the default values are not correct, you can set the environment variables PGDATABASE, PGHOST, PGPORT, and PGUSER to the appropriate values, or use a psql ~/.pgpass file to contain frequently-used passwords.

Connect with psql

Depending on the default values used or the environment variables you have set, the following examples show how to access a database via psql:

$ psql -d mydatabase -h coordinator_host -p 5432 -U `gpadmin`

$ psql mydatabase

$ psql

If a user-defined database has not yet been created, you can access the system by connecting to the postgres database. For example:

$ psql postgres

After connecting to a database, psql provides a prompt with the name of the database to which psql is currently connected, followed by the string => (or =# if you are the database superuser). For example:

mydatabase=>

At the prompt, you might type in SQL commands. A SQL command must end with a ; (semicolon) in order to be sent to the server and run. For example:

=> SELECT * FROM mytable;

Connect with application interfaces

You might want to develop your own client applications that interface to Apache Cloudberry. PostgreSQL provides a number of database drivers for the most commonly used database application programming interfaces (APIs), which can also be used with Apache Cloudberry. These drivers are available as a separate download. Each driver (except libpq, which comes with PostgreSQL) is an independent PostgreSQL development project and must be downloaded, installed and configured to connect to Apache Cloudberry. The following drivers are available:

API	PostgreSQL Driver	Download Link
ODBC	psqlODBC	https://odbc.postgresql.org/
JDBC	pgjdbc	https://jdbc.postgresql.org/
Perl DBI	pgperl	https://metacpan.org/release/DBD-Pg
Python DBI	pygresql	http://www.pygresql.org/
Python DBI	psycopg2	https://www.psycopg.org/
libpq C Library	libpq	https://www.postgresql.org/docs/14/libpq.html

General instructions for accessing a Apache Cloudberry with an API are:

1. Download your programming language platform and respective API from the appropriate source. For example, you can get the Java Development Kit (JDK) and JDBC API from Oracle.
2. Write your client application according to the API specifications. When programming your application, be aware of the SQL support in Apache Cloudberry so you do not include any unsupported SQL syntax.

Download the appropriate driver and configure connectivity to your Apache Cloudberry coordinator instance.

Troubleshoot connection problems

A number of things can prevent a client application from successfully connecting to Apache Cloudberry. This topic explains some of the common causes of connection problems and how to correct them.

Problem	Solution
No pg_hba.conf entry for host or user	To enable Apache Cloudberry to accept remote client connections, you must configure your Apache Cloudberry coordinator instance so that connections are allowed from the client hosts and database users that will be connecting to Apache Cloudberry. This is done by adding the appropriate entries to the pg_hba.conf configuration file (located in the coordinator instance's data directory).
Apache Cloudberry is not running	If the Apache Cloudberry coordinator instance is down, users will not be able to connect. You can verify that the Apache Cloudberry system is up by running the gpstate utility on the Apache Cloudberry coordinator host.
Network problems: Interconnect timeouts	If users connect to the Apache Cloudberry coordinator host from a remote client, network problems can prevent a connection (for example, DNS host name resolution problems, the host system is down, and so on.). To ensure that network problems are not the cause, connect to the Apache Cloudberry coordinator host from the remote client host. For example: ping hostname If the system cannot resolve the host names and IP addresses of the hosts involved in Apache Cloudberry, queries and connections will fail. For some operations, connections to the Apache Cloudberry coordinator use localhost and others use the actual host name, so you must be able to resolve both. If you encounter this error, first make sure you can connect to each host in your Apache Cloudberry array from the coordinator host over the network. In the /etc/hosts file of the coordinator and all segments, make sure you have the correct host names and IP addresses for all hosts involved in the Apache Cloudberry array. The 127.0.0.1 IP must resolve to localhost.