Github

Connect to Greengage DB via psql

Andrey Aksenov

The psql utility is a PostgreSQL client application that allows you to connect to your Greengage DB cluster and interact with it: display information about database objects, execute SQL queries, and more. To interact with a Greengage DB cluster, you must connect to the master host. Segments are not intended to accept client connections.

Connection parameters overview

To connect to the master host, you need to know the following connection information:

  • Database name

    This could be the default postgres database created automatically during the initialization or a database created manually.

  • Database user name

    This might be the superuser account created automatically during the initialization (typically gpadmin) or any user that has the privileges to access the specified database.

  • Host address

    This is the DNS-resolvable domain name (for example, mdw.example.com) or IP address of the host on which the master instance is running.

  • Port number

    This should be the port number that the master instance is running on (typically 5432).

You can specify the required connection parameters by configuring corresponding environment variables (such as PGDATABASE or PGUSER) or pass these parameters directly to the psql command.

Configure the environment

Before connecting to the database using psql, you can configure the default connection parameters using environment variables, such as PGDATABASE, PGUSER, and PGPORT. The example below shows how to do this:

$ export PGDATABASE=testdb
$ export PGUSER=gpadmin
$ export PGPORT=5432

The values of these variables are used if the corresponding command-line options are not passed to psql.

Apart from connection-related options, you can set other environment variables to customize the psql behavior. For example, you can override the time zone and encoding by using the PGTZ and PGCLIENTENCODING environment variables, respectively. Learn more in the Environment section.

Connect to a database

To connect to a database, execute the psql command with the corresponding options described below.

Option Description Default value Environment variable

-d

The database name

The operating system’s user name

PGDATABASE

-U

The name of the user used to connect to the specified database

The operating system’s user name

PGUSER

-h

The name or IP address of the master host. If the value begins with a slash, it is used as the directory for the Unix domain socket

/var/run/postgresql

PGHOST

-p

The TCP port used to connect to the master host

5432

PGPORT

Local connection

This section shows several examples of connecting to databases locally from the master host as a superuser (gpadmin). In this case, a Unix domain socket is used to connect to databases.

Before connecting to the database, switch to the gpadmin user:

$ sudo su - gpadmin

The examples below demonstrate how to connect to databases via psql locally:

  • Connect to the default database:

    $ psql

  • Connect to the testdb database:

    $ psql testdb

Remote connection

To connect to the database remotely via TCP/IP, you also need to pass the host address and port values, for example:

$ psql -d sales -U alice -h 192.168.1.10 -p 5432

If password authentication is enabled, enter the password when the following prompt is shown:

Password for user alice:

Press Enter.

NOTE

Ensure that the pg_hba.conf file allows remote connections from the specified user.

Connect to segments

You can connect to segments directly in utility mode only. To do this, set the gp_session_role server configuration parameter to utility, for example:

$ PGOPTIONS="-c gp_session_role=utility" psql -p 10000 -d testdb

Execute commands interactively

After connecting to a database, psql provides a prompt displaying the name of the connected database. The database name is followed by one of the following strings:

  • =# if you are connected as a superuser:

    testdb=#

  • => if you are connected as a regular user:

    sales=>

At the prompt, type in a command and press Enter to execute it.

SQL commands

The example below shows how to select all the data from the gp_segment_configuration system view:

SELECT * FROM gp_segment_configuration;

SQL commands should end with ; (semicolon) to be sent to the server and run.

Meta-commands

Meta-commands start with a backslash (\) and can be used to perform various database-related tasks, such as displaying information about database objects and controlling output formatting. Below are a few examples of using such commands:

  • Connect to the postgres database:

    \c postgres

  • List all the databases on the server:

    \l

  • Logout from psql:

    \q

Execute commands non-interactively

psql allows you to execute commands or scripts directly from the command line without entering the interactive shell:

  • The SQL query below is passed to the psql command using the -c option:

    $ psql -c 'SELECT * FROM gp_segment_configuration;'

    If you need to mix SQL and meta-commands, pipe the string into psql, for example:

    $ echo '\x \\ SELECT * FROM gp_segment_configuration;' | psql

  • The command below executes SQL commands from the myscript.sql file non-interactively:

    $ psql -f /home/gpadmin/test/myscript.sql
Previous
Configure a time zone and localization settings
Next
pg_hba.conf
Open search page