gsql

gsql, provided by openGauss, is a database connection tool that runs in the command line. You can use gsql to connect to the server and perform operations and maintenance. In addition, gsql provides multiple Advanced Features for users.

Overview

Basic Features

• Connect to the database: By default, only the local server can be connected. To connect to a remote database, you must configure the server. For details, see Gsql Connection and Usage.

  NOTE

  If gsql is used to connect to a database, the connection timeout period will be 5 minutes. If the database has not correctly set up a connection and authenticated the identity of the client within this period, gsql will time out and exit. To resolve this problem, see Troubleshooting.

• Run SQL statements: Interactively entered SQL statements and specified SQL statements in a file can be run.

• Run meta-commands: Meta-commands help the administrator view database object information, query cache information, format SQL output, and connect to a new database. For details about meta-commands, see Meta-Command Reference.

Advanced Features

Table 1 lists the advanced features of gsql.

Table 1 Advanced features of gsql

\set varname value

\unset varname

• Variables

  To set a variable, run the \set meta-command of gsql. For example, to set variable foo to bar, run the following command:

  openGauss=# \set foo bar
  

  To reference the value of a variable, add a colon (:) before the variable. For example, to view the value of variable foo, run the following command:

  openGauss=# \echo :foo
  bar
  

  The variable reference method is suitable for regular SQL statements and meta-commands.

  gsql pre-defines some special variables and plans the values of these variables. To ensure compatibility with later versions, do not use these variables for other purposes. For details about special variables, see Table 2.

  NOTE

  • All the special variables consist of upper-case letters, digits, and underscores (_).
  • To view the default value of a special variable, run the \echo :varname meta-command, for example, \echo :DBNAME.

  Table 2 Settings of special variables

  Variable	Setting Method	Description
  DBNAME	\set DBNAME dbname	Name of the connected database. This variable is set again when a database is connected.
  ECHO	\set ECHO all | queries	If this variable is set to all, only the query information is displayed. This has the same effect as specifying the -a parameter when gsql is used to connect to a database. If this variable is set to queries, the command line and query information are displayed. This has the same effect as specifying the -e parameter when gsql is used to connect to a database.
  ECHO_HIDDEN	\set ECHO_HIDDEN on | off | noexec	When a meta-command (such as \dg) is used to query database information, the value of this variable determines the query behavior. If this variable is set to on, the query statements that are called by the meta-command are displayed, and then the query result is displayed. This has the same effect as specifying the -E parameter when gsql is used to connect to a database. If this variable is set to off, only the query result is displayed. If this variable is set to noexec, only the query information is displayed, and the query is not run.
  ENCODING	\set ENCODING encoding	Character set encoding of the current client.
  FETCH_COUNT	\set FETCH_COUNT variable	If the value is an integer greater than 0, for example, n, n lines will be selected from the result set to the cache and displayed on the screen when the SELECT statement is run. If this variable is not set or set to a value less than or equal to 0, all results are selected at a time to the cache when the SELECT statement is run. NOTE: A proper variable value helps reduce the memory usage. The recommended value range is from 100 to 1000.
  HISTCONTROL	\set HISTCONTROL ignorespace | ignoredups | ignoreboth | none	ignorespace: A line started with a space is not written to the historical record. ignoredups: A line that exists in the historical record is not written to the historical record. ignoreboth, none, or other values: All the lines read in interaction mode are saved in the historical record. NOTE: none indicates that HISTCONTROL is not set.
  HISTFILE	\set HISTFILE filename	Specifies the file for storing historical records. The default value is ~/.bash_history.
  HISTSIZE	\set HISTSIZE size	Specifies the number of commands to store in the command history. The default value is 500.
  HOST	\set HOST hostname	Specifies the name of a connected host.
  IGNOREEOF	\set IGNOREEOF variable	If this variable is set to a number, for example, 10, the first nine EOF characters (generally Ctrl+C) entered in gsql are neglected and the gsql program exits when the tenth Ctrl+C is entered. If this variable is set to a non-numeric value, the default value is 10. If this variable is deleted, gsql exits when an EOF is entered.
  LASTOID	\set LASTOID oid	Specifies the last OID, which is the value returned by an INSERT or lo_import command. This variable is valid only before the output of the next SQL statement is displayed.
  ON_ERROR_ROLLBACK	\set ON_ERROR_ROLLBACK on | interactive | off	If the value is on, an error that may occur in a statement in a transaction block is ignored and the transaction continues. If the value is interactive, the error is ignored only in an interactive session. If the value is off (default value), the error triggers the rollback of the transaction block. In on_error_rollback-on mode, a SAVEPOINT is set before each statement of a transaction block, and an error triggers the rollback of the transaction block.
  ON_ERROR_STOP	\set ON_ERROR_STOP on | off	on: specifies that the execution stops if an error occurs. In interactive mode, gsql returns the output of executed commands immediately. off (default value): specifies that an error, if occurring during the execution, is ignored, and the execution continues.
  PORT	\set PORT port	Specifies the port number of a connected database.
  USER	\set USER username	Specifies the database user you are currently connected as.
  VERBOSITY	\set VERBOSITY terse | default | verbose	This variable can be set to terse, default, or verbose to control redundant lines of error reports. terse: Only critical and major error texts and text locations are returned (which is generally suitable for single-line error information). default: Critical and major error texts and text locations, error details, and error messages (possibly involving multiple lines) are all returned. verbose: All error information is returned.

• SQL substitution

  gsql, like a parameter of a meta-command, provides a key feature that enables you to substitute a standard SQL statement for a gsql variable. gsql also provides a new alias or identifier for the variable. To replace the value of a variable using the SQL substitution method, add a colon (:) before the variable. For example:

  openGauss=# \set foo 'HR.areaS'
  openGauss=# select * from :foo;
   area_id |       area_name        
  ---------+------------------------
         4 | Middle East and Africa
         3 | Asia
         1 | Europe
         2 | Americas
  (4 rows)
  

  The above command queries the HR.areaS table.

  NOTICE

  The value of the variable is copied literally, so it can even contain unbalanced quotation marks or backslash commands. Therefore, the input content must be meaningful.

• Prompt

  The gsql prompt can be set using the three variables in Table 3. These variables consist of characters and special escape characters.

  Table 3 Prompt variables

  Variable	Description	Example
  PROMPT1	Specifies the normal prompt used when gsql requests a new command. The default value of PROMPT1 is: %/%R%#	PROMPT1 can be used to change the prompt. Change the prompt to [local]: openGauss=> \set PROMPT1 %M [local:/tmp/gaussdba_mppdb] Change the prompt to name: openGauss=> \set PROMPT1 name name Change the prompt to =: openGauss=> \set PROMPT1 %R =
  PROMPT2	Specifies the prompt displayed when more input is expected because the command that is not terminated with a semicolon (;) or a quote (") is not closed.	PROMPT2 can be used to display the prompt. openGauss=# \set PROMPT2 TEST openGauss=# select * from HR.areaS TEST; area_id | area_name ---------+-------------------- 1 | Europe 2 | Americas 4 | Middle East and Africa 3 | Asia (4 rows))
  PROMPT3	Specifies the prompt displayed when the COPY statement (such as COPY FROM STDIN) is run and data input is expected.	PROMPT3 can be used to display the COPY prompt. openGauss=# \set PROMPT3 '>>>>' openGauss=# copy HR.areaS from STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >>>>1 aa >>>>2 bb >>>>\.

  The value of the selected prompt variable is printed literally. However, a value containing a percent sign (%) is replaced by the predefined contents depending on the character following the percent sign (%). For details about the defined substitutions, see Table 4.

  Table 4 Defined substitutions

  Symbol	Description
  %M	Replaced with the full host name (with domain name). The full name is [local] if the connection is over a Unix domain socket, or [local:/dir/name] if the Unix domain socket is not at the compiled default location.
  %m	Replaced with the host name truncated at the first dot. It is [local] if the connection is over a Unix domain socket.
  %>	Replaced with the number of the port that the host is listening on.
  %n	Replaced with the database session username.
  %/	Replaced with the name of the current database.
  %~	Similar to %/. However, the output is tilde (~) if the database is your default database.
  %#	Uses # if the session user is the database administrator. Otherwise, uses >.
  %R	In PROMPT1 normally =, but ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails). In PROMPT2 %R is replaced with a hyphen (-), an asterisk (*), a single or double quotation mark, or a dollar sign ($), depending on whether gsql expects more input because the query is inside a /*...*/ comment or inside a quoted or dollar-escaped string.
  %x	Replaced with the transaction status. An empty string when it is not in a transaction block An asterisk (*) when it is in a transaction block An exclamation mark (!) when it is in a failed transaction block A question mark (?) when the transaction status is indefinite (for example, because there is no connection).
  %digits	Replaced with the character with the specified byte.
  %:name	Replaced with the value of the name variable of gsql.
  %command	Replaced with the command output, similar to substitution with the "^" symbol.
  %[ . . . %]	Prompts may contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. For example: potgres=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%#' The output is a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals.

Environment Variables

Table 5 Environment variables related to gsql

PSQL_EDITOR_LINENUMBER_ARG = '+'  
PSQL_EDITOR_LINENUMBER_ARG='--line '

How to Use gsql

Prerequisites

The user using gsql must have the permission to access the database.

Background

You can use the gsql command to connect to the local database or remote database. When connecting to the remote database, enable remote connection on the server. For details, see Gsql Connection and Usage.

Procedure

1. Connect to the openGauss server using the gsql tool.

   The gsql tool uses the -d parameter to specify the target database name, the -U parameter to specify the database username, the -h parameter to specify the host name, and the -p parameter to specify the port number.

   NOTE

   If the database name is not specified, the default database name generated during initialization will be used. If the database username is not specified, the current OS username will be used by default. If a variable does not belong to any parameter (such as -d and -U), and -d is not specified, the variable will be used as the database name. If -d is specified but -U is not specified, the variable will be used as the database username.

   Example 1: Connect to the 8000 port of the local postgres database as user omm.

   gsql -d postgres -p 8000
   

   Example 2: Connect to the 8000 port of the remote postgres database as user jack.

   gsql -h 10.180.123.163 -d postgres -U jack -p 8000
   

   In a centralized cluster, when connecting to the primary DN, you can use commas (,) to separate the IP addresses of DNs and add them to the end of -h. gsql connects to each IP address in sequence to check whether the current DN is the primary DN. If no, gsql disconnects from the current IP address and attempts to connect to the next IP address until the primary DN is found.

   gsql -h 10.180.123.163,10.180.123.164,10.180.123,165 -d postgres -U jack -p 8000
   

   Example 3: postgres and omm do not belong to any parameter, and they are used as the database name and the username, respectively.

   gsql postgres omm -p 8000
   

   Equals

   gsql -d postgres -U omm -p 8000
   

   For details about the gsql parameters, see Command Reference.

2. Run a SQL statement.

   The following takes creating database human_staff as an example:

   CREATE DATABASE human_staff;
   CREATE DATABASE
   

   Ordinarily, input lines end when a command-terminating semicolon is reached. If the command is sent and executed without any error, the command output is displayed on the screen.

3. Execute gsql meta-commands.

   The following takes all openGauss databases and description information as an example:

   openGauss=# \l
                                   List of databases
         Name      |  Owner   | Encoding  | Collate | Ctype |   Access privileges   
   ----------------+----------+-----------+---------+-------+-----------------------
    human_resource | omm | SQL_ASCII | C       | C     | 
    postgres       | omm | SQL_ASCII | C       | C     | 
    template0      | omm | SQL_ASCII | C       | C     | =c/omm         +
                   |          |           |         |       | omm=CTc/omm
    template1      | omm | SQL_ASCII | C       | C     | =c/omm          +
                   |          |           |         |       | omm=CTc/omm
    human_staff    | omm | SQL_ASCII | C       | C     | 
   (5 rows)
   

   For details about gsql meta-commands, see Meta-Command Reference.

Example

The example shows how to spread a command over several lines of input. Note the prompt change:

openGauss=# CREATE TABLE HR.areaS(
openGauss(# area_ID   NUMBER,
openGauss(# area_NAME VARCHAR2(25)
openGauss-# )tablespace EXAMPLE;
CREATE TABLE

Query the table definition:

openGauss=# \d HR.areaS
               Table "hr.areas"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 area_id   | numeric               | not null
 area_name | character varying(25) | 

Insert four lines of data into HR.areaS.

openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (1, 'Europe');
INSERT 0 1
openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (2, 'Americas');
INSERT 0 1
openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (3, 'Asia');
INSERT 0 1
openGauss=# INSERT INTO HR.areaS (area_ID, area_NAME) VALUES (4, 'Middle East and Africa');
INSERT 0 1

Change the prompt.

openGauss=# \set PROMPT1 '%n@%m %~%R%#'
omm@[local] openGauss=#

Query the table:

omm@[local] openGauss=# SELECT * FROM HR.areaS;
 area_id |       area_name        
---------+------------------------
       1 | Europe
       4 | Middle East and Africa
       2 | Americas
       3 | Asia
(4 rows)

Use the \pset command to display the table in different ways:

omm@[local] openGauss=# \pset border 2
Border style is 2.
omm@[local] openGauss=# SELECT * FROM HR.areaS;
+---------+------------------------+
| area_id |       area_name        |
+---------+------------------------+
|       1 | Europe                 |
|       2 | Americas               |
|       3 | Asia                   |
|       4 | Middle East and Africa |
+---------+------------------------+
(4 rows)

omm@[local] openGauss=# \pset border 0
Border style is 0.
omm@[local] openGauss=# SELECT * FROM HR.areaS;
area_id       area_name        
------- ----------------------
      1 Europe
      2 Americas
      3 Asia
      4 Middle East and Africa
(4 rows)

Use the meta-command:

omm@[local] openGauss=# \a \t \x
Output format is unaligned.
Showing only tuples.
Expanded display is on.
omm@[local] openGauss=# SELECT * FROM HR.areaS;
area_id|2
area_name|Americas

area_id|1
area_name|Europe

area_id|4
area_name|Middle East and Africa

area_id|3
area_name|Asia
omm@[local] openGauss=#

Obtaining Help Information

Procedure

• When connecting to the database, run the following command to obtain the help information:

  gsql --help
  

  The following help information is displayed:

  ......
  Usage:
    gsql [OPTION]... [DBNAME [USERNAME]]
  
  General options:
    -c, --command=COMMAND    run only single command (SQL or internal) and exit
    -d, --dbname=DBNAME      database name to connect to (default: "omm")
    -f, --file=FILENAME      execute commands from file, then exit
  ......
  

• When connecting to the database, run the following command to obtain the help information:

  help
  

  The following help information is displayed:

  You are using gsql, the command-line interface to gaussdb.
  Type:  \copyright for distribution terms
         \h for help with SQL commands
         \? for help with gsql commands
         \g or terminate with semicolon to execute query
         \q to quit
  

Examples

1. Run the following command to connect to the database:

   gsql -d postgres -p 8000
   

   postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

   If information similar to the following is displayed, the connection succeeds:

   gsql ((openGauss VxxxRxxxCxx build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131)
   Non-SSL connection (SSL connection is recommended when requiring high-security)
   Type "help" for help.
   

2. View the gsql help information. For details, see Table 1.

   Table 1 gsql online help

   Description	Example
   Query the copyright.	\copyright
   View help information about SQL statements supported by openGauss.	View help information about SQL statements supported by openGauss. For example, view all SQL statements supported by openGauss. openGauss=# \h Available help: ABORT ALTER AGGREGATE ALTER APP WORKLOAD GROUP ... ... For example, view parameters of the CREATE DATABASE command: openGauss=# \help CREATE DATABASE Command: CREATE DATABASE Description: create a new database Syntax: CREATE DATABASE database_name [ [ WITH ] {[ OWNER [=] user_name ]| [ TEMPLATE [=] template ]| [ ENCODING [=] encoding ]| [ LC_COLLATE [=] lc_collate ]| [ LC_CTYPE [=] lc_ctype ]| [ DBCOMPATIBILITY [=] compatibility_type ]| [ TABLESPACE [=] tablespace_name ]| [ CONNECTION LIMIT [=] connlimit ]}[...] ];
   View the help information about gsql commands.	For example, view commands supported by gsql. openGauss=# \? General \copyright show openGauss usage and distribution terms \g [FILE] or ; execute query (and send results to file or |pipe) \h(\help) [NAME] help on syntax of SQL commands, * for all commands \q quit gsql ... ...

Command Reference

For details about gsql parameters, see Table 1, Table 2, Table 3, and Table 4.

Table 1 Common parameters

Table 2 Input and output parameters

Table 3 Output format parameters

Table 4 Connection parameters

Meta-Command Reference

This section describes meta-commands provided by gsql after the openGauss database CLI tool is used to connect to a database. A gsql meta-command can be anything that you enter in gsql and begins with an unquoted backslash.

Precautions

• The format of the gsql meta-command is a backslash (\) followed by a command verb, and then a parameter. The parameters are separated from the command verb and from each other by any number of whitespace characters.
• To include whitespace characters into an argument, you must quote them with a single straight quotation mark. To include a single straight quotation mark into such an argument, precede it by a backslash. Anything contained in single quotation marks is furthermore subject to C-like substitutions for \n (new line), \t (tab), \b (backspace), \r (carriage return), \f (form feed), \digits (octal), and \xdigits (hexadecimal).
• Within a parameter, text enclosed in double quotation marks ("") is taken as a command line input to the shell. The output of the command (with any trailing newline removed) is taken as the argument value.
• If an unquoted argument begins with a colon (:), the argument is taken as a gsql variable and the value of the variable is used as the argument value instead.
• Some commands take an SQL identifier (such as a table name) as a parameter. These parameters follow the SQL syntax rules: Unquoted letters are forced to lowercase, while double quotation marks ("") protect letters from case conversion and allow incorporation of whitespace into the identifier. Within double quotation marks, paired double quotation marks reduce to a single double quotation mark in the result name. For example, FOO"BAR"BAZ is interpreted as fooBARbaz, and "Aweird""name" becomes A weird"name.
• Parsing for arguments stops when another unquoted backslash is found. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of parameters and continues parsing SQL statements if any. In this way, SQL and gsql commands can be freely mixed in a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.
• In most cases the gsqlrc file won't be applied to the execution of commands the -c options specifies, except when -c options are used together with the -f options.

Meta-command

For details about meta-commands, see Table 1, Table 2, Table 3, Table 4, Table 6, Table 8, Table 9, Table 10, and Table 12.

Table 1 Common meta-commands

Table 2 Query buffer meta-commands

Table 3 Input/Output commands

Table 4 Information display meta-commands

openGauss=# \d

openGauss=# \dtable+ a

openGauss=# \d+ f*

openGauss=# \da f*

openGauss=# \db p*

openGauss=# \dc *

openGauss=# \dC c*

openGauss=# \dd

openGauss=# \ddp

openGauss=# \dD

openGauss=# \ded

openGauss=# \det

openGauss=# \des

openGauss=#\deu

openGauss=# \dew

openGauss=# \df

openGauss=# \dF+

openGauss=# \dFd

openGauss=# \dFp

openGauss=# \dFt

openGauss=# \dg j?e

openGauss=# \dl

openGauss=# \dL

openGauss=# \dm

openGauss=# \dn+ d*

openGauss=# \do

openGauss=# \dO

rolename=xxxx/yyyy  --Assigns permissions to a role.

=xxxx/yyyy --Assigns permissions to public.

openGauss=# \dp

openGauss=# \drds *openGausss

openGauss=# \dT

openGauss=# \du

openGauss=# \div

openGauss=# \dx

openGauss=# \l

openGauss=# \sf function_a
openGauss=# \sf 
"func()name"(argtype1, argtype2)

openGauss=# \z

Table 5 Description of permissions

Table 6 Formatting meta-commands

Table 7 Adjustable printing options

Table 8 Connection meta-commands

Table 9 OS meta-commands

Table 10 Variable meta-commands

Table 11 Common \set commands

Table 12 Large object meta-commands

PATTERN

The various \d commands accept a PATTERN parameter to specify the object name to be displayed. In the simplest case, PATTERN is the exact name of the object. Characters in PATTERN are usually converted to lowercase (as in SQL names), for example, \dt FOO will display a table named foo. As in SQL names, placing double quotation marks (") around a pattern prevents them being folded to lower case. If you need to include a double quotation mark (") in a pattern, write it as a pair of double quotation marks ("") within a double-quote sequence, which is in accordance with the rules for SQL quoted identifiers. For example, \dt "FOO""BAR" will be displayed as a table named FOO"BAR instead of foo"bar. You cannot put double quotation marks around just part of a pattern, which is different from the normal rules for SQL names. For example, \dt FOO"FOO"BAR will be displayed as a table named fooFOObar if just part of a pattern is quoted.

Whenever the PATTERN parameter is omitted completely, the \dcommands display all objects that are visible in the current schema search path, which is equivalent to using an asterisk (*) as the pattern. An object is regarded to be visible if it can be referenced by name without explicit schema qualification. To see all objects in the database regardless of their visibility, use a dot within double quotation marks (*.*) as the pattern.

Within a pattern, the asterisk (*) matches any sequence of characters (including no characters) and a question mark (?) matches any single character. This notation is comparable to Unix shell file name patterns. For example, \dt int* displays tables whose names start with int. But within double quotation marks, the asterisk (*) and the question mark (?) lose these special meanings and are just matched literally.

A pattern that contains a dot (.) is interpreted as a schema name pattern followed by an object name pattern. For example, \dt foo*.*bar* displays all tables (whose names include bar) in schemas starting with foo. If no dot appears, then the pattern matches only visible objects in the current schema search path. Likewise, the dot within double quotation marks loses its special meaning and becomes an ordinary character.

Senior users can use regular-expression notations, such as character classes. For example [0-9] can be used to match any digit. All regular-expression special characters work as specified in POSIX. The following characters are excluded:

• A dot (.) is used as a separator.
• An asterisk (*) is translated into an asterisk prefixed with a dot (.*), which is a regular-expression marking.
• A question mark (?) is translated into a dot (.).
• A dollar sign ($) is matched literally.

You can write ?, (R+|), (R|), and R to the following pattern characters: ., R*, and R?. The dollar sign ($) does not need to be used as a regular expression character because PATTERN must match the entire name instead of being interpreted as a regular expression (in other words, $ is automatically appended to PATTERN). If you do not expect a pattern to be anchored, write an asterisk (*) at its beginning or end. All regular-expression special characters within double quotation marks lose their special meanings and are matched literally. Regular-expression special characters in operator name patterns (such as the \do parameter) are also matched literally.

Troubleshooting

Low Connection Performance

• log_hostname is enabled, but DNS is incorrect.

  Connect to the database, and run show log_hostname to check whether log_hostname is enabled in the database.

  If it is enabled, the database kernel will use DNS to check the name of the host where the client is deployed. If the host where the database is configured with an incorrect or unreachable DNS server, the database connection will take a long time to set up. For details about this parameter, see the description of log_hostname in section Log Content.

• The database kernel slowly runs the initialization statement.

  Problems are difficult to locate in this scenario. Try using the strace Linux trace command.

  strace gsql -U MyUserName -d postgres -h 127.0.0.1 -p 23508 -r -c '\q'
  Password for MyUserName:
  

  The database connection process will be printed on the screen. If the following statement takes a long time to run:

  sendto(3, "Q\0\0\0\25SELECT VERSION()\0", 22, MSG_NOSIGNAL, NULL, 0) = 22
  poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
  

  It indicates that the SELECT VERSION() statement was run slowly.

  After the database is connected, you can run the explain performance select version() statement to find the reason why the initialization statement was run slowly. For more information, see SQL Execution Plan.

  An uncommon scenario is that the disk of the machine where the DN resides is full or faulty, affecting queries and leading to user authentication failures. As a result, the connection process is suspended. To solve this problem, simply clear the data disk space of the DN.

• TCP connection is set up slowly.

  Adapt the steps of troubleshooting slow initialization statement execution. Use strace. If the following statement is run slowly:

  connect(3, {sa_family=AF_FILE, path="/home/test/tmp/gaussdb_llt1/.s.PGSQL.61052"}, 110) = 0
  

  Or,

  connect(3, {sa_family=AF_INET, sin_port=htons(61052), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)
  

  It indicates that the physical connection between the client and the database is set up slowly. In this case, check whether the network is unstable or has high throughput.

Problems in Setting Up Connections

• gsql: could not connect to server: No route to host

  This problem occurs generally because an unreachable IP address or port number was specified. Check whether the values of -h and -p parameters are correct.

• gsql: FATAL: Invalid username/password,login denied.

  This problem occurs generally because an incorrect username or password was entered. Contact the database administrator to check whether the username and password are correct.

• gsql: FATAL: Forbid remote connection with trust method!

  For security purposes, remote login in trust mode is forbidden. In this case, you need to modify the connection authentication information in the pg_hba.conf file. For details, see Configuration File Reference.

  NOTE

  Do not modify the configurations of database hosts in the pg_hba. conf file. Otherwise, the database may become faulty. It is recommended that service applications be deployed outside the database instead of inside the database.

• The DN can connect to the database if -h 127.0.0.1 is specified, and the connection will fail if -h 127.0.0.1 is removed.

  Run the SQL statement show unix_socket_directory to check whether the unix socket directory used by the DN is the same as that specified by the environment virable $PGHOST in the shell directory.

  If they are different, set $PGHOST to the directory specified by unix_socket_directory.

  For more information about unix_socket_directory, see Connection Settings.

• The "libpq.so" loaded mismatch the version of gsql, please check it.

  This problem occurs because the version of libpq.so used in the environment does not match that of gsql. Run the ldd gsql command to check the version of the loaded libpq.so, and then load correct libpq.so by modifying the environment variable LD_LIBRARY_PATH.

• gsql: symbol lookup error: xxx/gsql: undefined symbol: libpqVersionString

  This problem occurs because the version of libpq.so used in the environment does not match that of gsql (or the PostgreSQL libpq.so exists in the environment). Run the ldd gsql command to check the version of the loaded libpq.so, and then load correct libpq.so by modifying the environment variable LD_LIBRARY_PATH.

• gsql: connect to server failed: Connection timed out

  Is the server running on host "xx.xxx.xxx.xxx" and accepting TCP/IP connections on port xxxx?

  This problem is caused by network connection faults. Check the network connection between the client and the database server. If you cannot ping from the client to the database server, the network connection is abnormal. Contact network management personnel for troubleshooting.

  ping -c 4 10.10.10.1
  PING 10.10.10.1 (10.10.10.1) 56(84) bytes of data.
  From 10.10.10.1: icmp_seq=2 Destination Host Unreachable
  From 10.10.10.1 icmp_seq=2 Destination Host Unreachable
  From 10.10.10.1 icmp_seq=3 Destination Host Unreachable
  From 10.10.10.1 icmp_seq=4 Destination Host Unreachable
  --- 10.10.10.1 ping statistics ---
  4 packets transmitted, 0 received, +4 errors, 100% packet loss, time 2999ms
  

• gsql: FATAL: permission denied for database "postgres"

  DETAIL: User does not have CONNECT privilege.

  This problem occurs because the user does not have the permission to access the database. To solve this problem, perform the following steps:

  1. Connect to the database as the system administrator dbadmin.

     gsql -d postgres -U dbadmin -p 8000
     

  2. Grant the user with the permission to access the database.

     GRANT CONNECT ON DATABASE postgres TO user1;

     NOTE

     Actually, some common misoperations may also cause a database connection failure, for example, entering an incorrect database name, username, or password. In this case, the client tool will display the corresponding error messages.

gsql -d postgres -p 8000
gsql: FATAL:  database "postgres" does not exist
gsql -d postgres -U user1 -p 8000
Password for user user1:
gsql: FATAL:  Invalid username/password, login denied.

• gsql: FATAL: sorry, too many clients already, active/non-active: 197/3.

  This problem occurs because the number of system connections exceeds the allowed maximum. Contact the DBA database administrator to release unnecessary sessions.

  You can check the number of connections as described in Table 1.

  You can view the session status in the PG_STAT_ACTIVITY view. To release unnecessary sessions, use the pg_terminate_backend function.

  select datid,pid,state from pg_stat_activity;
  

   datid |       pid       | state  
  -------+-----------------+--------
   13205 | 139834762094352 | active
   13205 | 139834759993104 | idle
  (2 rows)
  

  The value of pid is the thread ID of the session. Terminate the session using its thread ID.

  SELECT PG_TERMINATE_BACKEND(139834759993104);
  

  If a command output similar to the following is displayed, the session is successfully terminated.

  PG_TERMINATE_BACKEND
  ----------------------
   t
  (1 row)
  

  Table 1 Viewing the number of session connections

  Description	Command
  View the maximum number of sessions connected to a specific user.	Run the following command to view the upper limit of the number of USER1's session connections. -1 indicates that no upper limit is set for the number of USER1's session connections. SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='user1'; rolname | rolconnlimit ---------+-------------- user1 | -1 (1 row)
  View the number of session connections that have been used by a specified user.	Run the following command to view the number of session connections that have been used by USER1. 1 indicates the number of session connections that have been used by USER1. SELECT COUNT(*) FROM dv_sessions WHERE USERNAME='user1'; count ------- 1 (1 row)
  View the maximum number of sessions connected to a specific database.	Run the following command to view the upper limit of the number of postgres's session connections. -1 indicates that no upper limit is set for the number of postgres's session connections. SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='postgres'; datname | datconnlimit ----------+-------------- postgres | -1 (1 row)
  View the number of session connections that have been used by a specific database.	Run the following command to view the number of session connections that have been used by postgres. 1 indicates the number of session connections that have been used by postgres. SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='postgres'; count ------- 1 (1 row)
  View the number of session connections that have been used by all users.	Run the following command to view the number of session connections that have been used by all users: SELECT COUNT(*) FROM dv_sessions; count ------- 10 (1 row)

• gsql: wait xxx.xxx.xxx.xxx:xxxx timeout expired

  When gsql initiates a connection request to the database, a 5-minute timeout period is used. If the database cannot correctly authenticate the client request and client identity within this period, gsql will exit the connection process for the current session, and will report the above error.

  Generally, this problem is caused by the incorrect host and port (that is, the xxx part in the error information) specified by the -h and -p parameters. As a result, the communication fails. Occasionally, this problem is caused by network faults. To resolve this problem, check whether the host name and port number of the database are correct.

• gsql: could not receive data from server: Connection reset by peer.

  Check whether DN logs contain information similar to "FATAL: cipher file "/data/coordinator/server.key.cipher" has group or world access". This error is usually caused by incorrect tampering with the permissions for data directories or some key files. For details about how to correct the permissions, see related permissions for files on other normal instances.

• gsql: FATAL: GSS authentication method is not allowed because XXXX user password is not disabled.

• In pg_hba.conf of the target DN, the authentication mode is set to gss for authenticating the IP address of the current client. However, this authentication algorithm cannot authenticate clients. Change the authentication algorithm to sha256 and try again. For details, see Configuration File Reference.

  NOTE

  • Do not modify the configurations of database hosts in the pg_hba. conf file. Otherwise, the database may become faulty.
  • It is recommended that service applications be deployed outside the database instead of inside the database.

Other Faults

• There is a core dump or abnormal exit due to the bus error.

  Generally, this problem is caused by changes in loading the shared dynamic library (.so file in Linux) during process running. Alternatively, if the process binary file changes, the execution code for the OS to load machines or the entry for loading a dependent library will change accordingly. In this case, the OS kills the process for protection purposes, generating a core dump file.

  To resolve this problem, try again. In addition, do not run service programs in a database during O&M operations, such as an upgrade, preventing such a problem caused by file replacement during the upgrade.

  NOTE

  A possible stack of the core dump file contains dl_main and its function calling. The file is used by the OS to initialize a process and load the shared dynamic library. If the process has been initialized but the shared dynamic library has not been loaded, the process cannot be considered completely started.