Use the standard PostgreSQL tool — psql to connect to ADPG

Eugenia Kuzina

The psql tool is a standard PostgreSQL client application that you can use to connect to ADPG. It is an interactive terminal that allows you to run SQL queries and view their results. You can execute queries from the command line or from an SQL file. The psql tool is available on each ADPG cluster node.

Run the following commands to start working with psql:

$ sudo su - postgres

$ psql

The first command switches the shell to a login mode with the access rights for the postgres user. The second one runs psql for the default database (postgres).

You can use the psql options from the table below to specify a database name, host, port, and user instead of default values.

psql options
Option	Description
-d <dbname> --dbname=<dbname>	Specifies the name of the database to connect to
-h <hostname> --host=<hostname>	Specifies the host name where the server is located
-p <port> --port=<port>	Specifies the TCP port on which the server listens for connections. The default value is `5432`
-U <username> --username=<username>	Connects to a database as the specified user

Example:

$ psql -h 10.92.6.36 -p 5432 -U user1 -d postgress

TIP

If you face connection problems, use the pg_isready tool to check the connection status of a PostgreSQL database server.

When the psql command is executed, the terminal displays the psql prompt with the database name and the # character at the end. All subsequent commands should be written after #.

psql (14.3)
Type "help" for help.

postgres=#

You can find examples of SQL queries executed from the command line in the article Example of working with tables via psql.

To run an SQL query from a file, use the meta-command \i:

\i /path/to/file/filename.sql

To logout from psql, use the following command:

\q

The table below lists some meta-commands.

psql meta-commands
Command	Description
`\c <database_name>`	Establishes a new connection to the specified database
`\l`	Lists all the databases on the server and shows their names, owners, character set encodings, and access privileges
`\dt`	Lists all tables in the database
`\dt+`	Lists all tables in the database with its persistence status (`permanent`, `temporary`, or `unlogged`), physical size on disk, and description
`\dn`	Lists all schemas in the database
`\d+ <table_name>`	Shows detailed information about the table
`\dv`	Lists all views in the current database
`\df`	Lists functions with their result data types, argument data types, and function types, which are classified as `agg` (aggregate), `normal`, `procedure`, `trigger`, or `window`
`\x`	Sets expanded table formatting mode
`\du`	Lists database users (roles)

Meta-commands send queries to system tables and views to get the required information. To display these queries, set the ECHO_HIDDEN variable to on:

\set ECHO_HIDDEN on

Example:

postgres=# \set ECHO_HIDDEN on
postgres=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

               List of relations
  Schema  |       Name       | Type  |  Owner
----------+------------------+-------+----------
 public   | author           | table | postgres
 public   | book             | table | postgres
 public   | us_gaz           | table | postgres
 public   | us_lex           | table | postgres
 public   | us_rules         | table | postgres
 public   | users            | table | postgres
(6 rows)

To disable displaying queries with the result of a meta-command, set the ECHO_HIDDEN variable to off:

\set ECHO_HIDDEN off

Found a mistake? Seleсt text and press Ctrl+Enter to report it