FAQs

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 primary database node 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 "GUC Parameter Description > Error Reports and Logs > Logging Content" in the Reference Guide.

• 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 -W MyPassWord -d mogdb -h 127.0.0.1 -p 23508 -r -c '\q'

  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 "Performance Tuning > SQL Tuning Guide > SQL Execution Plan" in the Performance Tuning Guide.

  An uncommon scenario is that the disk of the machine where the primary database node 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 primary database node.

• 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 "Database Security Management > Client Access Authentication > Configuration File Reference" in the Developer Guide.

  NOTE: Do not modify the configurations of the MogDB host in the pg_hba. conf file. Otherwise, the database may become faulty. It is recommended that service applications be deployed outside the MogDB.

• If -h 127.0.0.1 is specified, the database connection is successful. If -h 127.0.0.1 is removed, the connection fails.

  Run the show unix_socket_directory SQL statement to check whether the Unix socket directory used by the primary database node is the same as that specified by $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 "GUC Parameter Description > Connection and Authentication > Connection Settings" in the Developer Guide.

• 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 "mogdb"

  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 mogdb -U dbadmin -p 15400

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

     GRANT CONNECT ON DATABASE mogdb TO user1;

  NOTE: Actually, some common misoperations may also cause a database connection failure, for example, entering an incorrect database name, username, or password. Misoperations are accompanied with an error information on the client tool.

  gsql -d mogdb -p 15400
  gsql: FATAL:  database "mogdb" does not exist
  gsql -d mogdb -U user1 -W Enmo@789 -p 15400
  gsql: FATAL:  Invalid username/password,login denied.

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

  This problem occurs because the number of system connections exceeds the upper limit. Contact the database administrator to release unnecessary sessions.

  For details about how to view the number of user session connections.

  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);
  PG_TERMINATE_BACKEND
  ----------------------
   t
  (1 row)

  • 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 maximum number of sessions connected to a specific database

    Run the following command to view the upper limit of the number of mogdb's session connections. -1 indicates that no upper limit is set for the number of mogdb's session connections

    SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='mogdb';
    datname  | datconnlimit
    ------+-----
    mogdb |           -1 (1 row)
    (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 mogdb. 1 indicates the number of session connections that have been used by mogdb.

    SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='mogdb';
    count
    ------
    1
    (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 primary database node logs contain information similar to "FATAL: cipher file "/data/dbnode/server.key.cipher" has group or world access". This error is usually caused by tampering with the permissions for data directories or some key files by mistake. 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 primary database node, 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 "Database Security Management > Client Access Authentication > Configuration File Reference" in the Developer Guide.

  NOTE:

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

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 MogDB 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.